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!
LVL 17
HoggZillaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jimmypantsCommented:
there is no way to be secure using a generic account, since there is no accountability.  If someone makes a change, you have no idea who made it.

Do all users have active directory accounts already?  If so, you can write sql scripts that will give them appropriate permissions on the databases they need to be accessing.

http://vyaskn.tripod.com/scripting_permissions_in_sql_server_2005.htm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HoggZillaAuthor Commented:
In our world every user that gets added to an AD group gets done by the Security team. That means a separate request for every user. We can't even send them a list, it is one at a time. So we definately dont have permission to add users with scripts.
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?
0
HoggZillaAuthor Commented:
Thanks
0
Ted BouskillSenior Software DeveloperCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.