locked
Error: 18456 Severity: 14, State: 11 RRS feed

  • Question

  • A dev in our test environment told me about a newly imaged client that couldnt connect to an instance of SQL server.  The client OS is WindowsServer2008R2 Enterprise.  The SQL server OS is WindowsServer2008R2.  The SQL Server version is  2008R2 SP1 10.50.2861.0.

    In the SQL server log, for the client in question, I see:

    Source
      Logon
    Message
      Error: 18456 Severity: 14, State: 11

    Source
      Logon
    Message
      Login failed for user 'MyDomain\NewClient$'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: n.n.n.n]

    So, in dsa.msc I verified this new SQL client is in the same AD SG as its peers. (its peers that are working)

    I verified the SG (thats already in use) has a SQL instance-scoped login. (peers probably wouldnt be working if this wasnt the case)

    I verifed the SQL instance-scoped login has sysadmin role.

    After reading some posts on here, I checked that UAC was disabled on the new SQL client.  I also checked one of its peers, UAC was disabled there as well.

    As a work around test, I RDP'd out to SQL Server box and put the new SQL clients' AD computer account into the local admin group of the SQL server.  The new SQL client now successfully connects to the SQL Server instance.  (i believe this is working via the BUILTIN\administrators SQL login)  So I know the new SQL clients AD computer account is valid. (not like its disabled or locked out or corrupt)

    Any thoughts as to why it wont connect via the SG/SQL-instance-login thats in place?

    I thought, like sometimes when a domain user is added to a new SG, you have to log off and back in to get a new KDC/NTLM token with the new SID to the new SG you just were added to.  Could this be the case?  If so, how do you logoff/login for the computer account to get a new token?  I'm not sure if I can bounce this new box, so I havent tried rebooting it.  Is there a way to get a new token or refresh your existing token without rebooting?

    Any other thoughts on what could cause this failure to connect?

    Thanks!

     

    Friday, February 22, 2013 5:12 PM

Answers

  • You can use xp_logininfo to investigate the current ways in for the login, but you cannot say "this time the account domain\user got in through domain\thatgroup" - rather the account got in through all groups that are authorised to log into SQL Server.

    The login presents its tokens for SQL Server, and SQL Servers sees if any of these logins are permitted to access. If BUILTIN\Administrators is among the tokens, SQL Server may not check the rest, since the user is sysadmin. (If there are three plain-vanilla groups, SQL Server needs to check all three. Even if one is permitted to connect, another may explicitly have been denied access.

    Anyway, the message about infrastructure error is often due to that SQL Server has a different SID for the account that what is in the AD. Whence the recommendation to drop and recreate. As there is no way to alter the SID for a login, I don't think you have much choice.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, February 23, 2013 7:41 PM

All replies

  • Error: 18456, Severity: 14, State: 11 is occure due to reason of Reason of  "Valid login but server access failure".

    As you say other login is working fine, Try to drop & recreate the user that is not working & try again.

     It seems you already go visit but if not, Refer article : http://blogs.msdn.com/b/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx  


    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Friday, February 22, 2013 5:42 PM
  • Thanks Rohit-  I cant drop the SQL login thats in use.  Its named after and mapped to my AD SG (security group) thats in use by peer FE's (front ends) that are working.

    So, I'm trying to compare my new FE box that isnt working compared to some of its peer FE's that are working.

    How would I verify which SQL server instance-scoped login is actually being used by the working FE sessions?  I want to verify they really are using the SQL login (the SQL login thats mapped to my <domain>\<sg>) I think they are and not connecting to the instance via another login, such as the BUILTIN\Administrators login.

    sys.dm_exec_connections/sessions shows the '<domain>\<host>?' name used to authenticate for existing/working sessions, but these DMVs dont show me how the connections were authorized. 

    Is there a way to see this info?  For example, session 55 is using the 'sa' login.  Session 56 is using the 'BUILTIN\Administrators' login.  Session 57 is using <domain>\<sg> login.  Session 58 is using <domain>\<user> login.

    Thanks
    Chris

    Friday, February 22, 2013 9:11 PM
  • You can use xp_logininfo to investigate the current ways in for the login, but you cannot say "this time the account domain\user got in through domain\thatgroup" - rather the account got in through all groups that are authorised to log into SQL Server.

    The login presents its tokens for SQL Server, and SQL Servers sees if any of these logins are permitted to access. If BUILTIN\Administrators is among the tokens, SQL Server may not check the rest, since the user is sysadmin. (If there are three plain-vanilla groups, SQL Server needs to check all three. Even if one is permitted to connect, another may explicitly have been denied access.

    Anyway, the message about infrastructure error is often due to that SQL Server has a different SID for the account that what is in the AD. Whence the recommendation to drop and recreate. As there is no way to alter the SID for a login, I don't think you have much choice.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, February 23, 2013 7:41 PM