How to Identify Currently Used SQL Server Authentication Mode

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.