By Hally | Last Updated
This article introduces four ways for SQL Server users to enable Mixed Mode Authentication from installation to use.
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.
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.
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]
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.
Refer to: How to Change SQL Server Authentication Mode
Related Articles:
