none
Microsoft SQL Server Error 18456 Severity 14 State 1

    Question

  • I can't seem to connect to our local instance of Microsoft SQL Server. I obtained the followinf infrotmation from the error log and I can't find anything in regards to Severity 14 and state 1. If anyone has any information in regards to this it would be much appreciated. Thanks in advance!

    ===================================

    Cannot connect to 10.1.0.191.

    ===================================

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

    ------------------------------
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

    ------------------------------
    Server Name: 10.1.0.191
    Error Number: 18456
    Severity: 14
    State: 1


    ------------------------------
    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.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()

    Tuesday, June 19, 2007 4:22 PM

All replies

  • Ensure the database is set for both SQL and Windows authentication under SQL server / Properties / Security.
    • Proposed as answer by rasor88 Tuesday, November 09, 2010 10:25 AM
    Wednesday, August 12, 2009 1:56 PM
  • Just thought I'd throw this out there, in the hope that it might help someone else.

    I had trouble logging into the SQL Server (via management studio) with the SA account.  I logged into the server using the domain admin and found the sa account disabled.  I re-enabled the account and logged out.

    I then found that I have State 1, Severity 14 on my sa account and still cannot log in.  I searched much and found nothing.  It dawned on me that the password might be the problem, so I went back in via domain admin and reset the sa password.  I can now log in without problems.

    Hope this helps someone.
    • Proposed as answer by Kevin Hulquest Saturday, February 05, 2011 10:31 PM
    Monday, September 21, 2009 6:41 PM
  • tnx, it was really helpfull
    Tuesday, April 20, 2010 2:49 PM
  • Another trigger for 18456 State 1 in 2008 R2 is the server is configured only for Windows authentication.  Turning on Windows+SQL server authentication will solve it.
    Saturday, February 05, 2011 10:32 PM
  • how do you turn this on?
    Wednesday, February 16, 2011 3:08 AM
  • Travis,

    Check the document http://msdn.microsoft.com/en-us/library/ms188670.aspx & http://www.hosting.com/support/vps/windows/mixedmode for changing the authentication mode .

    For more information on login failure check http://sql-articles.com/articles/troubleshooting/troubleshooting-login-failed-error-18456


    Vidhya Sagar. Mark as Answer if it helps!
    Wednesday, February 16, 2011 6:10 AM
    Moderator
  • Microsoft SQL Server Error 18456 happened to me in 2008 R2 with same detail described above.

    Solution: If you are admin on this machine, Start SQL Server Management Studio in elevated mode (right click on Microsoft SQL Server Management Studio, select Run as adminitrator) then try to connect to the SQL server. This solves the issue for me.

     

    • Proposed as answer by Pants79 Monday, March 28, 2011 8:20 AM
    Thursday, February 17, 2011 2:14 AM
  • Thank you so much for responding, I was able to change the authentication mode to both Windows and Sql Server but i am still receiving this error. I even called my host server just to verify my username and password which are correct.

    I think it is best to explain my situation.

    I have my own website and was previously using SSMS 2005 with Visual Studio 2008 to connect to my host database. While trying to add SQL membershipprovider  I got an error stating that SQL 2005 needed to be upgraded for VS 2008 (Not sure the exact wording but something along those lines)

    So I then updated to SSMS r2 2008

    Im using the same credentials as 2005, I have set the r2 to use both Windows and Sql Authentication.I enter the same credentials as SSMS 2005 but now im getting the 18456 error.

    Please help if you know wht is causing this!

    Thanks again!!

     

    Friday, March 11, 2011 12:44 AM
  • Hi Travis,

    Use this article http://sql-articles.com/articles/troubleshooting/troubleshooting-login-failed-error-18456 and see what state you are hitting for the error 18456 , this will help you to identify the issue and fix it. 


    Thanks, Leks

     

    Friday, March 11, 2011 1:48 AM
  • Thanks Leks, Unfortuately I had already read this page.

    According to the link you sent me my error is either a state 2 or 5 (that is the error message i'm getting) stating "This state occurs when a user logs in with the name that doesn’t exist on the sql server. This error mostly comes in when users specify wrong user name or misspell the user name"

    However I have already confirmed with the host company that my user name and password are correct.

    The hosting company told me that it could probably be a setting on the SSMS r2 and I also think so because the credentials worked great using SSMS 2005.

    Any other suggestions?

    Also i dont know if this helps but I can log in to my SQLEXPRESS instance on laptop(local machine) with no problem. Just the host server that rejects me.

    Thanks again!

     

    Friday, March 11, 2011 2:48 AM
  • If any one is having the same problem as I listed above I was able to work around this by going to my host sql server and create a new login for myself. I still have no idea why the upgrade from SSMS 2005 to 2008 r2 would not authenticate my old login. But any way I AM ABLE TO START WORKING AGAIN!! :)
    Sunday, March 13, 2011 8:14 PM
  • This fixed it for me. Had a clean install of SQL 2008R2 Ent and Server 2008R2. Would not let me login.

    Running the Management Studio elevated fixed it. Could have thought of it myself, but my other SQL Server does not need it. Weird.

    Maybe specifying a group in the Administrator page instead of a specific user makes the difference.

    Monday, March 28, 2011 8:23 AM
  • I even faced the same issue recently and was in search of the solution but did not find a proper solution .Just replying on this thread so that others may find this useful.

    I faced this error because I had not created a BUILTIN\administrators user while installing.

    If you have installed SQL in mixed mode you can login through 'sa' user and then create a BUILTIN\administrators as a login user.

    Note: while installing always while creating the user, type "administrators" not "administrator" and then check names so that the right "BUILTIN\administrator" user will be created and will not face such issue.

    Thursday, July 05, 2012 9:54 AM
  • Hi All,

    SQL Server 2008 r2

    I have windows level permissions but i am unable to connect using windows authentication mode.

    Please find the below error message and help me to resolve the issue

    TITLE: Connect to Server
    ------------------------------

    Cannot connect to (local).
    ------------------------------
    ADDITIONAL INFORMATION:

    Login failed for user 'GVK\ITO.DatabaseL2'. (Microsoft SQL Server, Error: 18456)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    Regards,

    Brahma

    Tuesday, May 07, 2013 6:29 AM
  • Look in the SQL Server error log (or have the DBA to look there) to see what the state is, and the check these blog posts:

    http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx

    http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Proposed as answer by nmehr Monday, June 17, 2013 9:47 AM
    Tuesday, May 07, 2013 9:46 PM
  • hello

    i install sql .in instaletion i write sqldb user for sql autentication . i chose mix aotentication.

    bul when i want connect with sqldb i recive this error

    log in failed user sqldb . microsoft sql server ,error 18456

    state 1 , severity 14

    when i connect with may domain admin  in security -> login -> i dont see sqldb

    i type corect name and pass .

    also i have another problem

    i connect to sql with domain admin and i creat database and i creat vpxuser and

    when i want creat a obdc connection

    i recive this error

    i dont see sqldb (user) in sql -> database -> security

    please help me

    Monday, June 17, 2013 10:15 AM
  • Microsoft SQL Server Error 18456 happened to me in 2008 R2 with same detail described above.

    Solution: If you are admin on this machine, Start SQL Server Management Studio in elevated mode (right click on Microsoft SQL Server Management Studio, select Run as adminitrator) then try to connect to the SQL server. This solves the issue for me.

     


    Yep, for me too, thanks for reminding, once again :)
    Tuesday, July 16, 2013 9:47 AM
  • As pointed out by James Love the above link no longer works. It can be found here; http://history.sqlserver-qa.net/blogs/tools/archive/2007/05/18/msg-18456-level-14-state-1-server-servername-line-1-login-failed-for-user-name.aspx

    w: http://www.the-north.com/sharepoint | t: @JMcAllisterCH | c: http://www.b-i.com

    Wednesday, September 11, 2013 1:58 PM
  • It solved the issue..Thanks a lot..
    Monday, October 28, 2013 6:13 AM
  • This solved my problem. I was using SQL Server Std Edition 2012 on an Amazon EC2 server. I was surprised to discover (thanks to this post) that the default authorization mode was Windows Authentication only. 
    Sunday, March 23, 2014 8:24 PM
  • http://msdn.microsoft.com/en-us/library/cc645917.aspx

    Raju Rasagounder MSSQL DBA

    Monday, March 24, 2014 1:04 AM