Microsoft SQL Server 2012 Bible

Chapter 32

Authentication Types in SQL Server

In This Chapter

Understanding SQL Server Authentication Types

Understanding Advantages, Disadvantages, and Differences between SQL and Windows Authentication

Understanding Kerberos and Windows Authentication Delegation

One of the most important considerations in any deployment of SQL Server 2012 is the authentication type that users and applications use to connect to SQL Server databases. The two authentication types supported in SQL Server 2012 follow:

1. Windows

2. SQL Server

Windows authentication is always enabled and cannot be disabled. SQL Server authentication must be explicitly allowed during setup by choosing Mixed Mode or after setup by modifying SQL Server properties and enabling SQL and Windows Authentication mode. To verify if an instance supports SQL Server Authentication, you may use the SERVERPROPERTY function as shown in following script:

SELECT 
CASE 
    WHEN SERVERPROPERTY('IsIntegratedSecurityOnly') = 1 
    THEN ‘Windows Authentication Only'
    WHEN SERVERPROPERTY('IsIntegratedSecurityOnly') = 0 
    THEN ‘SQL and Windows Authentication'
    ELSE ‘Configuration Error'
END

A value of 1 means the SQL Server instance is configured for only Windows Authentication. A value of 2 means the SQL Server instance is configured for both SQL and Windows authentication. Any other value or a value of NULL means there is an error in the configuration.

You may also check the Authentication mode under the SQL Server properties in SQL Server Management ...