IN THIS ARTICLE

Enable Mixed Mode Authentication for SQL Server

Incorrect email!

The article was successfully sent to the email

Solution


Mixed Mode (SQL Authentication Mode) provides a System Administrator (SA) account using a separate user name (e.g. SA) and password that can also be used to connect to the SQL server in addition to the Windows account.

During the SQL Server installation, after the "Server Configuration" step, the "Database Engine Configuration" step will appear as below.



During this step, you will be able to add users (such as your account) to the group of SQL Server Administrators; click on Add Current User radio button if it is not already highlighted.
Then, specify a password for this SA account and continue with the installation process.

After implementation of those settings you'll be able to use this SA account to connect to the SQL server with Administrator privileges as an alternative to the Windows account.
If the Windows user changes or if there are issues with Windows authorization, you will have the ability to use the SA account to connect to the SQL server and implement any needed changes (change user access, maintain database engine etc.).

An alternative solution

You may change authentication to mixed mode through windows registry key editing.
Open the registry and search for key LoginMode under for your SQLEXPRESS instance:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQLServer
Change the LoginMode value to 2.

Open SSMS and execute the query below:
USE [master]
GO
ALTER LOGIN sa ENABLE;
GO
ALTER LOGIN sa WITH PASSWORD = 'trbonet.com';
GO

Helpful?
We're glad this article helped.

Thanks for letting us know. What went wrong?