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!

By becoming a proud member we can:
  • Switch off annoying ads
  • Create more articles and videos
  • Develop more useful features supporting your learning
  • Provide FREE webinars (for members)
  • Feel our hard work since 2011 is appreciated
Become a proud member and get exclusive features.

If 2% of our 64,943 visitors (Last 30 days) become a proud member than we can quit our jobs and work full time on this website. We started membership in May 2014 and so far 0.07% of our visitors are members and we would like to say THANK YOU to those who become a proud member and support our efforts.

Thanks to members:
  • Education is for FREE and we create more FREE articles and videos
  • We provide new features (members) that can speed up learning.
  • 53% of our page views (82,000 each month) do not have page ads.
  • We switched video ads from 14% of our videos (50 out of 345).
We have invested 4 years of our time in this website and we estimate it will take about 5 years before we can quit our jobs and dedicate all our time to develop considerably our website which should help to truly achieve our vision of free education for everyone; all thanks to support of our members only. Become a proud member

Visitor Voting

Did you know we (Katie & Emil) immigrated to UK in 2005 without knowing English, having any IT skills (or degree) and we set up a website in 2008 to learn by teaching?
Visitor Voting: Yes +13  |  No -6
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




Are you a member?

If yes than please log in

Learning Map is for members with Bronze, Silver and Gold Membership.
For more information visit membership options.


Close this window , log in or learn more about membership benefits
  Become a Proud Member