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_permissionsandselect * from sys.server_principalsThis 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.
I have this error but I have no idea what you just wrote about it. What do I need to do to fix it?
Same issue. also have no clue on how to fix this
Fix the "CONNECT SQL" permissions. As stated, at least in our case it was 08R2 and the 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. 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.