Sql Server Error 18456 Login Failed for User

Input : select * from sys.sysmessages where error = 18456

Output: Login failed for user '%.*ls'.%.*ls%.*ls

This is ane of the infamous fault message (and number) that almost of the DBAs and developers have come up across while working on SQL server. This message simply denotes that the client call was able to reach the SQL server and and so an ACCESS was denied to the particular login for a reason. To effigy out the verbal reason, this error number 18456 with its STATE number is logged into the SQL server error log file, if SQL server was immune or configured to capture the failed logins.

Configuring SQL server for capturing login failures:

Past default, SQL server is configured to capture only failed logins but it tin be changed to whatever of the options as mentioned in this books online link http://technet.microsoft.com/en-u.s./library/ms188470.aspx

Below figure shows these options to have login failed letters written into mistake log

error_18456_1

Accessing Error log:

At present that, nosotros know SQL server logs all login failed messages into its fault log and windows event viewer but how practice I access them?

Windows event viewer:

Become to start –> Run –> Eventvwr –> open upward the application logs, and now we could see the login failed mistake message with computer name, instance name, date and fourth dimension and finally the reason for the login failed

error_18456_2

SQL Error log:

SQL server error log tin can be viewed from multiple places

If nosotros take gained access or can gain admission to SQL server with different logon credentials so always use sp_readerrorlog, xp_readerrorlog or employ the GUI option of opening up Management node –> SQL server Logs –> View –> SQL server log. Same sample shown above looks similar

error_18456_3

In a case where we cannot gain access to SQL server, so we may utilise the actual mistake log path and open the txt file physically from the file system. Use SQL server configuration manager to find the error log path and from there y'all could open the file. Go the value next to –e parameter and that gives the actual error log file location

error_18456_5

Typically the mistake log files are available in install directory for SQL server.

SQL server 2005:

C:\MSSQL\MSSQL.1\MSSQL\LOG\Errorlog

SQL server 2008:

C:\MSSQL\MSSQL10.instanceID\MSSQL\Log\Errorlog

InstanceID – MSSQLSERVER for default instance and for named instance information technology's the name of the case

STATES of 18456

State 1:

This is a very generic error message that is sent to the client tools to deliberately hide the nature of the login failure issue. Notwithstanding, the SQL Server error log, a corresponding error contains an error country that maps to an authentication failure condition with its state number.

error_18456_6

State 2 and 5:

This state occurs when a SQL server login logs in with the name that doesn't exist in sql server. This error mostly comes in when users specify wrong user name or misspell the login proper noun. I am logging in to my instance with a login proper name chosen DOESNTEXIST that actually doesn't exist and let'due south see what the mistake country in mistake log is.

error_18456_7

Error: 18456, Severity: 14, State: 5.

State 6:

This state occurs when a user tries to login with a WINDOWS account but specifying that as a sql server account. I accept a windows account examination (domain\test) only I am specifying it as a sql account and trying to login into SQL server, let's run into what land the error log has

error_18456_8

Error: 18456, Severity: 14, State: six.

Login failed for user 'domain\test'. Reason: Attppting to use an NT business relationship name with SQL Server Hallmark. [Customer: ]

State 7:

This country occurs when a wrong password is specified for a login which is disabled likewise. In this case, my SQL server user 'Leks' is disabled and I'm mentioning a incorrect countersign for the connection

error_18456_9

Mistake: 18456, Severity: 14, Land: 7.

For the accounts (logins) that are disabled and if you specify the correct password, the mistake log is logged with 18470

Error: 18470, Severity: 14, State: one.

State 8:

This country occurs when password is not right in the connexion string for whatever SQL server authenticated logins. I'm logging into SQL server using 'sa' account with wrong password

error_18456_10

Mistake: 18456, Severity: 14, State: viii.

State ix:

This state means that the password was rejected by the password policy check as an invalid ane. The policy API has rejected the password with error NERR_BadPassword. Come across more info on this error and visit http://msdn.microsoft.com/library/default.asp?url=/library/enus/netmgmt/netmgmt/net_validate_output_arg.asp

State ten:

This is one of the rare state and is very well documented here – http://support.microsoft.com/kb/925744

State 11 & 12:

This land means the login was valid but server access failed. One such instance is when a windows login in trying to access sql server that wasn't explicitly added to sql server (at least starting from 2008). To overcome this error, you can add that domain\windows account to sql logins explicitly.

Other reasons for this to happen are when a login is denied access (revoking connect permissions to SQL) to SQL server and UAC issues.SQL server product team covered this country extensively here –http://blogs.msdn.com/b/sqlserverfaq/annal/2010/10/27/troubleshooting-specific-login-failed-mistake-messages.aspx

Windows account which do not accept access

Error: 18456, Severity: 14, State: xi.

Login failed for user 'domain\user'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [Customer:]

SQL account that was denied admission

Error: 18456, Severity: fourteen, State: 12.

Login failed for user 'Leks'. Reason: Login-based server access validation failed with an infrastructure error. Check for previous errors. [Client:]

Country thirteen:

This state occurs when whatever login tries to admission to sql server with services paused on it. Simply for this reason, in that location will likewise be other error number 17142 logged along with 18456.

error_18456_11

Fault: 17142, Severity: 14, Land: 0.

SQL Server service has been paused. No new connections volition be immune. To resume the service, use SQL Figurer Manager or the Services application in Control Panel.

Error: 18456, Severity: fourteen, State: thirteen.

Login failed for user ". Reason: SQL Server service is paused. No new connections tin can be accepted at this time. [CLIENT:]

Country xvi:

This state occurs for logins that do not have access to the target database or the database doesn't exist anymore with the same proper name or offline. We always specify the database in the connexion string equally initial itemize or using -d parameter.

This can be fixed by granting access to the database and sometimes orphan users existing in the database. Orphan users can be fixed by sp_change_users_login

error_18456_12

This state occurs in SQL server 2005 and same is changed to 40 in SQL server 2008 and above

Error: 18456, Severity: fourteen, State: 16.

Country 18:

This state occurs when a sql login is added with USER MUST CHANGE THEIR PASSORD ON Outset LOGON or a login that needs to have its password changed as per the domain or windows password policy ; this state gets in to error log when the new countersign box prompts up.

error_18456_14

This fault is logged with state 18488

Mistake: 18488, Severity: 14, Country: 1.

Country 23:

This state tin happen due to couple reasons; first being simultaneous action of shutting down SQL SERVER and any incoming logins to sql server takes identify. The other 1 is documented here as an issue http://support.microsoft.com/kb/937745

Country 38:

This is like to state sixteen but this was introduced from SQL server 2008 and in a higher place.

Database doesn't exist or login doesn't have access to the database.

Error: 18456, Severity: 14, Country: 38.

State xl:

This state occurs when the login'due south default database doesn't exist in SQL server or offline or the login doesn't have access to the default database. This state is ever logged alongside with fault 4064

Fault: 18456, Severity: 14, Country: 40.

State 58:

This country occurs when a SQL server login is used for accessing SQL server when SQL server is installed with windows hallmark mode.

You may accept to use simply windows login or change the hallmark to mixed authentication for this to work – http://msdn.microsoft.com/en-us/library/ms188670.aspx. The authentication style change always requires a SQL restart to come into effect.

Error: 18456, Severity: xiv, Land: 58.

Login failed for user 'sa'. Reason: An attppt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: <local machine>]

I have tried and covered nearly of usa that I know of, and if you lot find states that I haven't discussed happy to hear and add together to this list. Apart from the fault 18456, there are other login failure errors that you might have to keep an eye on

18451	Login failed for user '%.ls'. Only administrators may connect at this time.%.ls
18452	Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.%.*ls
18458	Login failed. The number of simultaneous users already equals the %d registered licenses for this server. To increment the maximum number of simultaneous users, obtain additional licenses and then register them through the Licensing item in Command Panel.%
18459	Login failed. The workstation licensing limit for SQL Server access has already been reached.%.*ls
18460	Login failed. The number of simultaneous users has already reached the limit of %d licenses for this '%ls' server. Additional licenses should be obtained and installed or you should upgrade to a total version.%.*ls
18461	Login failed for user '%.ls'. Reason: Server is in single user manner. But one ambassador tin connect at this time.%.ls
18462	The login failed for user "%.ls". The password change failed. The countersign for the user is as well recent to modify. %.ls
18463	The login failed for user "%.ls". The password change failed. The countersign cannot be used at this time. %.ls
18464	Login failed for user '%.ls'. Reason: Password change failed. The password does not come across Windows policy requirements considering it is also short.%.ls
18465	Login failed for user '%.ls'. Reason: Countersign change failed. The password does not meet Windows policy requirements because it is too long.%.ls
18466	Login failed for user '%.ls'. Reason: Countersign modify failed. The countersign does not meet Windows policy requirements because it is not circuitous enough.%.ls
18467	The login failed for user "%.ls". The password alter failed. The password does not encounter the requirements of the countersign filter DLL. %.ls
18468	The login failed for user "%.ls". The password change failed. An unexpected error occurred during password validation. %.ls
18470	Login failed for user '%.ls'. Reason: The account is disabled.%.ls
18471	The login failed for user "%.ls". The countersign alter failed. The user does not have permission to modify the password. %.ls
18482	Could non connect to server '%.ls' because '%.ls' is non divers every bit a remote server. Verify that you lot have specified the correct server proper noun. %.*ls.
18483	Could not connect to server '%.ls' because '%.ls' is not defined as a remote login at the server. Verify that you accept specified the correct login name. %.*ls.
18485	Could not connect to server '%.ls' considering it is not configured to take remote logins. Utilise the remote admission configuration selection to permit remote logins.%.ls
18486	Login failed for user '%.ls' because the account is currently locked out. The system administrator can unlock information technology. %.ls
18487	Login failed for user '%.ls'.Reason: The password of the business relationship has expired.%.ls
18488	Login failed for user '%.ls'.Reason: The password of the account must be changed.%.ls
18489	The dedicated ambassador connexion is in utilize by "%.ls" on "%.ls".%.*ls