Authentication Modes in SQL Server

Hi,

Today we will discuss various authentication modes available in SQL Server. A script that helps to check current Authentication Mode and how we can change the Authentication mode from Registry.

There are 3 authentication modes in SQL Server:

• SQL Server
• Windows Authentication
• Mixed Mode

Let's discuss various modes in detail:

SQL Server Authentication allows a user to connect with a specified login name and password. when a request is received SQL Server performs the authentication itself by checking to see if a SQL Server login account has been set up and if the specified password matches. If SQL Server does not have a login account set, authentication fails and the user receives an error message.

The authentication diagram as per BOL is:

Windows Authentication mode allows a user to connect through a Microsoft Windows user account. When a user connects through a Windows user account, SQL Server validates the account name and password by calling back to Windows for the information. SQL Server achieves login security integration with Windows by using the security attributes of a network user to control login access. A user's network security attributes are established at network login time and are validated by a Windows domain controller. When a network user tries to connect, SQL Server uses Windows-based facilities to determine the validated network user name. name and password. Login security integration operates over any supported network protocol in SQL Server.

If a user attempts to connect to an instance of SQL Server providing a blank login name, SQL Server uses Windows Authentication. If a user attempts to connect to an instance of SQL Server configured for Windows Authentication Mode by using a specific login, the login is ignored and Windows Authentication is used.

Windows Authentication has certain benefits over SQL Server Authentication, primarily due to its integration with the Windows security system. Windows security provides more features, such as secure validation and encryption of passwords, auditing, password expiration, minimum password length, and account lockout after multiple invalid login requests.

Because Windows users and groups are maintained only by Windows, SQL Server reads information about a user's membership in groups when the user connects. If changes are made to the accessibility rights of a connected user, the changes become effective the next time the user connects to an instance of SQL Server or logs on to Windows (depending on the type of change). Note that Windows Authentication Mode is not available when an instance of SQL Server is running on Windows.

Mixed Mode (Windows Authentication and SQL Server Authentication)
Mixed Mode allows users to connect to an instance of SQL Server using either Windows Authentication or SQL Server Authentication. Users who connect through a Windows user account can make use of trusted connections in either Windows Authentication Mode or Mixed Mode.

Windows vs SQLServer Authentication
Windows authentication is generally preferred because it provides an optimal level of integration with Windows 2000. User and group accounts from Windows are granted or denied access to SQL Server. Windows 2000 authenticates the user when the user logs on to the network. Because the password is authenticated at network login, SQL Server does not need to know or verify the password of a user.

Windows Authentication provides following advantages over SQL Server Authentication.

• Windows Authentication can grant group accounts access SQL Server, thus minimizing the over head of login administration
• Users are authenticated by Windows, resulting in a secure authentication over a network
• Users could be able to use the same user credentials for network and database access
• Audited events can be tracked to a network user.

Change Authentication mode through Registry:

We can change the authentication mode from Windows Registry of the machine where SQL Server is installed using following steps:

Step 1: Let's check what is the current Authentication mode of our SQL Server:

Step 2: Click Start=>Run and type REGEDIT to open Registry Editor

Step 3: Go to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer. There you should be able to see LOGINMODE property.

Step 4: Double Click on LoginMode property, it will be either 1 or 2, change it.

Step 5: let's check what's the new/updated Authentication mode. Its already updated to SQL Server & Windows Authentication but it will not work till we restart the SQL Server Service.

Step 6: Restart SQL Server Service

Script to check Authentication Mode:

--SQL 2005 Version

set nocount on
go
DECLARE @SqlPath Nvarchar(255)
DECLARE @instance_name Nvarchar(30)
DECLARE @reg_key Nvarchar(500) 
Declare @value_name Nvarchar(20)
Declare @LoginMode_Value int
Declare @LoginMode Nvarchar(15)

if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1) = '9'
BEGIN
select @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname)

if @instance_name is NULL
BEGIN
set @reg_key = 'SOFTWARE\Microsoft\MSSQLServer\MSSQlServer\SuperSocketNetLib\Tcp' 
END
ELSE BEGIN
set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'
END

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', 
@key=@reg_key, @value_name=@instance_name,
@value=@SqlPath output

if @instance_name is NULL
BEGIN
set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\' 
END
ELSE BEGIN
set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @sqlpath + '\MSSQLServer\'
END 

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', 
@key=@reg_key, @value_name='LoginMode',
@value=@LoginMode_Value output

if @LoginMode_Value = 1
set @LoginMode = 'Windows'
if @LoginMode_Value = 2
set @LoginMode = 'Mixed' 

select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @LoginMode as AuthenticationMode

END

if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1) = '8'
BEGIN
select @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname)

if @instance_name is NULL
BEGIN
set @reg_key = 'Software\Microsoft\MSSQLServer\MSSQLServer' 
END
ELSE BEGIN
set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer'
END

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', 
@key=@reg_key, @value_name=LoginMode,
@value=@LoginMode_Value output 


if @LoginMode_Value = 1
set @LoginMode = 'Windows'
if @LoginMode_Value = 2
set @LoginMode = 'Mixed' 

select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @LoginMode as AuthenticationMode
END

Do let me know if you have any comments.

Regards,

HAPPY SQL CODING