Troubleshooting and turning on SQL Server Authentication mode

Scenario

Anyone who has ever connected to a remote system before has invariably encountered authentication or connection issues. Unfortunatly, due to the security implications of connecting to remote systems, the error message returned is not always helpful.

For instance, I came across the message below when trying to connect to a remote SQL Server instance using SQL Server Management Studio (SSMS) and SQL Authentication.

Cannot connect to '{server}'.

Additional information:

Login failed for user '{username}' (Microsoft SQL Server, Error: 18456)

Not initially very helpful.

Troubleshooting

To Google we go!

Hang on. There is a better way to go about this! We have an error code, so a good starting point for detailed information is the SQL Server errors page.

The error page for MS SQL Server error 18456 gives some potential causes, but narrowing down the root cause required more detailed error information.

Server Logs

The SQL Server Log will give you more detailed information related to the error, specifically a more detailed message and a state code to accompany the error code. If you do not have access to the server you will need to request the information from your server administrator. However, if you do have access, there are 2 different ways to view the logs.

1.) On the server itself, the error logs will be located in a director similar to this path:

%Program-Files%\Microsoft SQL Server\MSSQL.1MSSQL\LOG\ERRORLOG

2.) In SSMS you can access the same logs using the Object Explorer to navigate to:

SSMS -> server node -> Management -> SQL Server Logs

Detailed Error

In this case, I quickly found the details of the error:

SQL Server error 18456, severity 14, state 58

Login failed for user {username}. Reason: An attempt to login using SQL authentication failed. Server is configured for Integrated authentication only. [CLIENT:{ip address} ] 2019-03-01 14:25:36.60 Logon Error: 18456, Severity: 14, State: 58.

OK. Here now we have an error state code: 58. Unfortunately the error documentation from Microsoft does not specifically reference a reason for this state. However, the detailed error message tells us that the authentication failed because the server is configured for Integrated (Windows) authentication only and we are trying to log in with SQL Server credentials.

Authentication Modes

We can confirm that the server is indeed NOT set up for mixed authentication in two ways:
1.) By querying the SQL Server property related to Integrated Security:

<span class="token keyword">SELECT</span> SERVERPROPERTY<span class="token punctuation">(</span><span class="token string">'IsIntegratedSecurityOnly'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

• If the return value is 1 then the server is configured for Windows Authentication only.
• If the return value is 0 then the server is configured for Windows and SQL Server Authentication (mixed authentication). See documentation related to server properties for more details.

2.) In SSMS by going right clicking on the server node -> Properties -> Security.

If you are able to and need to change server authentication modes, refer to the authentication mode documentation for details and security issues pertaining to the steps.

Once the authentication mode has been changed, the authentication error should go away and a connection to the server can be established.

Summary

While troubleshooting error codes from any server or software can be challenging, taking the time to track down the proper documentation and detailed error logs can be well worth the time spent. The first few times may be quite time consuming to figure out, but writing down clean documentation and bookmarking resources will go a long way towards helping debug future errors.

This is a MUST BOOKMARK reference for MS SQL Server error logs:
https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-events-and-errors

Featured image by Robert Anasch @ unsplash.com