How to Identify Currently Used SQL Server Authentication Mode

http://www.mytechmantra.com/LearnSQLServer/SQL_Server_Authentication_Mode_P1.html

Introduction

Microsoft SQL Server Support basically supports two types of Authentication Modes to using which one can connect to an instance of SQL Server 2000 and higher versions. Two different authentication modes are Windows Authentication Mode & SQL Server and Windows Authentication Mode (Mixed Mode). In this article we will take a look at how you can verify the currently used authentication mode and how to change the authentication mode using TSQL and SQL Server Management Studio.

Different ways by which SQL Server Authenticate Users

Windows Authentication Mode: - When Windows Authentication Mode is used only windows logins can connect to SQL Server. Windows authentication is much more secure when compared to mixed authentication as in an enterprise environment Windows Login credentials are generally Active Directory domain credentials.

Mixed Mode / SQL Server and Windows Authentication Mode Authentication: - When Mixed Mode aka SQL Server and Windows authentication mode is used either Windows Logins or SQL Server Logins can be used to connect to SQL Server.

Identify SQL Server Authentication Mode Using TSQL

Database administrator can use the below mentioned TSQL code to identify the currently used SQL Server Authentication Mode.

Use Master
GO
SELECT
            CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
            WHEN 0 THEN 'Mixed Mode - Allows Both SQL Server and Windows Authentication Mode'
            WHEN 1 THEN 'Allows Only Windows Authentication Mode'
END AS [Currently Used SQL Server Authentication Mode]
GO

You can execute the below mentioned TSQL code to identify the login security configuration on an instance of SQL Server. As per Microsoft one can use this only for backward compatibility purpose.

Use Master
GO

EXEC xp_loginconfig
GO

Identify SQL Server Authentication Mode Using SQL Server Management Studio

Database administrators can also identify the SQL Server Authentication Mode using SQL Server Management Studio.
1. Connect to an Instance of SQL Server using SQL Server Management Studio
2. In Object Explorer, right click the SQL Server Instance and select Properties from the drop down list.



3. In Server Properties, click Security under Select a Page on the left side panel. Here you will be able to see the currently used SQL Server Authentication Mode.

Important Note: -
• If you would like to change the SQL Server authentication mode, then select the authentication mode under Server authentication and click OK to change the authentication mode and restart SQL Server Database Engine services to reflect the changes.

• You can also generate the TSQL script to change the authentication mode by selecting any of the options under Script drop down as highlighted in the snippet below in Server Properties window.

Change SQL Server Authentication Mode Using TSQL

Database administrators can use the scripts generated to change the authentication modes programmatically. Below mentioned is the sample scripts which was generated on my instance of SQL Server for your reference.

/* "1" for Windows Authentication Mode */
USE [master]
GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 1
GO

/* "2" for SQL Server and Windows Authentication Mode */
USE [master]
GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
GO

Conclusion

In this article you have seen how easily a database administrator can verify and change the SQL Server Authentication Mode using TSQL and SQL Server Management Studio.