locked
Unique Experience with Error 18456 (state 11) Login Failure RRS feed

  • Question

  • Greetings All -

    I have a production SQL 2005 system (v9.00.4053.00 / SP2) which behaves differently on Login depending upon whether I'm connected to the server locally (via remote desktop --> SSMS on server) or remotely (via SSMS tools). My authentication method is integrated/windows auth with my domain credentials. My domain account is both a windows system admin, and a sql server admin.

    NOTE: There *is* a versioning difference; the client tools on the server are from SQL 2005, & the remote (local) connection is established using SSMS tools for SQL 2008. Not sure that this should make a difference, but seems noteworthy.

    The problem is this : When I attempt to login to the server locally, I receive the basic login error: [Login failed for user 'mydomain\myaccount' ... error 18456] which is displayed in greater detail in the sql log with a state of 11. I know that at this point the problem could point to a few potential issues, but this is where the uniqueness comes in. When connecting from my client (local) machine using the SSMS 2008 tools, I am able to login without the error, view the log, query and such without issue. This is actually how I was able to see the state 11 error in the SQL logs. My logs are otherwise clean, default databases online, etc.

    So I thought I'd log a posting here and see if there are any ideas as the to underlying cause. I've read that for this error the state 11 maps to "Valid login; Server access failure". However, most of what I've read (online) isn't applicable to this situation, so I must be missing something. Any thoughts on what might be preventing me from logging in locally?

    Thanks, in advance, for the help !

    Thursday, April 8, 2010 4:31 PM

Answers

  • Hi,

    There might be a security issue. Could you please try to use SQL Server Authentication Troubleshooter (SSAT) to analysis this “Login Failed” error?

    For more information about SSAT and download link, seeSQL Server Authentication Troubleshooter from MSDN BLOG.

    Thanks,
    Chunsong


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Thursday, April 15, 2010 1:35 AM

All replies

  • Hi,

    Thank you for posting here!

    The login failed error 18456, state 11 means that “Valid Login but server access failure” as you said.

    Based on my understanding, you use the same Windows Domain Account to log on to local server running SQL 2008 and remote server running SQL 2005 (using Remote Desktop Connection). Also, you can connect to SQL 2005 remotely at local server but not locally via Remote Desktop Connection (RDC).

    In this way, you should be able to connect SQL 2005 in both ways because you are using the same credential. However, it is strange that it sates “Login failed: error 18456, state 11” which means “valid login but server access failure”. Could you please try the following and provide results here?

    How about logging on to server running SQL 2005 locally (not via RDC) using same domain account, and connecting to SQL 2005 using Windows Authentication?
    Have you encounter this problem while connecting to other SQL servers via RDC?

    Thanks,
    Chunsong


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Monday, April 12, 2010 6:38 AM
  • Hi Chunsong - Thanks so much for taking the time to respond!

    For this system I am logging into via RDC, it's actually a virtualized OS sitting on top of (what I believe is) a VMWare host. The operating system is Windows Server 2008. I don't think that I have this problem occuring on any other system right now, but should it arise I will attempt the login locally and see if the result is different from that via RDC.

    Do you think it's related to the host being virtualized, or perhaps with the attempt to login via remote desktop session itself? 

    Tuesday, April 13, 2010 10:10 PM
  • Hi,

    There might be a security issue. Could you please try to use SQL Server Authentication Troubleshooter (SSAT) to analysis this “Login Failed” error?

    For more information about SSAT and download link, seeSQL Server Authentication Troubleshooter from MSDN BLOG.

    Thanks,
    Chunsong


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Thursday, April 15, 2010 1:35 AM
  • Thanks ChunSong - Apologies for the delay, but I've been away the last few days. I will research the SSAT , & give that a try today.

    Tuesday, April 20, 2010 11:42 AM