How to Change Authentication Mode in SQL Server?
Authentication is a process to establish a successful connection. SQL Server provides two types of authentication modes to connect to their database instances. Authentication Mode is configured while installing SQL Server instance. We can also change SQL Server authentication mode later post installation. Below are two types of authentication modes in SQL Server.
- Mixed Mode ( Windows +SQL Server login )
- Windows Mode (Only Windows)
Mixed mode provides two ways to establish database connections. One is using SQL Server logins at SQL Server layer and another one is using Windows domain user authenticated using active directory and whose login is created and mapped on SQL Server instance.
Windows Mode has only one way to establish database connections and that is using windows domain accounts. We must map windows accounts on SQL Server first before making any database connections. This mode doesn’t provide access to SQL Server logins.
Read More to Grow:
- SQL Server Interview Questions & Answers on Indexes
- How to Prepare for a Job Interview?
- SQL Server Alwayson Interview Questions & Answers
- SQL Server Replication Interview Questions & Answers
Why Windows Authentication Mode is More Secure in SQL Server?
Windows mode is secure than Mixed mode authentication because mixed mode has SQL Server authentication which cannot use Kerberos security protocol. Windows account needs to be authenticated to domain active directory every time you establish a database connection. Also, Windows provides additional password policies that are not available for SQL Server logins and that is why Microsoft always recommends to use Windows authentication mode wherever it’s possible.
One more risk with Mixed mode is the encrypted SQL Server Authentication login password must be passed over the network at the time of the connection. Some applications that connect automatically will store the password at the client. These are additional attack points.
Change Authentication Mode in SQL Server
You can configure authentication mode in SQL Server during installing it to your system. SQL Server installation windows will allow you to choose any one of above given authentication modes. If you will choose mixed authentication mode then you need to provide sa password as per windows password policy. You can also change existing authentication mode setting to another one as per your business need.
Let’s assume you have installed SQL Server with Mixed authentication mode and now you have to change it to Windows Authentication mode. This section will explain step by step process to change SQL Server authentication mode.
We can set or change authentication mode in SQL Server either using SQL Server management studio or using T-SQL statements.
Using SQL Server Management Studio
- Launch SQL Server Management Studio, connect to your target SQL Server instance.
- In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
- On the Security page, under Server authentication, you can see both authentication modes. Select any as per your need and then click OK.
- This change will require SQL Server service restart. A popup window will appear to ask you to restart the service when you will click at OK button in above step.
- In Object Explorer, right-click your server, and then click Restart. If SQL Server Agent is running, it must also be restarted.
You have changed your authentication mode from Mixed mode to Windows using SQL Server Management Studio.
If you are changing authentication mode from Windows to Mixed mode then make sure to enable SQL Server login sa account. This account gets disabled during windows authentication mode to secure your SQL Server instance as sa is generic SQL Server login as it can be targeted by hackers to access your instance. Below are the steps to enable this account:
- In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties.
- On the General page, you might have to create and confirm a password for the sa login.
- On the Status page, in the Login section, click Enabled, and then click OK.
Using T-SQL Statement
Microsoft has provided an extended stored procedure to chnage authenication mode in SQL Server using T-SQL statement. This extended stored procedure chnages windows registry entries so be careful while changing authentication mode in SQL Server using T-SQL statements. Make sure to take a backup of windows registry before executing this script.
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'LoginMode', REG_DWORD, 1
Above command has again changed authenticating mode from mixed to windows.
If you are doing other way around then make sure to enable sa login as i suggested above. You can also use below T-SQL statement to enable sa account.
ALTER LOGIN sa ENABLE ;
ALTER LOGIN sa WITH PASSWORD = 'STRONG-PASSWORD' ;
Here, i have explained how to change SQL Server authentication mode from Windows to Mixed mode or vice versa. Please comment us if you have any feedback on this article.
- Fix SQL Server Error 18456: Login failed for user ‘User_Name’.
- How to Change SQL Server Instance Name?
- Create a Logon Trigger to Restrict sysadmin logins to connect to SQL Server for given Time Interval
- Fix Error 18452: Login failed. The login is from an untrusted domain
- Fix SQL Error 18456: failed to open the explicitly specified database - September 18, 2021
- Fix Always ON Connection Timeout Error 35206 in SQL Server - July 23, 2021
- How to Enable Preview Features in Azure Data Studio - July 15, 2021