SQL Event ID 18456: Login failed for user Reason: Token-based server access validation failed


If you get event ID 18456 with Source MSSQLSERVER in your application and SQL logs with the verbiage of "Login failed for user 'domain\service.account.you.use'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors" somebody may have messed with your "CONNECT SQL" permissions.
Everybody who reads this blog knows that I'm not a SQL guy, but a lot of advice on the internet suggests that this is UAC related so some of you Platform and AD folks might get asked about the error. Especially considering that the error suggests an issue with tokens and authentication, rather than permissions.
Though this isn't the only potential root cause, a deny or revoke or lack of grant can cause this. Especially on 2008 R2. In 2012 a lot of rules changed in regards to grouping but in 08R2 the loss of this would potentially cause various outages.
In our case what was happening was the configuration server was no longer accessible by the various SQL servers and this was appearing on that server every minute.
Try this:
select * from sys.server_permissions
and
select * from sys.server_principals
This may turn up that builtin\administrators or the service account (or some other important security principle) has a specific "deny" on "connect sql" or… revoke, or even a simple lack of grant.


Comments (20)

  1. Anonymous says:

    Fix the "CONNECT SQL" permissions. As stated, at least in our case it was 08R2 and the builtinadministrators or the service account (or some other important security principle) has a specific "deny" on "connect sql" or… revoke, or even a simple lack
    of grant. Put this back to default and see if it fixes your issue. What happened to us was that some SQL admin who was provisioning some SQL2012 boxes made changes to ALL the SQL servers (including the 08R2’s) and that caused the problem. In 2012 this wouldn’t
    break anything. In 08R2 you need the "CONNECT SQL" permissions for the security principle you’re using.

  2. Anonymous says:

    Well, as I mentioned in my post, sorry… I’m really not a SQL expert. Just happened to run across this issue and found the solution posted above in an internal KB.

  3. Anonymous says:

    I’m tempted to delete that comment, but I’m amused by how arrogantly inaccurate everything from your statement to your "name" are – so I’m leaving it here to give me a laugh.
    For all the rest of the "real" IT people who read my forum, this has been validated as a solution to fix this exact problem on many occasions. We have also published this as an internal KB to address the issue when people call in.

  4. Huh? says:

    I have this error but I have no idea what you just wrote about it. What do I need to do to fix it?

  5. shaun says:

    Same issue. also have no clue on how to fix this

  6. Dan says:

    This is 100% accurate. Not sure how the deny got put there, but it was correct. Great job

  7. SQL DBA says:

    This is not accurate, the reason could be AD trust related.

  8. hdang says:

    This solution worked for me today – thanks

  9. Chris says:

    This can be a UAC problem, right clicking Management Studio and Run As Administrator can sometimes work

  10. rob says:

    I get login failed for user domaintest$. Reason etc … Event id 18456 (Every minute). The event shows up in the Live server event viewer not in the Test server event viewer. I believe Test server was cloned from Live. I ran to the two scripts and there
    is no Deny on anything. I’m a SQL novice. Any suggestions.

  11. Smilieface says:

    Came across this today on an Azure VM, and while I could "run as administrator" I wasn’t happy with that.
    The solution which did work for me was to drop the windows group through which the credentials were inherited from SQL, restart SSMS and the re-add the group. This appears to have been related to the SID of the group not matching but the name did.
    Something funky about the way Azure builds VMs I guess.

  12. rob says:

    smilieface: i looked at group accounts on test server and i did see about 5 groups referencing live$. One group had nt servicesqlbrowser with SID info. I searched the web to find out how to add this user to a new group but couldn’t find anything. Since
    it is a test environment, i plan on uninstalling and re-installing.

  13. Mujeeb says:

    Hi All – We had the same issue on a two node test cluster with multiple instances and fixed it by removing database user accounts from (msdb and sysutil_mdw databses) associated with the logins then adding the logins back and granting original permisions
    – for us this issue had manifested itself after applying SQL Server 2008 R2 SP3 and related to the (msdb and sysutil_mdw databses associated with data collector).

  14. NewToIT says:

    I got this error when I was trying to use Copy database whizard to copy from one server to another. Can you please advise on how to fix this problem?

  15. NewToIT says:

    Thanks BigDaddy9z

  16. S550 says:

    check firewall settings.

  17. Basker says:

    Chris’s suggestion worked for me, run as admin, and now it;s fine.

  18. UAC - right click says:

    I was getting same issue/error. I ran SQL studio "As Administrator" and it worked on SQL2012/server2012.

  19. Jeff says:

    My issue was not any DENY permissions, this is a brand new cluster just built.

    I followed Chris’s comment and Ran as Administrator, then logged in and it was OK. My problem of backups failing still exists however, but I am getting closer.

  20. Malamute says:

    Had the same problem for all the regular user logins. The logins were created bij the ERP system. I deleted the logins in SQL management and recreated them there et voila: works like charm.
    Thanks.