Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We currently use Mixed Mode for our SQL Server Authentication. I've tried to convince our DBA to allow us to use Windows Authentication so we can use Team Foundation Server, however he absolutely refuses to allow us to have it.

Accord to him, we cannot have Windows Authentication since we are planning to become PCI compliant eventually and PCI requires Mixed Mode. From what I see online, it's the opposite: The PCI standard actually prefers Windows Authentication over Mixed Mode.

Can someone give me some more information about this (preferably a URL that states the correct information) so I can direct it to our department head?

share|improve this question
4  
just a side note for a good story on auditor pains: serverfault.com/questions/293217/… –  Shawn Melton Jan 19 '12 at 18:39
add comment

3 Answers

up vote 8 down vote accepted

No.

As you suggested, the PCI Data Security Standard actually prefers Windows Authentication over any other means of authenticating to SQL Server.

The section of the standard that covers this is Requirement 8: Assign a unique ID to each person with computer access. SQL Server authentication (which Mixed Mode authentication allows) fails, or makes it extremely difficult to satisfy, the following PCI requirements:

8.5.5 Remove/disable inactive user accounts at least every 90 days.

8.5.8 Do not use group, shared, or generic accounts and passwords, or other authentication methods.

8.5.12 Do not allow an individual to submit a new password that is the same as any of the last four passwords he or she has used

As an administrator, the main problem I have with allowing SQL Server authentication is that an application connecting to your database with it will likely pull usernames and passwords in plain text from a configuration file. Anybody with read access to that configuration file now also has access to your database.

If you're in a Windows shop with a strong Active Directory setup, using only Windows Authentication to connect to your production databases confers many advantages:

  • Security and identity are enforced at the domain level, making it easy to confer and revoke rights across the domain.

    • Every person and service gets a separate domain account; service accounts can't remote into machines, and non-DBA person accounts can't connect to the databases.
    • Your DBA team can no longer share a DBA SQL Server login with God rights on every instance in your environment.
  • While SQL Server does allow you to enforce password complexity rules (which you have to enforce per PCI DSS requirements 8.5.9-11), I bet AD does this better. Also, do you really want to enforce these rules in two different places?
  • Applications connecting using Windows Authentication can no longer expose their credentials in plain text. It is much harder for someone to pop open a config file or application server and get access to your database.
share|improve this answer
    
8.5.12 will get you as well using SQL Server authentication as SQL does not have a mechanism for applying restrictions on password history that I know of. –  Shawn Melton Jan 19 '12 at 18:35
    
@ShawnMelton - Do you have a reference or demo of how one can connect via Windows Auth to SQL Server in a way that exposes the password in plain text? –  Nick Chammas Jan 19 '12 at 18:36
    
I retract my statement, since I can't prove it and that part of my brain is no longer accessible :) –  Shawn Melton Jan 19 '12 at 20:13
    
@ShawnMelton - Be sure to come back here and let me know if you find some proof. –  Nick Chammas Jan 19 '12 at 20:35
    
Thanks, this will help me fight the battle, I'll either keep fighting it or wait till he retires lol –  csharpdev Jan 20 '12 at 2:25
add comment

If you are referring to PCI compliance for credit card industry, yes Windows Authentication is preferred. Search this phrase on Google: "SQL Server security best practices"

The top two links, especially the first one, will link you to documentation that Windows Authentication is preferred. The first one is a link to the best practices whitepaper for SQL 2005 but applies to all versions of SQL Server above that.

I'm involved with doing security scans on databases and instances that are required to meet DoD standards and Windows Authentication is preferred there as well. As well I have gone through PCI compliance audits and they will tend to look for Windows Authentication.

share|improve this answer
add comment

None of the compliance regulations (e.g. PCI, HIPAA, GLBA, Basel II, FERPA, or SOX) forbid Windows Authentication mode

Actually, it's recommended not to use supplied system and other security parameters on SQL Server. Instead of using the mixed mode (enables both Windows authentication and SQL Server authentication), use the Windows authentication only. It utilizes the Windows password policy for accessing SQL Server. This enables checking the password history, password minimum length, and password minimum and maximum life. The most important Windows password policy characteristics is the login lockout - if a login continuously fails for a specified number of times

When it comes to SQL Server authentication brute-force attack vulnerability, the situation is not so favorable. SQL Server Authentication has no features that allow detecting when the system is under a brute-force attack. Moreover, SQL Server is very responsive when it comes to validating the SQL Server authentication credentials. It can easily handle repeated, aggressive, brute-force login attempts without negative overall performance that might indicate such attacks. This means that the SQL Server Authentication is a perfect target for password cracking via brute-force attacks

Also, brute-force methods are evolving with each newly introduced encryption and password complexity method. For example, attackers that use rainbow tables (the pre-computed tables for reversing the cryptographic hash values for every possible combination of characters) can easily and quickly crack any hashed password

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.