none
Error: 18456, Severity: 14, State: 58 Login failed for user ''

    Question

  • Hi,

    My SQL Server engine is hosted on a server in my domain but the services run under a local account: Server\App

    where App is a local account defined on the system and has sysadmin access.

    My client is hosted on a server in the same domain and it uses SQL authentication to avoid any SPN issues or Kerberos errors.

    But we keep getting error intermittently:

    Source        Logon

    Message
    Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: xxxxxxx]

    Source        Logon

    Message
    Error: 18456, Severity: 14, State: 58.

    I will appreciate some pointers because I am not able to find any help regarding this.

    Regards

    Chandan

    Wednesday, April 25, 2012 7:55 AM

Answers

  • Do you have any idea why TCP should fail if TCP is enabled on a sql server with static port and with correct firewall settings.


    Not that I know, but I can see that there can be incompatibilities. Particularly, since you use a legacy client, I can see that SQL Server needs to use fallback code, which may not be equally well tested.

    Is there any chance that you can upgrade the application to use SQL Server Native Client 10?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, April 26, 2012 9:52 PM

All replies

  • Hi,

    Take a look at this link for help understanding the various 18456 error codes:

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

    Error: 18456, Severity:14, State: 58 means:

    58 State 58 occurs when SQL Server is set to use Windows Authentication only, and a client attempts to log in using SQL Authentication.

    Error: 18456, Severity: 14, State: 58.
    Login failed for user '<x>'.
    Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only

    If you want to continue using SQL Server authentication, you need to enable it in the server properies, as per:

    http://msdn.microsoft.com/en-us/library/ms188670.aspx

    Kerberos is not really that difficult to set-up.  Take a look at: http://www.sqlservercentral.com/articles/Security/65169/.



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    • Proposed as answer by Sudeepta Ganguly Wednesday, April 25, 2012 8:36 AM
    • Unproposed as answer by Chandan jha Thursday, April 26, 2012 7:35 AM
    Wednesday, April 25, 2012 8:30 AM
  • Hi,

    Take a look at this link for help understanding the various 18456 error codes:

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

    Error: 18456, Severity:14, State: 58 means:

    58 State 58 occurs when SQL Server is set to use Windows Authentication only, and a client attempts to log in using SQL Authentication.

    Error: 18456, Severity: 14, State: 58.
    Login failed for user '<x>'.
    Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only

    If you want to continue using SQL Server authentication, you need to enable it in the server properies, as per:

    http://msdn.microsoft.com/en-us/library/ms188670.aspx

    Kerberos is not really that difficult to set-up.  Take a look at: http://www.sqlservercentral.com/articles/Security/65169/.



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    thanks for your reply. but we had set mixed authentication mode because we use a lot of sql users(Non-AD) for our applications. so certainly this is not the reason.

    My client machine uses an ODBC to connect and when I test the ODBC, it shows tested successfully but there is something wrong.

    If you read the error carefully, it shows login failed for ". no name has been mentioned so it means that the name also it was not able to resolve. Profiler trace on sql server machine shows the same error but it does not show any username or hostname which is the client -side information.

    thanks

    chandan

    Thursday, April 26, 2012 7:35 AM
  • Also Kerberos and SPN issues whould come in picture when someone uses a windiows login. We are using an SQL Server login which gets authenticated by SQL Server and not AD.

    So what is missing here.

    Thanks

    Chandan

    Thursday, April 26, 2012 10:11 AM
  • I also want to highlight that the client side is connecting to the server using ODBC(2000) version to my sql server which is 54 but 2008 R2. Can this lead to authentication failures? However, when i test ODBC manually, it shows tested successfully  :-(
    Thursday, April 26, 2012 10:25 AM
  • For 18456 check the error below. Have you restarted SQL Server service after you set it to mixed authentication mode? Can you create one more sql id and check whether if it's working?

    Troubleshooting Error 18456


    Mark as ANSWER if I helped you today :-)

    Thursday, April 26, 2012 10:56 AM
    Moderator
  • For 18456 check the error below. Have you restarted SQL Server service after you set it to mixed authentication mode? Can you create one more sql id and check whether if it's working?

    Troubleshooting Error 18456


    Mark as ANSWER if I helped you today :-)

    Usually all experienced DBAs set the authentication mode to 'mixed' while installing new server. I checked with another id and it works.

    Here is what I did few minutes back:

    I created a user on SQL Server machine: B called test

    Then I logged in to my client server A and opened ODBC window. I opened up the system dsn being used and to test it, I put my user name and password. while doing that, I had a Profiler running on Server A and it correctly shows the connection of login 'test' coming from my hostname.

    However, when the application connects, the profiler does not show any login name. It just shows blank astericks. It cant be an AD issue because we use sql user.

    Any inputs?

    Thanks

    chandan

    Thursday, April 26, 2012 11:26 AM
  • the application uses the same ODBC connections as the one you just edited?  Did you leave your username and password in the ODBC connection when testing the application?   Have you looked at the applications connection string?


    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    Thursday, April 26, 2012 11:34 AM
  • have you seen this: http://sql-kevin.blogspot.co.uk/2011/09/error-18456-severity-14-state-58.html


    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    Thursday, April 26, 2012 11:39 AM
  • the application uses the same ODBC connections as the one you just edited?  Did you leave your username and password in the ODBC connection when testing the application?   Have you looked at the applications connection string?


    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    While testing this ODBC, i put my own credentials 'test' but did not modify anything in the system DSN. And if password had any problem, we should have got errors related to password.

    I am waiting for app guys to send me the connection string. while nothing chnaged on the client side, the sql server was migrated to windows+sql 2008 and something is missing. Earlier i thought it could be SPN but then NTLM is always there and moreover when we use sql autehntication, Active directory should not interfere. anything else you would like to know? Thanks for taking time to read this.

    Thursday, April 26, 2012 11:40 AM
  • have you seen this: http://sql-kevin.blogspot.co.uk/2011/09/error-18456-severity-14-state-58.html


    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    Now, this link sounds interesting. I missed it in the post above. It suggests that we can try using named pipes in the ODBC settings.

    Do you have any idea why TCP should fail if TCP is enabled on a sql server with static port and with correct firewall settings.

    Thanks for this link. it has very limited information with a suggestion but no explanation though. but still useful.

    thanks

    chandan

    Thursday, April 26, 2012 12:11 PM
  • Do you have any idea why TCP should fail if TCP is enabled on a sql server with static port and with correct firewall settings.


    Not that I know, but I can see that there can be incompatibilities. Particularly, since you use a legacy client, I can see that SQL Server needs to use fallback code, which may not be equally well tested.

    Is there any chance that you can upgrade the application to use SQL Server Native Client 10?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, April 26, 2012 9:52 PM