{"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> Additional details of the error when connecting from sql server management studio would be like this:<\/p>\n Server Name: Servername Reasons for the error:<\/strong> 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 <\/p>\n Resolution\/Fix for sql server error 18456:<\/strong> \nSA login failure error 18456:<\/strong> Use the following script to enable SA login with a password: \nFollowing are the error and error states: Complete log of the error message in ssms: 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 —————————— —————————— —————————— at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) 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}]}}
\nCannot connect to Servername.
\nADDITIONAL INFORMATION:
\nLogin failed for user ‘username’. (Microsoft SQL Server, Error: 18456)<\/em><\/p>\n
\nError Number: 18456
\nSeverity: 14
\nState: 1
\nLine Number: 65536<\/em><\/p>\n
\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\n
\n State<\/td>\n Description Of The State Error<\/td>\n<\/tr>\n \n state 2<\/td>\n User ID is not valid.<\/td>\n<\/tr>\n \n state 5<\/td>\n User ID is not valid.<\/td>\n<\/tr>\n \n state 6<\/td>\n An attempt was made to use a Windows login name with SQL Server Authentication.<\/td>\n<\/tr>\n \n state 7<\/td>\n Login is disabled, and the password is incorrect.<\/td>\n<\/tr>\n \n state 8<\/td>\n The password is incorrect.<\/td>\n<\/tr>\n \n state 9<\/td>\n Password is not valid.<\/td>\n<\/tr>\n \n state 11<\/td>\n Login is valid, but server access failed.<\/td>\n<\/tr>\n \n state 12<\/td>\n Login is valid login, but server access failed.<\/td>\n<\/tr>\n \n state 18<\/td>\n Password must be changed.<\/td>\n<\/tr>\n \n state 23<\/td>\n SQL Service is shutting down.<\/td>\n<\/tr>\n \n state 38<\/td>\n User does not have access to the database.<\/td>\n<\/tr>\n \n Other States<\/td>\n Internal Server Error<\/td>\n<\/tr>\n<\/table>\n
\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
\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
\n[sql]
\nALTER LOGIN sa ENABLE
\nGO
\n–Change the newpassword value below
\nALTER LOGIN sa WITH PASSWORD = ‘newpassword’
\nGO
\n[\/sql]<\/p>\n
\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
\n[xml]
\nsql server error 18456<\/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
\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":"