Microsoft sql server error 18456 login failed for user

My Learning: Log in Learning Report [Log in]


Remove Ads or Log in
Remove Ads or Log in
+2  |  -0

In this blog post I will show you reasons why you might be getting SQL Server error 18456 and show you may be able to solve it.

Contents

18456 Error overview

 Microsoft sql server error 18456 login failed for userWhen connecting to Microsoft SQL Server and trying to use usually SQL Authentication method you may get event ID error 18456 login failed for user you provided. See below example.


This usually means that your connection request was successfully received by the server name you specified but the server is not able to grant you access for a number of reasons and throws error: 18456. This eventID sometimes provides also state 1 which actually isn’t that useful as due to security reasons any error is converted to state 1 unless you can check logging on the server. Microsoft does not provide very useful message boxes so below are some explanations why you get the error.

Below is a list of reasons and some brief explanation what to do:

 

SQL Authentication not enabled: If you use SQL Login for the first time on SQL Server instance than very often error 18456 occurs because server might be set in Windows Authentication mode (only).

How to fix? Check our SQL Server and Windows Authentication Mode page.


Invalid userID: SQL Server is not able to find the specified UserID on the server you are trying to get. The most common cause is that this userID hasn’t been granted access on the server but this could be also a simple typo or you accidentally are trying to connect to different server (Typical if you use more than one server)

Invalid password: Wrong password or just a typo. Remember that this username can have different passwords on different servers.

less common errors: The userID might be disabled on the server. Windows login was provided for SQL Authentication (change to Windows Authentication. If you use SSMS you might have to run as different user to use this option). Password might have expired and probably several other reasons…. If you know of any other ones let me know.

18456 state 1 explanations: Usually Microsoft SQL Server will give you error state 1 which actually does not mean anything apart from that you have 18456 error. State 1 is used to hide actual state in order to protect the system, which to me makes sense. Below is a list with all different states and for more information about retrieving accurate states visit Understanding "login failed" (Error 18456) error messages in SQL Server 2005

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

Remove Ads or Log in

We need you!

Time of free websites that are supported by ads only is over, due to increasing use of ad blocking.

Our website would die probably within a year; as there is no point investing free time and later on own money in something that is doomed.

Our only hope to survive (without any ads) is to ask our visitors like yourself to support it by becoming a member.

We know it's only going to be a tiny % of visitors who will do that but we hope that will be enough to keep our website for free and within a year switch off all ads. (54% page ads are switched off already for visitors from low income countries and 15% video ads are switched off for everyone).

Become a regular supporter (with benefits)
or make a one-off donation

Visitor Voting

Visitor comment (we like):
"I like you both, you read Dale Carnagie, you want what most of us want and you are trying to do something useful for all! You are already successful! Blessings!"
Add comment (no email required).



Add Comment
Name: Glen
Date Posted: 12/4/2011 7:09:24 PM
Comment: Spot on. Thanks for the information


Name: Michał
Date Posted: 12/8/2011 5:04:58 PM
Comment: I have similar problem but I use to login user from domain 'domain\user', I have set SQL authentication, additionaly I try to login from computer which is in other domain, I receive error: Login failed for user 'domain\user'. (Microsoft SQL Server, Error: 18456), do you have any suggestion?


Name: Ed
Date Posted: 12/13/2011 10:31:20 AM
Comment: Hello, I have same problem. I just change the date of my server (dec 2012) and now i can't connect to SQL, i have this error.


Name: Emil
Date Posted: 12/13/2011 2:45:38 PM
Comment: Hi Michal. I did get your email. For benefit of other people I will share my comment. If you try to connect to a different domain (for instance using VPN) you might have to sometimes open SSMS from command line as domain/username and that sometimes work (at least for me in current work). Regarding domain\username I don't think that relates to SQL Authentication. I might be misunderstanding your comment here.


Name: Emil
Date Posted: 12/13/2011 2:47:18 PM
Comment: Ed, I have not come across that before but I would change the date back to correct value and restart database engine service (services.msc) and try again.


Name: Rajesh
Date Posted: 12/29/2011 1:43:47 PM
Comment: this the error i m facing while login first time in window Authentication Mode =================================== Login failed for user 'A\rajanarora'. (.Net SqlClient Data Provider) ------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476 ------------------------------ Server Name: A 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.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server) at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser() Pls help, thanks


Name: Emil
Date Posted: 1/3/2012 11:29:11 PM
Comment: Hi Rajesh. You may try to check this link http://technet.microsoft.com/en-us/library/bb326612.aspx that describes 18456 windows authentication. If you still have the issue please provide sql server version and windows version. Regards Emil


Name: Mac
Date Posted: 3/6/2012 1:52:58 PM
Comment:

This is all completely useless seeing that the error is preventing me from connecting to the database, so how exactly does one intend to change the authentication method if one can't get in?




Name: Emil
Date Posted: 3/7/2012 8:15:47 AM
Comment:

Hi Mac,

This article is for people who manage databases and have windows authentication access to the database and have permissions to perform these actions.

You cannot change authentication if SQL authentication is not enabled and you try SQL Authentication method, SQL Authentication user is also rarely given this kind of permissions due to security.

 




Name: ispurmjay
Date Posted: 6/30/2012 5:55:45 AM
Comment:

You Update ur server ,Install sp1 




Name: Glenn
Date Posted: 7/3/2012 10:35:21 PM
Comment:

 Any one try to connect a 32bit ODBC connection and get 
 

Connection Failed:

SQLState: '28000'
SQL Server Error: 18456
[]Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'domain\username'.

I have verified that I"m a user, I'm a domain admin and own the DBO rights to the database.  Any help would be nice.  Thanks




Name: W@rfi
Date Posted: 7/8/2012 11:33:00 AM
Comment:

Thanks, your post has really helped me ...




Name: adb
Date Posted: 7/10/2012 7:37:03 AM
Comment:

thank you




Name: guillaume
Date Posted: 8/21/2012 9:26:17 AM
Comment:

I hardly comment on blog posts but this time I must say a big *THANK YOU* guys cause I have wasted 2 and a half hours trying to get that stupid thing working. I don't understand why Microsoft makes this whole SQL authentication process being so difficult and yet their support pages don't tell you about that option.

This has been ridiculously hard and painful to find on google and I am glad this page exists.

 

Thanks again !

G




Name: Srinivas
Date Posted: 8/23/2012 10:00:05 AM
Comment:

Thanks a lot for this page. Its helps a lot




Name: Alex
Date Posted: 8/27/2012 2:08:50 PM
Comment:

Sometimes when you create the user and forget to restart the server and services you would have that error message. So I suggest after creating a login, ensure that the server and service are restarted to ensure that you changes take effect.




Name: Katie and Emil
Date Posted: 8/29/2012 6:30:05 PM
Comment:

Thanks for all your comments. It's really nice to see our effort to write good articles is appreciated.

Katie & Emil




Name: omprakash
Date Posted: 9/4/2012 7:23:48 AM
Comment:

Thanks!




Name: Kevin
Date Posted: 11/19/2012 7:49:11 AM
Comment:

Thank you very much for your help.

 




Name: Katy
Date Posted: 11/26/2012 12:45:53 PM
Comment:

Hello, I have head each from sql 2012 installation process. I've installed SQL server 2012 on my computer , and I cannot connect to server with my user account, It's strange ,I have uninstalled SQL and installed again , but it just does not work. Could you help me, please ?




Name: Emil
Date Posted: 11/27/2012 7:21:03 PM
Comment:

Hi Katy,

If you perform the installation yourself than generally it should cause any issues. I suspect you get 18456 error? when you connect to database engine do you select windows authentication (without typing your credentials)? I suspect you added yourself during the setup as admin?

Regards

Emil




Name: Vincent
Date Posted: 12/19/2012 8:38:09 AM
Comment:

Hello,

I meet an error message when I try to connect on Database Engine with a Windows Account. (Error 18456, state 1)

This problem is very strange because when I try to connect to other server type (analysis services, report services, ...), it is a success with a Windows account.

I verify in 'Server Properties' and the option 'SQL Authentification and Windows Authentification' is selected.

For information, I use SQL Server 2008.

Thanks for your help

Vincent




Name: arul
Date Posted: 12/22/2012 5:05:11 AM
Comment:

Thanks for your detailed answer. I have solved the issue




Name: Shannon
Date Posted: 1/16/2013 9:08:54 PM
Comment:

Presented clearly, very helpful. Thx.




Name: reetika
Date Posted: 2/6/2013 9:42:42 AM
Comment:

thanku..... its really helpful for me.....:)

 




Name: balaram
Date Posted: 3/7/2013 7:33:15 AM
Comment:

Thanks a lot...it help me to solve the issue.




Name: Herbert
Date Posted: 5/31/2013 1:36:26 PM
Comment:

Thanks for the post! Really resolved my issue.




Name: Winie
Date Posted: 5/22/2014 4:59:28 PM
Comment: I was working with sql server 2012.Initially I could login successfully with my userId but suddenly after some days I am getting SQL Server Error as Login failed for user 'sa'.(Microsoft SQL Server, Error:18456).I am not able to connect to (local) now. Can anyone help me to solve this problem??


Name: Emil
Date Posted: 5/23/2014 7:20:44 AM
Comment: Hi Winie, Are you to connect using Windows account? If not then the Database Engine service might not be running. Go to Control Panel // Search for 'Services' click 'view local services' find SQL Server Database Instance and ensure status column says 'Running' Take care Emil


Name: Rajeev Sekhar
Date Posted: 6/4/2014 5:35:49 AM
Comment: Simple thing but helped me a lot. thank................ keep going