This article introduces four ways for SQL Server users to enable Mixed Mode Authentication from installation to use.
- Way 1: Enable Mixed Mode Authentication during SQL Server Installation
- Way 2: Enable SQL Server Mixed Mode Authentication by Changing Instance Properties
- Way 3: Enable Mixed Mode Authentication with SQL Query
- Way 4: Enable Mixed Mode Authentication in Registry Editor
Way 1: Enable Mixed Mode Authentication during SQL Server Installation
If you have paid attention to the SQL Server installation, you would find there is a step setting Authentication mode. If you don’t change the default settings, Windows Authentication will become default authentication mode. If you choose Mixed Mode Authentication, the installation progress will ask you to set strong password for SA account.
Once you set SA account during SQL Server installation, Mixed Mode Authentication would be available after finishing installing SQL Server. When you connect to SQL Server database, there would be two authentication modes for you to choose.
Even though you don’t choose Mixed Mode Authentication in SQL Server setup progress, following two ways still could help to enable SQL Server Mixed Mode Authentication.
Way 2: Enable SQL Server Mixed Mode Authentication by Changing Instance Properties
1. Right click on the SQL server instance.
2. Click on Properties.
3. Click on Security on the left pane.
4. Check SQL Server and Windows Authentication mode under Server authentication section. Click OK.
Way 3: Enable Mixed Mode Authentication with SQL Query
EXEC xp_instance_regwrite N’HKEY_LOCAL_MACHINE’,
N’Software\Microsoft\MSSQLServer\MSSQLServer’, N’LoginMode’, REG_DWORD, 2
[Note: 2 indicates mixed mode authentication. 1 is for windows only authentication]
Way 4: Enable Mixed Mode Authentication in Registry Editor
This way applies to users who have no administrator privileges to SQL Server, and cannot run SQL query easily.
Step 1: Open the registry editor.
Step 2: Navigate to:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.10.SQLEXPRESS\MSSQLServer
Step 3: Change the value of LoginMode from 1 to 2.
Step 4: Close the registry editor.
Tips: One thing to note is I had a few different options under the Microsoft SQL Server branch so you may need to take an educated guess if yours is different.