{"id":75,"date":"2011-12-09T08:14:41","date_gmt":"2011-12-09T08:14:41","guid":{"rendered":"https:\/\/sqlserverlearner.azurewebsites.net\/2011\/12\/09\/microsoft-sql-server-error-18456\/"},"modified":"2011-12-09T08:14:41","modified_gmt":"2011-12-09T08:14:41","slug":"microsoft-sql-server-error-18456","status":"publish","type":"post","link":"https:\/\/sqlserverlearner.com\/2011\/12\/09\/microsoft-sql-server-error-18456\/","title":{"rendered":"sql server error 18456 – Login failed for user"},"content":{"rendered":"

Microsoft sql server error 18456 – Login failed for the user<\/strong><\/p>\n

When you try to connect to SQL Server you get the error 18456.<\/p>\n

Error Message:<\/strong>
\nCannot connect to Servername.
\nADDITIONAL INFORMATION:
\nLogin failed for user ‘username’. (Microsoft SQL Server, Error: 18456)<\/em><\/p>\n

Additional details of the error when connecting from sql server management studio would be like this:<\/p>\n

Server Name: Servername
\nError Number: 18456
\nSeverity: 14
\nState: 1
\nLine Number: 65536<\/em><\/p>\n

Reasons for the error:<\/strong>
\nUser does not have access on the server
\nProvided user name is wrong
\nProvided Password is Wrong
\nUsing windows login credentials for SQL Server authentication
\nLogin for the user is disabled
\nLogin was successfull but access to the server failed
\nProblems with SQL Service
\nPassword for the user has to be changed
\nUser does not have access to perform the specified acton on the server, Only has limited access
\nUser does not have previlages to access the specific database, agent service etc<\/p>\n

The exact actual reason behind this error can be seen in the Server Error log by the server administrator. <\/p>\n

Also the error at a high level can be sorted out by the value of state in the error message.<\/p>\n

According to MSDN Following is the list of error descriptions according to the state of the error: (SQL Server error 18456 error state list)<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
State<\/td>\nDescription Of The State Error<\/td>\n<\/tr>\n
state 2<\/td>\nUser ID is not valid.<\/td>\n<\/tr>\n
state 5<\/td>\nUser ID is not valid.<\/td>\n<\/tr>\n
state 6<\/td>\nAn attempt was made to use a Windows login name with SQL Server Authentication.<\/td>\n<\/tr>\n
state 7<\/td>\nLogin is disabled, and the password is incorrect.<\/td>\n<\/tr>\n
state 8<\/td>\nThe password is incorrect.<\/td>\n<\/tr>\n
state 9<\/td>\nPassword is not valid.<\/td>\n<\/tr>\n
state 11<\/td>\nLogin is valid, but server access failed.<\/td>\n<\/tr>\n
state 12<\/td>\nLogin is valid login, but server access failed.<\/td>\n<\/tr>\n
state 18<\/td>\nPassword must be changed.<\/td>\n<\/tr>\n
state 23<\/td>\nSQL Service is shutting down.<\/td>\n<\/tr>\n
state 38<\/td>\nUser does not have access to the database.<\/td>\n<\/tr>\n
Other States<\/td>\nInternal Server Error<\/td>\n<\/tr>\n<\/table>\n

<\/p>\n

Resolution\/Fix for sql server error 18456:<\/strong>
\nContact the system administrator for help regarding this
\nAccoding to the state of the error, choose the possible solution.
\nChange password if the state is 18
\nFor states 8 and 9 try to login with the correct password
\nFor state 6 use windows authentication instead of sql server authentication
\nFor state 2 and 5 use the correct userid to login<\/p>\n

\nSA login failure error 18456:<\/strong>
\nFrequent SA login failures occur when the authentication mode of SQL Server is changed form windows authentication to SQL Server authentication.
\nIn such cases the following error occurs:
\nLogin failed for user ‘sa’<\/p>\n

Use the following script to enable SA login with a password:
\n[sql]
\nALTER LOGIN sa ENABLE
\nGO
\n–Change the newpassword value below
\nALTER LOGIN sa WITH PASSWORD = ‘newpassword’
\nGO
\n[\/sql]<\/p>\n

\nFollowing are the error and error states:
\nerror 18456 severity 14 state 1<\/strong>
\nerror 18456 severity 14 state 2<\/strong>
\nerror 18456 severity 14 state 5<\/strong>
\nerror 18456 severity 14 state 6<\/strong>
\nerror 18456 severity 14 state 7<\/strong>
\nerror 18456 severity 14 state 38<\/strong>
\nerror 18456 severity 14 state 8<\/strong>
\nerror 18456 severity 14 state 9<\/strong>
\nerror 18456 severity 14 state 11<\/strong>
\nerror 18456 severity 14 state 12<\/strong>
\nerror 18456 severity 14 state 28<\/strong>
\nerror 18456 severity 14 state 38<\/strong><\/p>\n

Complete log of the error message in ssms:
\n[xml]
\nsql server error 18456<\/p>\n

Login failed for user ‘username’.<\/p>\n

Complete error log would look like this:<\/p>\n

===================================<\/p>\n

Cannot connect to servername.<\/p>\n

===================================<\/p>\n

Login failed for user ‘username’. (.Net SqlClient Data Provider)<\/p>\n

——————————
\nFor help, click: http:\/\/go.microsoft.com\/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476<\/p>\n

——————————
\nServer Name: servername
\nError Number: 18456
\nSeverity: 14
\nState: 1
\nLine Number: 65536<\/p>\n

——————————
\nProgram Location:<\/p>\n

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

Microsoft sql server error 18456 – Login failed for the user When you try to connect to SQL Server you get the error 18456. Error Message: Cannot connect to Servername. ADDITIONAL INFORMATION: Login failed for user ‘username’. (Microsoft SQL Server, Error: 18456) Additional details of the error when connecting from sql server management studio would…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[128],"tags":[215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238],"_links":{"self":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/75"}],"collection":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/comments?post=75"}],"version-history":[{"count":0,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/75\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/media?parent=75"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/categories?post=75"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/tags?post=75"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}