Issue : I am able to connect to MSSQL Server Management Studio with Windows Authentication but unable to connect with SQL Server Authentication. The 'sa' login details are correct but still getting the following error message.
Login failed for user 'sa'. (Microsoft SQL Server, Error: 18456
Solution :
In order to resolve the issue, please refer to the following steps:
1. Login to the MSSQL Server Management Studio with Windows Authentication.
2. In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.
3. Under the Server Properties, select a page of "Security".
4. Select the Server authentication as "SQL Server and Windows Authentication mode" and click Ok.
5. Restart the SQL Services and then try to login with 'sa' details.
How to trouble shoot it SQL error 18456 ?
Login failure errors. Based on error state we will troubleshoot that.
ERROR STATE ERROR DESCRIPTION
2 and 5 Invalid userid
6 Attempt to use a Windows login name with SQL Authentication
7 Login disabled and password mismatch
8 Password mismatch
9 Invalid password
11 and 12 Valid login but server access failure
13 SQL Server service paused
18 Change password required
How to trouble shoot it SQL error 18456 ?
Login failure errors. Based on error state we will troubleshoot that.
ERROR STATE ERROR DESCRIPTION
2 and 5 Invalid userid
6 Attempt to use a Windows login name with SQL Authentication
7 Login disabled and password mismatch
8 Password mismatch
9 Invalid password
11 and 12 Valid login but server access failure
13 SQL Server service paused
18 Change password required
