"Login failed for user" error message when you log on to SQL Server

Article translations Article translations
Article ID: 555332 - View products that this article applies to.
Author: Yuval Sinay MVP
COMMUNITY SOLUTIONS CONTENT DISCLAIMER
MICROSOFT CORPORATION AND/OR ITS RESPECTIVE SUPPLIERS MAKE NO REPRESENTATIONS ABOUT THE SUITABILITY, RELIABILITY, OR ACCURACY OF THE INFORMATION AND RELATED GRAPHICS CONTAINED HEREIN. ALL SUCH INFORMATION AND RELATED GRAPHICS ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT AND/OR ITS RESPECTIVE SUPPLIERS HEREBY DISCLAIM ALL WARRANTIES AND CONDITIONS WITH REGARD TO THIS INFORMATION AND RELATED GRAPHICS, INCLUDING ALL IMPLIED WARRANTIES AND CONDITIONS OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, WORKMANLIKE EFFORT, TITLE AND NON-INFRINGEMENT. YOU SPECIFICALLY AGREE THAT IN NO EVENT SHALL MICROSOFT AND/OR ITS SUPPLIERS BE LIABLE FOR ANY DIRECT, INDIRECT, PUNITIVE, INCIDENTAL, SPECIAL, CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER INCLUDING, WITHOUT LIMITATION, DAMAGES FOR LOSS OF USE, DATA OR PROFITS, ARISING OUT OF OR IN ANY WAY CONNECTED WITH THE USE OF OR INABILITY TO USE THE INFORMATION AND RELATED GRAPHICS CONTAINED HEREIN, WHETHER BASED ON CONTRACT, TORT, NEGLIGENCE, STRICT LIABILITY OR OTHERWISE, EVEN IF MICROSOFT OR ANY OF ITS SUPPLIERS HAS BEEN ADVISED OF THE POSSIBILITY OF DAMAGES.
Expand all | Collapse all

Symptoms

After you install Microsoft SQL Server 2014, SQL Server 2012, SQL Server 2008, SQL Server 2005, or SQL Server 2000 and you try to connect to the server that is running SQL Server, you receive one of the following error messages: 
  • Login failed for user '%.*ls'. The login is a SQL Server login and cannot be used with Windows Authentication.%.*ls
  • Login failed for user '<user_name>'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)
  • Login failed for user '<user_name>'. (Microsoft SQL Server, Error: 18456)

Resolution

This problem occurs if the user tries to log in with credentials that cannot be validated. This problem can occur in the following scenarios:

Scenario 1: The login may be a SQL Server login but the server only accepts Windows Authentication

Collapse this imageExpand this image
assets folding start collapsed
To resolve this issue, configure SQL Server in Mixed Authentication Mode. To do this, follow these steps:
SQL Server 2014, SQL Server 2012, SQL Server 2008, and SQL Server 2005
Collapse this imageExpand this image
assets folding start expanded
  1. Open SQL Server Management Studio. To do this, click Start, click All Programs, click Microsoft SQL Server 20xx (where xx is the version of SQL), and then click SQL Server Management Studio.
  2. Right-click the server, and then click Properties.
    Collapse this imageExpand this image
    2728573
  3. On the Security page, under Server authentication, click the SQL Server and Windows Authentication mode option button, and then click OK.
    Collapse this imageExpand this image
    2728575
  4. In the SQL Server Management Studio dialog box, click OK to restart SQL Server. 

    For more information, see Choose an authentication mode in SQL Server Books Online.
Collapse this imageExpand this image
assets folding end
SQL Server 2000
Collapse this imageExpand this image
assets folding start collapsed
  1. Open the Enterprise Manager. To do this, click Start, click All Programs, click Microsoft SQL Server 2000, and then click SQL Server Enterprise Manager.
  2. Expand the server group.
  3. Right-click the server, and then click Properties.
    Collapse this imageExpand this image
    2728577
  4. Click the Security  tab.
    Collapse this imageExpand this image
    2728579
  5. Under Authentication, click the SQL Server and Windows option button.
  6. Restart SQL Server for the change to take effect.
Collapse this imageExpand this image
assets folding end collapsed
Note When SQL Server is installed by using Windows Authentication mode and is later changed to SQL Server and Windows Authentication mode, the sa login is initially disabled. This causes the state 7 error: "Login failed for user 'sa'." To enable the sa login, see How to: Change Server Authentication Mode.
Collapse this imageExpand this image
assets folding end collapsed

Scenario 2: You are trying to connect by using SQL Server Authentication but the login used does not exist on SQL Server

Collapse this imageExpand this image
assets folding start collapsed
To resolve this issue, verify that the SQL Server login exists. For more information, see Create a login in SQL Server Books Online.
Collapse this imageExpand this image
assets folding end collapsed

Scenario 3: The login may use Windows Authentication but the login is an unrecognized Windows principal

Collapse this imageExpand this image
assets folding start collapsed
An unrecognized Windows principal means that Windows can't verify the login. This might be because the Windows login is from an untrusted domain. To resolve this issue, verify that you are logged in to the correct domain.
Collapse this imageExpand this image
assets folding end collapsed

Additional error information

If you still can't connect to your SQL Server, check the error state in the SQL Server error log to determine the reason for the login failure.

To increase security, the error message that is returned to the client deliberately hides the nature of the authentication error. However, in the SQL Server error log, a corresponding error contains an error state that maps to an authentication failure condition. Compare the error state to the following list to determine the reason for the login failure:
Collapse this tableExpand this table
StateDescription
2User ID is not valid.
5User ID is not valid.
6An attempt was made to use a Windows login name with SQL Server Authentication.
7Login is disabled, and the password is incorrect.
8The password is incorrect.
9Password is not valid.
11Login is valid, but server access failed.
12Login is valid login, but server access failed.
18Password must be changed.

Properties

Article ID: 555332 - Last Review: March 5, 2015 - Revision: 5.0
Applies to
  • Microsoft SQL Server 2012 Standard
  • Microsoft SQL Server 2012 Enterprise
  • Microsoft SQL Server 2012 Developer
  • Microsoft SQL Server 2012 Express
  • Microsoft SQL Server 2012 Web
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Express
  • Microsoft SQL Server 2008 Workgroup
  • Microsoft SQL Server 2008 Web
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 Enterprise Edition

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com