Enable SQL Server authentication in SSMS to let users connect to the database using a specific username and password. As part of our Microsoft SQL Server Support Service, we assist our customers with several SQL Server queries.
SQL Server supports both Windows Authentication and SQL Server Authentication to control access to database instances. By enabling SQL Server Authentication in SQL Server Management Studio (SSMS), users can connect using a SQL Server login and password in addition to Windows credentials. In this article, we will look at how to enable SQL Server Authentication in SSMS and verify that the changes have been applied successfully.
An Overview
Before You Enable SQL Server Authentication
Before changing the authentication mode, make sure the following requirements are met:
| Requirement | Purpose |
|---|---|
| Administrative access | Required to change the server authentication mode. |
| SQL Server instance | Connect to the correct SQL Server instance in SSMS. |
| Planned service restart | The authentication mode change takes effect only after restarting the SQL Server service. |
Enabling SQL Server Authentication in SSMS
We can use SQL Server security in SSMS to connect to the SQL Server database with a username and password unique to the SQL Server instance, rather than using Windows login. Here’s a breakdown of the process:
1. Initially, open the SSMS on the device.
2. In the Object Explorer window, connect to the SQL Server instance that we want to change.
3. In Object Explorer, right-click the linked server’s name and pick “Properties” from the context menu.
4. Go to the “Security” page in the server properties window.
5. Under the “Server authentication” section, find the radio buttons for server verification modes. To allow both methods, select the option “SQL Server and Windows Authentication mode”.
6. When we click “OK” in the dialog box, we will be asked with a message confirming the need to restart the SQL Server service for the changes to take effect.
7. To restart the server, right-click on its name again in Object Explorer and select “Restart”.
Following these steps, the SQL Server instance will be set up to accept both Windows and SQL Server Authentication. This allows users to login with SQL Server Authentication details, such as a username and password, in addition to Windows logins.
Verify the Authentication Mode
After restarting the SQL Server service:
- Disconnect from the current SQL Server session.
- Open the Connect to Server window in SSMS.
- Select SQL Server Authentication as the authentication type.
- Enter a valid SQL Server login and password.
- Connect and confirm that SQL Server Authentication is enabled successfully
Common Issues After Enabling SQL Server Authentication
| Issue | Possible cause |
|---|---|
| SQL Server Authentication option is unavailable | The server authentication mode was not changed successfully. |
| Login fails after enabling Mixed Mode | The SQL Server service has not been restarted. |
| Login failed for user | The SQL Server login is disabled, incorrect, or does not have the required permissions. |
| Unable to connect using SQL Server Authentication | Verify that the correct server name, login credentials, and authentication type are being used. |
[Want to learn more? Click here to reach us.]
Conclusion
SQL Server Authentication lets users connect with SQL Server credentials instead of Windows accounts. After you enable Mixed Mode Authentication in SSMS and restart the SQL Server service, verify the required SQL logins and use strong passwords to help secure the server.
