This guide walks you through enabling the built-in sa (system administrator) login in Microsoft SQL Server, which is disabled by default for security. You’ll learn how to turn it on using SQL Server Management Studio (SSMS) or T-SQL, set a strong password, and configure the server’s authentication mode so the account can actually be used.
What You’ll Need
- Access to a Windows account with sysadmin privileges on the SQL Server instance (usually the account used to install SQL Server).
- SQL Server Management Studio (SSMS) installed, or the ability to run a T-SQL script via sqlcmd or another query tool.
- The SQL Server service must be running.
- If the server is a default instance, you connect to
localhostor.; if it’s a named instance, uselocalhost\InstanceName.
Step-by-Step
- Open SQL Server Management Studio and connect to your instance using Windows Authentication (or any login that has sysadmin rights).
- Check the server’s authentication mode. Right-click the server name in Object Explorer, select Properties, then go to the Security page. Under “Server authentication,” choose SQL Server and Windows Authentication mode and click OK. If you change this setting, you’ll need to restart the SQL Server service.
- Restart the SQL Server service (if you changed the authentication mode). You can do this in SSMS by right-clicking the server and selecting Restart, or via SQL Server Configuration Manager.
- Locate the sa login. In Object Explorer, expand Security → Logins, then right-click sa and select Properties.
- Set a strong password. In the General page, enter a new password in the “Password” and “Confirm password” fields. Use a mix of uppercase, lowercase, digits, and symbols—at least 8–12 characters.
- Enable the login. In the same General page, check the box labeled “Login is enabled” (next to “Status” at the bottom). Click OK.
- Verify the sa account works. Disconnect from SSMS and reconnect using SQL Server Authentication, username
sa, and the password you just set. If it connects, you’re done.
Alternative: Enable sa via T-SQL
- Open a new query window in SSMS (connected with sysadmin rights).
- Run the following script, replacing
YourStrongPasswordwith a real strong password:
ALTER LOGIN sa WITH PASSWORD = 'YourStrongPassword'; GO ALTER LOGIN sa ENABLE; GO
- If the server is in Windows Authentication mode, you’ll also need to change it to mixed mode. Run:
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2; GO
Then restart the SQL Server service.
Common Mistakes
- Forgetting to change the authentication mode. Even if you enable sa, the server will not accept SQL logins if it’s still in Windows Authentication mode.
- Not restarting the service. Changing the authentication mode requires a restart to take effect.
- Using a weak or empty password. SQL Server may enforce password policy, and a weak password can be a security risk.
- Enabling sa but leaving it with a default or blank password. This is a severe security hole.
- Not checking if the sa login is locked out. If the account is locked due to failed attempts, you’ll need to unlock it in the login properties.
Tips
- Always use a strong, unique password for sa, and store it in a secure password manager.
- Consider using Windows Authentication or a dedicated SQL login with only the needed permissions instead of sa for everyday tasks.
- If you’re enabling sa for a specific application, create a separate login with the least privileges required, rather than using sa.
- After enabling sa, monitor its usage in the SQL Server logs to detect any suspicious activity.
- If you’re on a domain, you can also use a group-managed service account, but that’s beyond the scope of this guide.
Troubleshooting
- “Login failed for user ‘sa’” – This usually means the password is wrong, or the account is disabled. Double-check the password and ensure the login is enabled.
- “Cannot connect to the server” – Verify that the SQL Server service is running. Also check if TCP/IP is enabled in SQL Server Configuration Manager (for remote connections).
- “The server is in single-user mode” – If you’re trying to connect while another session is active, you may need to disconnect other sessions or use the
masterdatabase with the-moption in sqlcmd. - “Cannot restart the service” – Ensure you have administrator rights on the machine. You can also restart via Windows Services (services.msc).
- “The sa login is locked out” – In SSMS, right-click sa → Properties → Status, and uncheck “Login is locked out.”
- Authentication mode change doesn’t stick – Make sure you clicked OK and restarted the service. You can verify the registry value:
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer\LoginModeshould be2for mixed mode.
Frequently Asked Questions
Why is the sa account disabled by default?
Microsoft disables sa by default to reduce the attack surface, since it’s a well-known login name that attackers target. Enabling it should only be done when necessary.
Can I enable sa without restarting the SQL Server service?
If you only enable the login (via ALTER LOGIN), no restart is needed. However, if you change the authentication mode from Windows to Mixed, a restart is required.
What is the default password for sa?
There is no default password. When SQL Server is installed, the sa account is disabled and has no password. You must set one when you enable it.
Is it safe to use sa for a production application?
It’s not recommended. The sa account has unrestricted access to the entire server. Use a dedicated login with only the necessary permissions for the application.
Can I enable sa using sqlcmd?
Yes. Connect using Windows Authentication and run the ALTER LOGIN statements as shown above. Example: sqlcmd -S localhost -E -Q "ALTER LOGIN sa WITH PASSWORD = '...'; ALTER LOGIN sa ENABLE;"
What if I forgot the sa password after enabling it?
If you have sysadmin access via Windows Authentication, you can reset the sa password using the same ALTER LOGIN command. If you don’t, you’ll need to start SQL Server in single-user mode to regain access.
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
- Hard Drive Very Slow Suddenly? Fixes That Work
- Windows Insider: Check New Windows 10 Features
- Restore Pitted Chrome Sink Drain Surrounds
- Error Performing Query Error Facebook: Fixes
- WiFi Not Working on HTC 10? Solved
- Fix Windows 100% CPU Usage: 7 Real Fixes