SQL Server – Login failed for user ‘[username]’ – How to troubleshoot?

You try to login to SQL Server and you get a really nasty message:

Cannot connect to mysqlserverinstance.

===================================

Login failed for user 'myuser'. (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&amp;EvtSrc=MSSQLServer&amp;EvtID=18456&amp;LinkId=20476

------------------------------
Server Name: mysqlserverinstance
Error Number: 18456
Severity: 14
State: 1
Line Number: 65536

------------------------------
Program Location:

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

The key data in the error message

At first glance, it appears to be utterly useless. The only useful message seems to be “Login failed for user ‘myuser‘.” but what we really need to know is “why?”.

That information is somewhat buried in this section of the message:

Server Name: mysqlserverinstance
Error Number: 18456
Severity: 14
State: 1

Based on the documentation for this error, the severity and state actually mean something. I have copied and pasted the same from the link.

Reasons

[BEGIN COPY]

State	Description
1	Error information is not available. This state usually means you do not have permission to receive the error details. Contact your SQL Server administrator for more information.
2	User ID is not valid.
5	User ID is not valid.
6	An attempt was made to use a Windows login name with SQL Server Authentication.
7	Login is disabled, and the password is incorrect.
8	The password is incorrect.
9	Password is not valid.
11	Login is valid, but server access failed. One possible cause of this error is when the Windows user has access to SQL Server as a member of the local administrators group, but Windows is not providing administrator credentials. To connect, start the connecting program using the Run as administrator option, and then add the Windows user to SQL Server as a specific login.
12	Login is valid login, but server access failed.
18	Password must be changed.

[END COPY]

In our case, “State: 1” translates to:

Error information is not available. This state usually means you do not have permission to receive the error details. Contact your SQL Server administrator for more information.

Login failure reason is hidden in the error log to dissuade hackers!

This does not help you very much and that is by design. SQL Server is trying not to reveal potential hackers what is exactly wrong with what they are trying to do but your DBA should be able to look the SQL Server error log (using xp_readerrorlog) and find the actual error corresponding to the time of the login failure

	2/27/2014 11:07 AM	Logon	Login failed for user ‘myuser’. [CLIENT: 11.24.58.119]
	2/27/2014 11:14 AM	Logon	Error: 18456, Severity: 14, State: 5.

Now, we know that “State: 5” translates to:

User ID is not valid.

From there, it is easy to know why. Again, the possible reasons are documented.

[BEGIN COPY]

If you are trying to connect using SQL Server Authentication, verify that SQL Server is configured in Mixed Authentication Mode.

If you are trying to connect using SQL Server Authentication, verify that SQL Server login exists and that you have spelled it properly.

If you are trying to connect using Windows Authentication, verify that you are properly logged into the correct domain.

If your error indicates state 1, contact your SQL Server administrator.

If you are trying to connect using your administrator credentials, start you application by using the Run as Administrator option. When connected, add your Windows user as an individual login.

If the Database Engine supports contained databases, confirm that the login was not deleted after migration to a contained database user.

When connecting locally to an instance of SQL Server, connections from services running under NT AUTHORITY\NETWORK SERVICE must authenticate using the computers fully qualified domain name. For more information, see How To: Use the Network Service Account to Access Resources in ASP.NET

[END COPY]

In this particular case, it turned out that the user tried to login using Windows ID but was using “SQL Server Authentication” in the login dialog. Since the user did not exist as a SQL Server user, it promptly reported it as “User ID is not valid”.

Summary

In case you need to run as a different user you could do it in a number of ways

Launch SSMS as that user using “Run As Administrator” and then do “Windows Authentication”
Login to windows using the other windows ID and just launch SSMS (or) you .
Set a shortcut to always “Run As Administrator” with that ID

One thought on “SQL Server – Login failed for user ‘[username]’ – How to troubleshoot?”

Leave a comment Cancel reply