[fix]-Microsoft SQL Server Error : 18456

Wikitechy | 1120 Views | sql server | 05 Jun 2016

 

Scenario :

This issue raises when the server come across an error that stops a login from succeeding, the client will exhibit the below error message :

Msg 18456, Level 14, State 1, Server <server name>, Line 1

Login failed for user ‘<user name>’

Fix 1 :

The administrator user must examine the server’s error log. A sample entry can be like:

2006-02-27 00:02:00.34 Logon     Error: 18456, Severity: 14, State: 8.

2006-02-27 00:02:00.34 Logon     Login failed for user ‘<user name>’. [CLIENT: <ip address>]

The error message type can be identified with the ‘State’ value. Here the State value 8 indicates that the authentication is unsuccessful due to improper password.  Error states and descriptions are given below:

ERROR STATE
ERROR DESCRIPTION
2 and 5
Invalid userid
6
Attempt to use a Windows login name with SQL Authentication
7
Login disabled and password mismatch
8
Password mismatch
9
Invalid password
11 and 12Valid login but server access failure
13
SQL Server service paused
18
Change password required

Fix 2 :

1. Open SQL Server Management Studio with 'Run as Administrator' option.


2. When connecting with "SQL Server Authentication" modify the authentication as follows:

    1. Right click server and click properties.
    2. Go to security page.
    3. In Server authentication, select the SQL Server and Windows Authentication mode radio button. (Select both mode).
    4. Click OK.
    5. Restart your server.

Fixes are applicable to the following versions of SQL Server:

  • SQL Server 1.1 (16 bit)
  • SQL Server 4.2A (16 bit)
  • SQL Server 4.2B (16 bit)
  • SQL Server 4.21a
  • SQL Server 6.0
  • SQL Server 6.5
  • SQL Server 7.0
  • SQL Server 7.0 OLAP Tools
  • SQL Server 2000
  • SQL Server 2000 64-bit Edition
  • SQL Server 2005
  • SQL Server 2008
  • Azure SQL DB
  • SQL Server 2008 R2
  • SQL Server 2012
  • SQL Server 2014
  • SQL Server 2016

Related Error Tags :

  • How to Fix Login Failed for User (Microsoft SQL Server, Error: 18456 
  • Understanding "login failed" (Error 18456) error messages in SQL
  • Microsoft SQL Server Error 18456 Login Failed for User 
  • Unable to Connect to SQL Server Due to Error: 18456, Severity: 14
  • How to Fix Login Failed to SQL Server - Error 18456/18452/4064/4062


Workshop

Bug Bounty
IOT Hackathon
Webinar

Join our Community

Advertise