Microsoft® SQL Server® 2012 Bible

833

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

O

ne of the most important considerations in any deployment of SQL Server 2012 is the authen-

tication 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 confi gured for only Windows Authentication. A value

of 2 means the SQL Server instance is confi gured for both SQL and Windows authentication. Any

other value or a value of NULL means there is an error in the confi guration.

c32.indd 833c32.indd 833 7/31/2012 10:01:02 AM7/31/2012 10:01:02 AM

http://www.it-ebooks.info