how to check the SQL Server authentication mode using T SQL scripts and SSMS

Using T SQL and SSMS, how to check the SQL Server authentication mode

Before we begin, I want to go through the two methods that SQL Server uses to verify logins. There are two methods:

 1. Windows Authentication Mode, for example

2. Authentication Mode for Windows and SQL Server (Mixed Mode) 

Being a SQL Server DBA, Sometime we need to know the SQL Server authentication mode to fix some issues.

 Windows Authentication Mode

We can only connect to SQL Server using Windows logins in Windows authentication mode. The Kerberos security protocol is used by Windows Authentication. Windows login credentials are often Active Directory domain credentials in business environments.

Mixed Mode Authentication

While using mixed mode authentication, we can connect to SQL Server using either Windows authentication or SQL Server authentication.

Mixed Mode is substantially less secure than Windows Authentication Mode. The provision of SQL Server Authentication is made only for backward compatibility.

Check Using SSMS

In SQL Server Management Studio Object Explorer, right-click on the server name, click Properties and go to Security page to check the SQL Server Authentication. In this case we can see that it is Windows Authentication mode.

Check Using Server Property

 The Server Property function will return "1" for Windows authentication and "0" for Windows/SQL Authentication (Mixed Mode). It would be nice if these values were consistent from what is stored in the registry.

Use Master

GO

 

SELECT

            CASE SERVERPROPERTY('IsIntegratedSecurityOnly')

            WHEN 0 THEN 'Mixed Mode - Authentication Mode (Both SQL Server and Windows Authentication)'

            WHEN 1 THEN 'Only Windows Authentication Mode.'

END AS SQL_Server_Authentication_Mode

GO