Unable to login to SQL Server SQL Server Authentication Error 18456

Question

I have created login account on my localhost\sql2008 Server (Eg. User123)

Mapped to Database (default)

Authentication Mode on SQL Server is set to both (Windows and SQL)

But login to SQL Server fails with following message (for User123)

Note: Have checked multiple time that UserName / Password are typed in correctly

Error Details:

Login failed for user 'User123' (Net.SqlClient Data Provider)

Server Name : localhost\sql2008 Error Number: 18456 Severity : 14 State : 1 Line Number : 65536

any help on this please.

User · Answer

The correct solution to the problem is to make sure SQL server authentication is turned on for your SQL Server

User · Answer

After enabling  quot SQL Server and Windows Authentication mode quot  check above answers on how to   navigate to the following   Computer Mangement in Start Menu  Services And Applications SQL Server Configuration Manager SQL Server Network Configuration Protocols for MSSQLSERVER Right click on TCP IP and Enable it   Finally restart the SQL Server

User · Answer

You need to enable SQL Server Authentication:

In the Object Explorer, right click on the server and click on "Properties"

DBMS Properties dialog

In the "Server Properties" window click on "Security" in the list of pages on the left. Under "Server Authentication" choose the "SQL Server and Windows Authentication mode" radio option.

SQL Server Authentication dialog

Restart the SQLEXPRESS service.

User · Answer

By default login failed error message is nothing but a client user connection has been refused by the server due to mismatch of login credentials  First task you might check is to see whether that user has relevant privileges on that SQL Server instance and relevant database too  thats good  Obviously if the necessary prvileges are not been set then you need to fix that issue by granting relevant privileges for that user login   Althought if that user has relevant grants on database  amp  server if the Server encounters any credential issues for that login then it will prevent in granting the authentication back to SQL Server  the client will get the following error message   Msg 18456  Level 14  State 1  Server  lt ServerName gt   Line 1 Login failed for user   lt Name gt     Ok now what  by looking at the error message you feel like this is non-descriptive to understand the Level  amp  state  By default the Operating System error will show  State  as 1 regardless of nature of the issues in authenticating the login  So to investigate further you need to look at relevant SQL Server instance error log too for more information on Severity  amp  state of this error  You might look into a corresponding entry in log as   2007-05-17 00 12 00 34 Logon     Error  18456  Severity  14  State  8  or  2007-05-17 00 12 00 34 Logon     Login failed for user   lt user name gt      As defined above the Severity  amp  State columns on the error are key to find the accurate reflection for the source of the  problem  On the above error number 8 for state indicates authentication failure due to password mismatch  Books online refers  By default  user-defined messages of severity lower than 19 are not sent to the Microsoft Windows application log when they occur  User-defined messages of severity lower than 19 therefore do not trigger SQL Server Agent alerts   Sung Lee  Program Manager in SQL Server Protocols  Dev team  has outlined further information on Error state description The common error states and their descriptions are provided in the following table     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 12         Valid login but server access failure 13                SQL Server service paused 18                Change password required   Well I m not finished yet  what would you do in case of error   2007-05-17 00 12 00 34 Logon     Login failed for user   lt user name gt      You can see there is no severity or state level defined from that SQL Server instance s error log   So the next troubleshooting option is to look at the Event Viewer s security log  edit because screen shot is missing but you get the  idea  look in the event log for interesting events

User · Answer

You can access this by  Right click on instance  IE SQLServer2008  Select  Properties  Select  Security  option Change  Server authentication  to  SQL Server and Windows Authentication mode  Restart the SQLServer service     Right click on instance     Click  Restart    Just for anyone else reading this   This worked for me on 2012 SQL Server too  Thanks

User · Answer

I had this same problem  however mine was because I hadn t set the Server authentication to  SQL Server and Windows Authentication mode   which you had  I just wanted to mention it here in case someone missed it in your question   You can access this by   Right click on instance  IE SQLServer2008  Select  Properties  Select  Security  option Change  Server authentication  to  SQL Server and Windows Authentication mode  Restart the SQLServer service   Right click on instance Click  Restart

[sql-server] Unable to login to SQL Server + SQL Server Authentication + Error: 18456

The answer is

Examples related to sql-server

Examples related to sql-server-2008

Tags