How to check & Change SQL Server Authentication Mode.

In this blogpost we will see and learn how to check and change SQL Server Authentication Mode. Before we get started, lets see the two modes that SQL Server authenticates logins. The two modes are:

1. Windows Authentication Mode

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

Windows Authentication Mode

Windows authentication is the default, and is often referred to as integrated security because this SQL Server security model is tightly integrated with Windows. Specific Windows user and group accounts are trusted to log in to SQL Server. Windows users who have already been authenticated do not have to present additional credentials. With Windows authentication, users are already logged onto Windows and do not have to log on separately to SQL Server.

A typical connection string for Windows Authentication would look like:

"Server=localhost;Database=myDataBase;Integrated Security=true;"

Mixed Mode Authentication

Mixed mode supports authentication both by Windows and by SQL Server. User name and password pairs are maintained within SQL Server. If you must use mixed mode authentication, you must create SQL Server logins, which are stored in SQL Server. You then have to supply the SQL Server user name and password at run time.

A typical connection string for SQL Server Authentication would look like:

"Server=localhost;Database=myDataBase;User Id=myUsername;Password=myPassword;Integrated Security=false;"

How to check SQL Server Authentication Mode?

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.

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

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]

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'

How to Change SQL Server Authentication Mode?

During the SQL Server installation you must have allowed at least one Windows user to have access to SQL Server and be able to log into SQL Server using this account. If this has not been done, you will need to reinstall SQL.

Change Using SSMS : Follow below steps to change SQL Server Authentication mode:

1. Open SQL Server Management Studio by going to Start > Programs > Microsoft SQL Server YEAR > SQL Server Management Studio.

2. Log in with Windows Authentication with an administrative account.

3. Change the authentication

   1. On the Object Explorer window right click on the server name and go to Properties.

      1. Select the Security section.

         1. Under Server Authentication change the selection from Windows Authentication mode to SQL Server and Windows Authentication mode.

         2. Click Ok

      2. Click Ok

4. Enable the sa login:

   1. In Object Explorer expand the Security folder, and the Logins.

   2. Right click sa and go to Properties.

      1. On the General tab set the password you want to use.

      2. On the Status tab, in Login set it to Enabled.

      3. Click OK

5. Restart SQL Server:

   1. Right click on your Server name in Object Explorer

   2. Select Restart

Change Using Server Property : Execute below command to change Windows authentication to Mixed Mode:

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
GO

After the changed, we need to bounce SQL Server to make it effective.

That's all in this post. If you liked this blog and interested in knowing more about SQL Server, Please Like, Follow, Share & Subscribe to www.ImJhaChandan.com .