Just wondering how others have solved this

Some background: We have a decent number of users for our SQL Server around 2000, with a minimum of around 500 active at any one time. We have I believe 4 clusters and around 10 other non clustered servers, with about 5 databases on average per server. Our 'primary' cluster and DB has around 900 tables, 1600 procs, 500ish functions, and a few views, though they are avoided due to the performance issues of filtering data with them.

Issue: Our issue revolves around security, and how to block ad hoc queries for performance reasons. Pre LLBLGen we primarily used stored procedures and around 100 permission groups (addative and subtractive) with windows authentication to restrict users.

The move to LLBLGen removes the need of many of our stored procs but will eventually create a nightmare management problem if we manage all the permissions on the tables, it was already bad enough on the stored procs.

Our current solution for 'data miners' i.e. ad hoc queries is currently we have a SQL Job that runs periodically and searchs for these connections and kills them off, not the best answer but mostly works.

Solution on the table: In the future we are considering using Windows Authentication to hit our deployment database where it can pull down an encrypted SQL Login/Password to connect to our other databases, this obviously has some disadvantages as well but solves our biggest performance issue of the ad hoc queries, and removes a lot of the maintenance issue with the number of tables we have which is expected to grow substantially, by placing the burden on the application and what functionality they can do instead of restricting tables/procs, and denying all Windows Auth access.

So I'm curious does anyone else have a large SQL Server system where you need to block ad hoc queries and direct table access and how you addressed it.

I've also looked at application roles but the whole connection pooling doesn't work with it really bums me out as a possible solution.

Thanks John Gooding

• define sqlserver roles in your catalog, define security per role on the tables/views etc.
• create several sqlserver users. Add them to each role.
• in your application, you pull from the server, based on the windows user, a dll. This dll contains the connection string to use.
• this connection string is read by your app, and passed to the LLBLGen Pro code.
• every week or so, you alter the passwords, create new dlls and let the users pull these from the server automatically.

This way, the app knows how to connect, it connects under a role, so it can only access the objects assigned to that role. The user doesn't know how to connect, as the connection string isn't known (you can for example encrypt it and decrypt it in your own app.).

If you then see a user manually browsing the db, s/he hacked the application and you can use management to punish this user, as s/he clearly undermined the organization. Though most users won't even understand how to grab the credentials.

Guess we will do more research into using roles, is there a big hit with the loss of connection pooling using LLBLGen and Roles?

That's the main reason we haven't really dug into them, it seems a pretty hefty price to pay in performance.

Thanks John

jtgooding wrote:

Guess we will do more research into using roles, is there a big hit with the loss of connection pooling using LLBLGen and Roles?

That's the main reason we haven't really dug into them, it seems a pretty hefty price to pay in performance.

Thanks John

Roles are used to group security settings, so they're not connected to connection pooling.

Why using the application role precludes the connection pooling?

1. You add all database user to the guest account

2. Create an application role for your application

3. For application role add required permissions for all the required tables

4. call the sp_setapprole stored procedure, when the application starts

If your application creates the connection string, and send it with every database call, the connection pooling has to work, because the connection string is the same, and independent of the users.

The user doesn't reach any table, only via the application. Gabor

Gabor wrote:

Why using the application role precludes the connection pooling?

I'm not sure but I remember I read somewhere in the MSDN that application roles can't be used when connection pooling is used. This is because the PHYSICAL connection, which is what's pooled) holds the application role. So when you close a connection in .NET, the role isn't stopped, so a new connection gets a new open physical connection WITH an active application role.

Hello,

I'm sorry to open this very old thread, but since I've started on a new project where security is super important due to FDA CFR 21 part 11 regulations compliance, I was wondering if this (Otis' answer with the dll-pulling) was still the "way to go" for security, or there are since then new and improved ways of doing things ? I will be using LLBLGen 3.5 but i don't think that matters ...

My initial thought was for an encrypted connection string (what are best practices there ?) and then use Active Directory to authenticate the user and based on that user get his permissions from users/groups/roles custom tables in the database. Or is that too simplistic ?

I can't stress enough the importance of security/traceability/auditing ... in this project due to the FDA stuff ...

HcD wrote:

I'm sorry to open this very old thread, but since I've started on a new project where security is super important due to FDA CFR 21 part 11 regulations compliance, I was wondering if this (Otis' answer with the dll-pulling) was still the "way to go" for security, or there are since then new and improved ways of doing things ?

The Otis' answer is still a very popular choice to secure the connection string. You also could encrypt the config with some kind of RSA keys associated to the machine. See this recent thread about that: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=20883&StartAtMessage=0&#117613

HcD wrote:

My initial thought was for an encrypted connection string (what are best practices there ?) and then use Active Directory to authenticate the user and based on that user get his permissions from users/groups/roles custom tables in the database. Or is that too simplistic ?

I can't stress enough the importance of security/traceability/auditing ... in this project due to the FDA stuff ...

I don't know whether or not it's "to simplistic", because in the security subject you can always go further, but usually there are always trade-offs. I'm not familiar with FDA, but having a quick look, the rules are pretty general. For instance, FDA says that you need to enforce the confidentiality, integrity, etc. There are a lot of ways (or layers) to enforce confidentiality, using roles (Active Directory, or roles managed by your app) and encrypting sensitive *.config data is good, I don't know whether it's enough, but it's good. FDA also says that you need to have good logging mechanisms, that is part of the confidentiality and monitoring, in this matter you can use LLBLGen Auditing. At the end of the day, you should evaluate what is worthy to implement, what is 'enough' based on the the app exposure, the business rules, the legal requirements, etc. There are a lot of books about this.

Hope helpful

(Edit) Next time please open a new thread and mention this thread as a reference