SQL Server supports two authentication modes: Windows Authentication Mode and Mixed Mode (Windows Authentication and SQL Server Authentication). These modes determine how users connect to the SQL Server instance and authenticate their identity. In this article, we shall discuss how to change change the server authentication mode “SQL Server and Windows Authentication Mode” in SQL Server by using SQL Server Management Studio. Please see how to Fix Microsoft Edge Not Responding on Windows 11.
Also, see How to enable an SA account that has been disabled, how to Install Microsoft SQL Server 2019 and MSSQL Command line tools on Ubuntu Linux, and How to reset MSSQL Server SA Password on Ubuntu.
Windows Authentication Mode
In the Windows Authentication Mode also referred to as the Windows Integrated Security enables users to connect to MSSQL Server using their Windows credentials. This mode leverages the security features of the Windows operating system, allowing users to log in with their Windows username and password.
It helps simplifies user management as SQL Server relies on Windows user accounts, and provides a single sign-on experience for users already authenticated to the Windows domain.
SQL Server Mixed Mode Authentication
While the Mixed Mode allows users to connect to MSSQL Server using either Windows Authentication or SQL Server Authentication. With SQL Server Authentication, users provide a SQL Server login and password for authentication, which is stored within SQL Server. This is practical when users may not have Windows accounts or when connecting from non-Windows platforms.
This Also introduces some security risk in managing additional credentials on the SQL Server thereby introducing potential security risk.
Here is how to fix “Unable to connect to MSSQL Server after changing the Server name“, How to uninstall Microsoft SQL Server on Windows, and VPC, Subnet, NACL, Security Group: Create your own Network on AWS from Scratch [Part 2].
1: Change SQL Server Authentication Mode in Management Studio
I have deliberately installed Microsoft SQL server via the mixed mode just to be able to reproduce these steps for you.
Lanch the SQL Server Management Studio from the Start Menu.
Connect to the SQL Server. See How to uninstall Microsoft SQL Server Management Studio.
In SQL Server Management Studio (SSMS) Object Explorer, right-click the server, and then select Properties.
As you can see below, we have SQL Server and Windows Authentication Mode selected. We have to change this to Windows Authentication Mode only. and click OK.
Yes, before restarting SQL services, let’s do some account clean up and disable the SA account.
Disable SQL SA Account
Note: If Windows Authentication mode is selected during installation, the sa login is disabled and a password is assigned by setup.
Therefore, we will have to disable the SA account and other accounts created during setup.To do this, expand the “Security” and Login as well. Right click on the SA account and select Properties.
Click on Deny permission to the DB and Disable Login.
Delete SQL User Account
I would also like to delete some other user accounts I added. Right click on the account and select “Delete” as shown below.
Confirm Deletion as shown below.
Restart SQL Server Services.
In Object Explorer, right-click your server, and then select Restart. If SQL Server Agent is running, it must also be restarted.
You can do this also from the Service Manager. Search for SQL Service Service and restart it.
Alternatively, you could use the SQL Server 2022 Configuration Manager to restart SQL services.
Restart SQL Server Services.
2: Change SQL Server Authentication Mode in Registry Editor
We can also change SQL Server authentication mode by editing LoginMode value data in Registry Editor.
To do so, click Start and type “regedit” in Run box. Press Enter to open Registry Editor. Expand to open and find “LoginMode” at the right side of window.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL16.\MSSQLServer
Double click LoginMode and a new dialog pops up. Set Value data as 2 if you wish to use the Mixed mode form of authentication. I am fine with the Windows Authentication mode and this is why I am not changing this to the value of 2.
.
Lastly, do not forget to restart
Conclusion
Selecting the right authentication mode depends on your security requirements and the overall architecture of your SQL Server deployment. It’s recommended to use Windows Authentication Mode whenever possible for enhanced security and centralized user management. However, Mixed Mode can be beneficial in scenarios where a combination of Windows and SQL Server logins is necessary.
I hope you found this article “Change from SQL Server and Windows Authentication Mode to Windows authentication Mode” useful. Please feel free to leave a comment below.
