SQL Server and asp.net application security models / best practice

Question

For my asp.net web applications against sql server(at least the ones that require a logon to access) I generally implement security as follows:

I generally roll my own user signup, user login pages and keep a userid and an encrypted password in an sql server and validate the login against that table - I also provide for forgotten passwords, 'send me my password', email verification to activate accounts etc all thru custom code.

Once the user has been validated by the app (and lets assume that all users have the same privileges), I generally use a utility logonid to let asp.net talk to sql server, so in other words I only need to create a single logon id per application, and since 100% of all data access is done thru stored procedures, I need only grant access to a single user to execute stored procedures, and no other work is required on sql server. Each application has its own database, and the logon for that app can only access that database.

All this works perfectly well for me, the only negative of this is that it would be nice to be able to setup a trace in sql server and see the userid and the procs that are being called, but since all users are 'talking' to the database thru a single database logon, this can't happen.

So, two part question:

1) Are there security models are you folks using that I should consider? Its easy to always do the same thing over and over, - especially since it works - but are there other models that would work better or that I should consider? Is it recommended practice that all database access from an asp.net app would all share a single database login? or is this considered bad practice? and if so, why?

2) Assuming I stick with my model, is there a way to allow for the application login id to be seen in the sql trace window? It would be nice to see the sp's being called and the user id of the person logged into the system (not the database login).

Answer 1

1)As long as you are using stored procedures for access one login may be fine. Some peolple like to use one for admin as well.

2)You could modify your stored procedures to accept a user id as a parameter.

Answer 2

1) It is recommended that your web application use a single login to the database typically. If you don't you are going to be forced to impersonate your caller, which is typcially not recommended, and it doesn't scale very well. You should not use a different connection string for each user. For example using SQL Authentication for each user is a bad idea. It will make connection polling ineffective.

2) You could do this by modifying the connection string but that would make connection pollign ineffective.

Answer 3

Is it recommended practice that all database access from an asp.net app would all share a single database login?

Yes, primarily for connection pooling.

In regards to 2), I normally do that by logging at the ASP.NET side.

Answer 4

From a .NET best practices point of view, you may want to consider taking a look at Microsoft Enterprise Library. It contains a set of practices, patterns, and features that assist with issues such as Security and Data Access.

Answer 5

I would generally share a single user for connection pooling.

In the case where you may need to trace a particular user. I write in admin functionality where you can make the application use a second database login. You can then enable this for a specific user and trace that user individually.

It just means you get the ability to trace as a one off, yet keeping the single user connection pooling for the rest of the application.

Answer 6

I've been using the same approach as well. More recently, I wonder if this is affecting the application's scaleability. My DB servers have multi-core processors and are quite capable of parallel operations, but I think SQL Server serialized queries running with the same userID. I think this means that stored procedures from different actual users are beeing queued up because SQL Server sees them all as coming from the same the same userID.

If this is the case, I'd think it would be severely limiting the scaleability of my app, no?