database security?

What is the standard for database security – windows login or sql server logins? User defined database roles?

We have earlier used a single sql server login with read/write permissions to tables & exec on stored procedures. Individual application users are stored in database tables & their permissions are handled by the application itself. But we are starting to get concerned about password issues with the sql login & also that when I see any connections on the database, all of hem show up with the same sql login. So I cannot make out which user exactly has a connection open.

To get around these issues, we are thinking to add NT logins for each user. These users will be part of an NT group and will be added as a user to the database. There will be a database role with exec permissions to required stored procedures (all read/write/update will be controlled through SPs) and the NT group will be part of this role. The stored procedures will be encrypted. So if a NT user logs in directly to the database, he won’t be able to read from the tables or SPs and access will be limited through the application.
I will be able to see each login associated with a connection.

What do you all think about this security set up? What are other issues I need to be concerned about? What are other popular database security models?

Thanks!

For Intranet

if you application requires tight security like only few people can use the application then go with Domain Group and add those users to domain group.

This can also be done by SQL Login, then your app should maintain a separate table for Application logins and authenticate using that table.

The are pros and cons on both methods.

I prefer SQL Login (just data reader and writer and EXEC permissions) for application to connect to sql server and the application maintain a separate login table.


If it is internet based then SQL Login.

This is for a web based application and includes users outside the intranet as well. We do want tight security.

Can you elaborate on why you like a sql server login? Doesnt Microsoft recommend Windows authentication where possible?

If application handles security through a login table, doesn’t it limit your capability to be able to do see which user did what transaction?

1. Outside Intranet you cannot connect to sql server using windows authentication. For example any online banking you cannot authenticate every customer using their own windows login because they do not belong to the domain

2. your application is going to connect to the SQL server and not the users directly.

3. Users who login to the application are validated and autheticated on the application side and not on the sql side.

4. If I have 3 different levels of users - Read/write, Read, execute and I have to maintain three differnt logins on the SQL Server side. Instead I create one login and application can handle all those categorization

5. If a hacker is going to break a SQL password in one day. It will take little bit more time for the hacker to break the windows password

6. If you are worried about security it should be tight in application side like RC4 and so on. and your sql password should be unpredictable like in alphanumeric with special characters.

I handle more than 100 sql servers single handedly. I dont like to have more headaches in maintainting different domain groups for different apps we have. All I provide is one login for one application with read, write and execute access. All reports are done from the replicated server. I create one login with readonly on that server for every application.

All the passwords are like this 'C2660AF5-7997-4BA5-A1BF-07615F3003D0'

I use "select newid()" to create those passwords

I change the passwords every month (for security sensitive apps) to a new password during our BCP test. So all the apps will get a new password.

I run security audits on all the servers.

It is application's resposibility to keep track of who deleted a row or updated it. Triggers or audit tables.

Wow! That’s a lot for me to think over :) I appreciate your comments:)

For num 1, All my users are known to me. They are part of trusted domains. So I can add their logins to sql server.
For num 4, I don’t need to create 3 different roles, just 1 role can have execute permissions on all stored procedures. Which user can execute what sp can stil be handled by the application.

I like your idea about using new(id) to create passwords.

We can use audit triggers etc to track who changed what record. But lets say I am seeing some processes blocked or a long running process, it will show up as the same single sql server login, how do I figure out which user is actually running that process & track down the problem. If I have separate logins, I can see which which user & from what hostname is running the process…

In a webbased application you cannot see the hostname in sqlserver. The hostname will be either you application server or your webserver name {depending upon your Tier architecture}

that's true - i will only be able to see the individual nt loginame , but not the individual hostname.