SQL Server 2005 login for an Application with generic Active Directory Account
We have an development team that builds applications in VB.NET 2008 and VBScript. All of these applications use SQL Server 2005 as the database. Our company is very strict about security and the normal policy is Windows Authentication for SQL Server.
Here is the question: We want to use a generic Active Directory to login to the database, how can we do this with utmost security?
The account is maintained by our team and the PCI compliant password is only known by a few key team members. With this type of control we don't have to control database access for over 1,000 users in Windows Groups. Since the application requires read/write access, we also don't have to worry about users accessing the database by other means/tools.
I really want to use this Application account. THEY really want me to manage literally 1000+ users in windows groups. I want users to be restricted in access to the database by the application. None of the users would even know what login connected to the database. THEY want me to make a case and prove it is the best way to go. Please help!
Here is the question: We want to use a generic Active Directory to login to the database, how can we do this with utmost security?
The account is maintained by our team and the PCI compliant password is only known by a few key team members. With this type of control we don't have to control database access for over 1,000 users in Windows Groups. Since the application requires read/write access, we also don't have to worry about users accessing the database by other means/tools.
I really want to use this Application account. THEY really want me to manage literally 1000+ users in windows groups. I want users to be restricted in access to the database by the application. None of the users would even know what login connected to the database. THEY want me to make a case and prove it is the best way to go. Please help!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks
Generally AD authentication is recommended to access a DB because the credentials are encrypted using a one way hash. So, IT loves it because they feel the database is safe.
However, the danger is now that any user with Excel or Access installed on their system can access the database directly without your code!
When you allow domain user accounts permission to execute T-SQL they don't have to use your applications to access the SQL server.
I actually demonstrated this at a company and their jaws dropped. Sitting at a users workstation I showed them how much havoc I could create with direct access to the database. The subject of using AD accounts to access the SQL database was quickly dropped.
I strongly recommend using SQL accounts with the credentials encrypted in a resource DLL. All versions of Microsoft SQL past SQL 2000 SP4 encrypt SQL passwords in the TCP packets.
Ultimately if you designed your database correctly the SQL account should have need to know access anyway and you can add auditing your application layers.
However, the danger is now that any user with Excel or Access installed on their system can access the database directly without your code!
When you allow domain user accounts permission to execute T-SQL they don't have to use your applications to access the SQL server.
I actually demonstrated this at a company and their jaws dropped. Sitting at a users workstation I showed them how much havoc I could create with direct access to the database. The subject of using AD accounts to access the SQL database was quickly dropped.
I strongly recommend using SQL accounts with the credentials encrypted in a resource DLL. All versions of Microsoft SQL past SQL 2000 SP4 encrypt SQL passwords in the TCP packets.
Ultimately if you designed your database correctly the SQL account should have need to know access anyway and you can add auditing your application layers.
ASKER
We already track changes made by the application back to the user. I mean, we know who the user is by their Active Directory account - we just want the connection to the database to be a single common login. Users in the application will not even know that it is not their AD account that gives them permission, the application account will be hidden from them.
What do the do in Oracle? I know in PeopleSoft we had a psoft account that talked to the datababse, nobody knew the password to that account except the DBA group.
Does anyone else have this problem? Similar problem?