mssqltips logo

How to check SQL Server Authentication Mode using T SQL and SSMS

By:   |   Updated: 2010-12-24   |   Comments (1)   |   Related: More > Security

Problem

Many times developers want to put logic into their code or SSIS/DTS package to check the SQL Server authentication mode. How can this be done programmatically?

Solution

Before we get started, I want to cover the two ways that SQL Server authenticates logins. The two ways are:

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

Windows Authentication Mode

In Windows authentication mode, we can only use Windows logins to connect to SQL Server. Windows Authentication utilizes the Kerberos security protocol .In enterprise environments, Windows login credentials are normally Active Directory domain credentials

Mixed Mode Authentication

In Mixed mode authentication, we can use either Windows authentication or SQL Server authentication to connect to SQL Server.

Windows Authentication Mode is much more secure than Mixed Mode. SQL Server Authentication is provided for backward compatibility only. Whenever possible use Windows Authentication.


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.

checking ways that sql server authenticates logins


Check Using xp_instance_regread

Using xp_instance_regread system procedure, we can read the registry value. SQL Server stores a "1" for Windows Authentication and a "2" for SQL Server authentication (Mixed Mode) in the windows registry. You can execute the below query to check the SQL Server Authentication.

DECLARE @AuthenticationMode INT  
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'LoginMode', @AuthenticationMode OUTPUT

SELECT CASE @AuthenticationMode
WHEN 1 THEN 'Windows Authentication'
WHEN 2 THEN 'Windows and SQL Server Authentication'
ELSE 'Unknown'
END as [Authentication Mode]

Output

check using xp_instance_regread


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.

SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')   
WHEN 1 THEN 'Windows Authentication'
WHEN 0 THEN 'Windows and SQL Server Authentication'
END as [Authentication Mode]

Output

check using server property


Check Using xp_logininfo

Another option is to use xp_loginfo. This returns a value of "Windows NT Authentication" for Windows Authentication and "Mixed" for Windows/SQL Authentication (Mixed Mode).

EXEC master.sys.xp_loginconfig 'login mode'  

Next Steps
  • You can also check the Windows registry by using the regedit application
  • So if you need to check the type of authentication that is being used on your SQL Server use these various methods
  • Read these additional security related tips.


Last Updated: 2010-12-24


get scripts

next tip button



About the author
MSSQLTips author Jugal Shah Jugal Shah has 8+ years of extensive SQL Server experience and has worked on SQL Server 2000, 2005, 2008 and 2008 R2.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Thursday, December 20, 2018 - 11:01:12 AM - Jim Evans Back To Top

 This is an enlightening article and it is good to know the different options for checking SQL Login Mode. These methods are relevant even now in SQL Server 2017!

 

However, it is good to point out that a change to the Login Mode required a SQL Service re-start!  Note that SERVERPROPERTY('IsIntegratedSecurityOnly') is the best option and it shows the Actual Effective Login Mode! The other two options (master.sys.xp_loginconfig 'login mode' AND master.dbo.xp_instance_regread) show the current registry setting which may not takes effect until the next SQL re-start. Typically, this setting should not be changed after the Server is in production.  We had an issue where the Login Mode was unexpectedly changed.  Later when the SQL Service was restarted it took effect and cause many unexpected connectivity issues.

Thanks for sharing this information.

Jim Evans



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools