SQL Server – Two Protocols Used by Windows Authentication

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.

Windows Authentication is always available, SQL Server Authentication is only available if Mixed Mode is enabled.

During setup, you must select an authentication mode for the Database Engine. There are two possible modes: Windows Authentication mode and mixed mode.

So:

For an instance using Windows Authentication mode, connections can only be established using Windows Authentication.

For an instance using Mixed Mode, connections may be made using either Windows Authentication or SQL Server Authentication.

In other words, Windows Authentication being always available does not mean it is always used. A company may choose to use only SQL Server Authentication, even if Windows Authentication cannot be disabled (it will remain enabled but unused).