SQL Server 2005 login for an Application with generic Active Directory Account

Posted on 2008-11-14
Last Modified: 2012-05-05
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!
Question by:HoggZilla
    LVL 3

    Accepted 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.
    LVL 17

    Author Comment

    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?
    LVL 17

    Author Closing Comment

    LVL 51

    Expert Comment

    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

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
    This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now