Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 627
  • Last Modified:

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!
  • 2
1 Solution
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.

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?
HoggZillaAuthor Commented:
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.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now