Skip to content

SQLServerLearner

Learn SQL Server Online

  • SQL Server Error List
    • SQL Server error messages list 0 to 1000
    • SQL Server error messages list 1000 to 2000
    • SQL Server error messages list 2000 to 3000
    • SQL Server error messages list 3000 to 4000
    • SQL Server error messages list 4000 to 5000
    • SQL Server error messages list 5000 to 6000
    • SQL Server error messages list 6000 to 7000
    • SQL Server error messages list 7000 to 8000
    • SQL Server error messages list 8000 to 9000
    • SQL Server error messages list 9000 to 10000
    • SQL Server error messages list 11000 to 14000
    • SQL Server error messages list 14000 to 16000
    • SQL Server error messages list 16000 to 18000
    • SQL Server error messages list 18000 to 22000
    • SQL Server error messages list 22000 to 39000
    • Other Languages
      • SQL Server error messages list 0 and 5000 in German
      • SQL Server error messages list 2000 to 5000 in Traditional Chinese
      • SQL Server error messages list 5000 and 10000 in German
      • SQL Server error messages list 8000 to 10000 in Traditional Chinese
      • SQL Server error messages list 0 and 5000 in French
      • SQL Server error messages list 0 to 2000 in Traditional Chinese
      • SQL Server error messages list 10000 and 20000 in German
      • SQL Server error messages list 10000 and 20000 in Traditional Chinese
      • SQL Server error messages list 10000 to 20000 in French
      • SQL Server error messages list 20000 and 50000 in German
      • SQL Server error messages list 20000 and 50000 in Traditional Chinese
      • SQL Server error messages list 5000 to 10000 in French
      • SQL Server error messages list 5000 to 8000 in Traditional Chinese
  • Video Tutorials
  • SQL SERVER LINKS
    • SQL Server Collations
    • t-sql code examples
    • All Posts
      • Random Posts
      • SQL SERVER History
  • About
    • Contact Me
    • Outsource DBA
  • Copyright
  • SQL Server DBA Opening in TCS

Tag: ms sql server error 18456

sql server error 18456 – Login failed for user

Posted on December 9, 2011 by deviprasadg

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 be like this:

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

Reasons for the error:
User does not have access on the server
Provided user name is wrong
Provided Password is Wrong
Using windows login credentials for SQL Server authentication
Login for the user is disabled
Login was successfull but access to the server failed
Problems with SQL Service
Password for the user has to be changed
User does not have access to perform the specified acton on the server, Only has limited access
User does not have previlages to access the specific database, agent service etc

The exact actual reason behind this error can be seen in the Server Error log by the server administrator.

Also the error at a high level can be sorted out by the value of state in the error message.

According to MSDN Following is the list of error descriptions according to the state of the error: (SQL Server error 18456 error state list)

State Description Of The State Error
state 2 User ID is not valid.
state 5 User ID is not valid.
state 6 An attempt was made to use a Windows login name with SQL Server Authentication.
state 7 Login is disabled, and the password is incorrect.
state 8 The password is incorrect.
state 9 Password is not valid.
state 11 Login is valid, but server access failed.
state 12 Login is valid login, but server access failed.
state 18 Password must be changed.
state 23 SQL Service is shutting down.
state 38 User does not have access to the database.
Other States Internal Server Error

Resolution/Fix for sql server error 18456:
Contact the system administrator for help regarding this
Accoding to the state of the error, choose the possible solution.
Change password if the state is 18
For states 8 and 9 try to login with the correct password
For state 6 use windows authentication instead of sql server authentication
For state 2 and 5 use the correct userid to login

SA login failure error 18456:
Frequent SA login failures occur when the authentication mode of SQL Server is changed form windows authentication to SQL Server authentication.
In such cases the following error occurs:
Login failed for user ‘sa’

Use the following script to enable SA login with a password:
[sql]
ALTER LOGIN sa ENABLE
GO
–Change the newpassword value below
ALTER LOGIN sa WITH PASSWORD = ‘newpassword’
GO
[/sql]

Following are the error and error states:
error 18456 severity 14 state 1
error 18456 severity 14 state 2
error 18456 severity 14 state 5
error 18456 severity 14 state 6
error 18456 severity 14 state 7
error 18456 severity 14 state 38
error 18456 severity 14 state 8
error 18456 severity 14 state 9
error 18456 severity 14 state 11
error 18456 severity 14 state 12
error 18456 severity 14 state 28
error 18456 severity 14 state 38

Complete log of the error message in ssms:
[xml]
sql server error 18456

Login failed for user ‘username’.

Complete error log would look like this:

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

Cannot connect to servername.

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

Login failed for user ‘username’. (.Net SqlClient Data Provider)

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

——————————
Server Name: servername
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()
[/xml]

References: MSDN.

Posted in SQL ErrorsTagged 18456 sql, error 18456 severity 14 state 1, error 18456 severity 14 state 11, error 18456 severity 14 state 12, error 18456 severity 14 state 2, error 18456 severity 14 state 38, error 18456 severity 14 state 5, error 18456 severity 14 state 6, error 18456 severity 14 state 7, error 18456 severity 14 state 8, error 18456 severity 14 state 9, event 18456, login failed error sql server, microsoft sql server error 18456, ms sql server error 18456, SA login failure error 18456, sql error 18456, sql login failed 18456, sql server error 18456, sql server error 18456 severity 14 state 11, sql server error 18456 severity 14 state 8, sql server error 18456 state 28000, troubleshooting sql server error 18456, understanding sql server error 18456Leave a Comment on sql server error 18456 – Login failed for user

Recent Posts

  • sp_xp_cmdshell_proxy_account – The provided account was invalid or the ##xp_cmdshell_proxy_account## credential could not be created Access is denied
  • Performance Tuning – Steps to make sure that you are comparing apples to apples
  • TSQL Script to fetch the list of tables with identity properties
  • TSQL Script to Clear SQL Server Cache
  • SQL SERVER 2016 Data Sheet

Recent Comments

  • Bitsqueezer on HOW TO CONVERT TABLE DATA TO XML AND XML TO TABLE
  • Matt on restore higher version database backup on lower version sql server – restore problem
  • Elisandro Lima Armindo on Causes For Network Related Errors in SQL Server
  • Dimssy on sp_MSforeachtable Examples – Uses – InDetail – msdn
  • sql1 on Conversion failed when converting the varchar value xx to data type int.

Archives

  • June 2018
  • August 2015
  • October 2013
  • September 2013
  • April 2013
  • March 2013
  • February 2013
  • January 2013
  • December 2012
  • September 2012
  • August 2012
  • July 2012
  • June 2012
  • May 2012
  • April 2012
  • March 2012
  • January 2012
  • December 2011
  • August 2011
  • July 2011
  • June 2011

Categories

  • 10060
  • 10061
  • CHANGE DATA CAPTURE
  • Collations
  • Configuring SQL Server
  • Credentials
  • DatabaseBackup
  • DBCC Commands
  • Dead Lock
  • Dead Lock Resolution
  • Denali
  • Error Messages
  • Filtered index
  • Identity
  • Indexed Views
  • Interview Questions
  • Msg 10054
  • Msg 107
  • Msg 120
  • Msg 1205
  • Msg 121
  • Msg 127
  • Msg 141
  • Msg 15099
  • Msg 15122
  • Msg 15151
  • msg 15195
  • msg 15405
  • Msg 156
  • Msg 207
  • Msg 229
  • Msg 245
  • Msg 297
  • Msg 402
  • Msg 4606
  • Msg 4621
  • Msg 628
  • Msg 6401
  • Msg 7919
  • Msg 8152
  • Msg 8180
  • Nokia OVI Store
  • Performance Tuning
  • PROXIES
  • Puzzles
  • sp_executesql
  • SQL AZURE
  • SQL Date Time Functions
  • SQL Errors
  • SQL Security
  • SQL Server 2000
  • SQL Server 2008 R2
  • SQL Server 2010
  • SQL Server 2012
  • SQL Server 2016
  • SQL Server Abbrivations
  • SQL Server Basics
  • SQL Server Collations
  • SQL Server Download
  • SQL Server Installation
  • SQL Server Issues
  • SQL Server Links
  • SQL Server Management
  • SQL Server Misconceptions
  • SQL Server Permissions
  • SQL Server Profiler
  • SQL Server Tricks
  • SQL SERVER VARIABLES
  • SQLServerMaximumLimitations
  • SQLServerPedia Syndication
  • Stored Procedures
  • System Stored Procedures
  • T-SQL Code Examples
  • Table Variables
  • Temporary Tables
  • Tools
  • TOP Clause
  • VIEW CHANGE TRACKING
  • VIEW SERVER STATE
  • XML
Proudly powered by WordPress | Theme: ioboot by ioTheme.