none
Non-SysAdmins get error 18456 Severity 14 State 11 Login Failed for user _ Reason Token-based server access validation failed with an infrastructure error.

    Question

  • I have a SQL 2008 R2 system (10.50.4000) where I'm having problems connecting any user that is not a SysAdmin.  Example: I setup a new SQL Login to use Windows Authentication and grant that user db_datareader on the target database.  The user attempts to connect using Excel client or Access or SQL Management Studio and receives Error 18456.  The SQL Server Logs shows Error 18456 Severity 14 State 11 Login Failed for user _ Reason Token-based server access validation failed with an infrastructure error.

    The strange part is that if I temporarily grant the user the sysadmin server role then the user can connect successfully and retrieve data.  But, if I take away that sysadmin server role then the user can no longer connect but again receives the Error 18456 Severity 14 State 11 Login Failed for user _ Reason Token-based server access validation failed with an infrastructure error.

    We've turned off UAC on the client machine to see if that was the problem, but no change.

    I have dropped and re-added the user's SQL Login (and the related database user login info).  No success.

    The Ring Buffers output shows:

    The Calling API Name: LookupAccountSidInternal
    API Name: LookupAccountSid
    Error Code: 0x534

    Thanks for any help.

    -Walt

    Monday, March 17, 2014 2:20 PM

Answers

  • The issue here is most certainly lack of CONNECT permmission somewhere.

    Try this:

    GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO public

    This connect is granted by default, but it can be revoked.

    If this does not work out, please post the output of

    SELECT * FROM sys.server_permissions


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, March 17, 2014 10:37 PM

All replies

  • Monday, March 17, 2014 3:10 PM
  • More info here http://www2.sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Hello Vault,

    You can refer to article by Aron but regarding Infrastructure error documentation is GOOD but not complete.And if you read comments properly someone has pointer it out.Infrastructure error mainly boild down to UAC but I would not recommend you to disable UAC unless very much required.

    In my case I found out that user was part of group and was trying to connect to SQL server.But his ID was not added to group.It was strange why Infrastructure error came when error should be cannot find login or something related to that.

    I guess AD would be involved here.Other solution can be recreate login in AD and then again add it into SQL Server


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    Monday, March 17, 2014 3:30 PM
  • Thank you Rick and Shanky for responding.

    Rick, I have read the the blog you referenced.  In response, we have disabled UAC (for testing).  I have also tried "Run as Administrator".  But, this also does not give the user access.  We are able to reach the Domain Controller.  And, I have dropped the SQL Login (and associated database user) and re-added as recommended.  Still no success.  Is there something else here I'm missing?

    Shanky, you mentioned that in your case the user had not been added to the AD group to give access.  But, in my case I'm bypassing any group issues by creating a SQL Login specific to that user and granting access to the specific database.

    THE ONLY THING THAT WE'VE FOUND THAT ALLOWS THE USER TO CONNECT IS TO GRANT SYSADMIN SQL SERVER ROLE TO THE USER.  (Obviously this is only for testing.)  So, when we do that then the user can connect to the SQL server without error.  IF WE TAKE AWAY THE SYSADMIN ROLE then the user can no longer connect but receives the error mentioned above.

    Any other troubleshooting assistance you can offer would be appreciated.  Thanks.

    -Walt 

    Monday, March 17, 2014 4:04 PM
  • I have a SQL 2008 R2 system (10.50.4000) where I'm having problems connecting any user that is not a SysAdmin.  Example: I setup a new SQL Login to use Windows Authentication and grant that user db_datareader on the target database.  The user attempts to connect using Excel client or Access or SQL Management Studio and receives Error 18456.  The SQL Server Logs shows Error 18456 Severity 14 State 11 Login Failed for user _ Reason Token-based server access validation failed with an infrastructure error.

    -Walt

    Hi,

    If I understand correctly you added a Window authenticated login say 'Wlog'  into SQL Server.Is user connecting using SSMS or is he connecting from some remote location is he connecitng locally .If he is connecting from remote location he has to login into his windows machine with Windows ID created 'Wlog' and then he can access from remote.If you could try with created SQL authenticated login I guess he might succeed.

    But still no clue  why sysadmin works


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Monday, March 17, 2014 4:32 PM
  • Yes, you understand correctly.  The user is logging onto a workstation (not the server) with a Windows Authenticated id.  The user is using either Excel or Access or SSMS and connecting to the server using a Windows Authenticated SQL Login account.  If the account has sysadmin role (which is only for testing) then the connection is successful.  If I take away sysadmin role from the account then the connection is unsuccessful and the SQL Server Log shows Error 18456 Severity 14 State 11 Login Failed for user _ Reason Token-based server access validation failed with an infrastructure error.

    (SQL Authentication is not an option here.  I must use Windows Authentication).

    Any other troubleshooting assistance you can offer would be appreciated.  Thanks.

    -Walt 

    Monday, March 17, 2014 6:10 PM
  • Yes, you understand correctly.  The user is logging onto a workstation (not the server) with a Windows Authenticated id. 

    Is this ID same as the one you have added in your SQL Server .I mean to say like you created a windows authenticated login for 'Wlog' now is you guy logging into workstation machine not the server machine using same 'wlog' or different ID.He should login in workstation machine using same 'wlog' id.

    Is is possible to delete this id ('wlog' in our case) and recreate it in Windows an agin add it in SQL Server and try?


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Monday, March 17, 2014 7:40 PM
  • The issue here is most certainly lack of CONNECT permmission somewhere.

    Try this:

    GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO public

    This connect is granted by default, but it can be revoked.

    If this does not work out, please post the output of

    SELECT * FROM sys.server_permissions


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, March 17, 2014 10:37 PM
  • Erland, that was indeed the problem.  I ran your script: "GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO public" and my public accounts can now connect successfully.  Thanks for the help.  : )

    -Walt

    Tuesday, March 18, 2014 3:51 PM