This guide explains how to switch SQL Server from Windows Authentication mode to Mixed Mode (Windows + SQL Server authentication). This is necessary when you need to use SQL logins (username/password) for applications, legacy systems, or cross-platform tools. You’ll need sysadmin access to the server and a restart of the SQL Server service.
What You’ll Need
- Administrative access to the Windows server where SQL Server is installed.
- SQL Server Management Studio (SSMS) or access to the registry (for the manual method).
- Permission to restart the SQL Server service (this causes brief downtime).
Step-by-Step
- Open SQL Server Management Studio (SSMS). Connect to the instance you want to change as a user with sysadmin privileges (usually ‘sa’ or a Windows admin).
- Right-click the server name in Object Explorer and select Properties.
- Go to the Security page (on the left side of the Server Properties window).
- Under “Server authentication”, select “SQL Server and Windows Authentication mode” (Mixed Mode).
- Click OK to save the change. A message will appear saying the change will take effect after the service is restarted.
- Restart the SQL Server service. In Object Explorer, right-click the server name and choose Restart. If you’re not connected to the instance, use SQL Server Configuration Manager or the Windows Services console (services.msc) to restart the service.
- Verify the change. After restart, reconnect and check the Security page again to confirm the new mode is active.
Common Mistakes
- Forgetting to restart the service: The change won’t take effect until the service is restarted. Many users think it’s immediate.
- Not having sysadmin rights: You need to be a member of the sysadmin fixed server role to change this setting. If you’re not, the option will be grayed out.
- Choosing the wrong option: Some users accidentally select “Windows Authentication mode” instead of Mixed Mode, which disables SQL logins entirely.
- Not enabling the ‘sa’ account: After switching to Mixed Mode, the ‘sa’ login is often disabled by default. You must explicitly enable it and set a strong password.
Tips
- Use a strong password for ‘sa’: If you plan to use the ‘sa’ account, set a complex password immediately. Better yet, create a dedicated SQL login with only the permissions needed.
- Plan for downtime: Restarting the service will disconnect all users. Do this during a maintenance window.
- Test after restart: Try connecting with a SQL login (e.g., ‘sa’) to ensure it works. If it fails, check if the login is enabled and the password is correct.
- Use T-SQL alternative: You can also change the mode with T-SQL:
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2;then restart. This is useful for scripting.
Troubleshooting
I can’t connect after changing to Mixed Mode.
If you were using Windows Authentication before and now can’t connect at all, it’s likely the service didn’t restart properly. Try restarting the service again. If you still can’t connect, check the SQL Server error log for clues. You may need to start SQL Server in single-user mode to fix permissions.
The ‘sa’ login is disabled or password unknown.
By default, ‘sa’ is disabled in new installations. After switching to Mixed Mode, you must enable it. If you don’t know the password, you can reset it by connecting as a sysadmin (via Windows Authentication) and running: ALTER LOGIN sa WITH PASSWORD = 'NewStrongPassword'; Then enable it: ALTER LOGIN sa ENABLE;
I get an error when trying to restart the service.
This can happen if other services depend on SQL Server. Check the Services console for the SQL Server Agent and SQL Server Browser. You may need to restart them as well. Also ensure you have administrative rights on the server.
Changes don’t seem to take effect.
Double-check that you clicked OK and that the service was actually restarted (not just the SSMS connection). You can verify the current mode by running: SELECT SERVERPROPERTY('IsIntegratedSecurityOnly') AS IsWindowsAuthOnly; If it returns 1, you’re still in Windows mode. If 0, Mixed Mode is active.
Frequently Asked Questions
Q: What is the difference between Windows Authentication and Mixed Mode?
A: Windows Authentication only allows Windows domain or local accounts. Mixed Mode allows both Windows accounts and SQL Server logins (username/password) stored in the SQL Server database.
Q: Will changing to Mixed Mode affect existing Windows logins?
A: No, Windows logins will continue to work. Mixed Mode adds SQL logins as an additional option.
Q: Do I need to restart the SQL Server service every time I change the mode?
A: Yes, the change is only applied when the service restarts. This is a one-time restart per change.
Q: Can I change the authentication mode using the command line?
A: Yes, you can use the xp_instance_regwrite stored procedure or edit the registry directly, but the SSMS method is simpler and less error-prone.
Q: Is it safe to use the ‘sa’ account?
A: It’s not recommended for daily use because it has full administrative rights. Create a dedicated SQL login with minimal permissions for your application instead.
Q: Will changing the mode cause any data loss?
A: No, it only changes how authentication is handled. Your databases and data remain untouched.
Related
General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.
More in this bed
- Fix Error 0x8007025d in Windows 10
- Fix Alexa App Stuck on Setup
- Android Camera for 7 Office Tasks: Step-by-Step
- Pair Phone to Pioneer Stereo After Bluetooth Reset
- Fix Windows Not Saving Remote Desktop Credentials
- Remove Malware from Windows PC & Restore Accounts