Reason: Token-based server access validation failed with an infrastructure error. Message: Login failed for user 'DOMAIN\grumpydba'. The only clue is that the SQL log contains the lines: I've only experienced this with sysadmin accounts, but the problem can occur in applications or even Management Studio, where users who should have full sysadmin access find they cannot connect at all. So check the Connect permissions for the login on the server.Something I'd forgotten when I was setting up a new SQL server, was how the UAC (User Account Controls) permissions affect connections to SQL when the windows account in question is part of a group. The requested operation is to connect to the server, and the correct state would be to have the necessary permissions to connect to the server. For those who haven’t clicked the link, the description is “”The group or resource is not in the correct state to perform the requested operation”. The takeaway from this is that while the error does look like it could be related to NTLM/Kerberos/AD troubles, a slight shift in perspective when reading the description of Error Code 0x139F (from the above link) is required. Put the tick in there, clicked OK, everything works. What did I find? Connect to SQL didn’t have a tick in the GRANT column. So going back to the instance of SSMS running under my account, I checked the permissions for the (previously working) login. I tried to open SSMS as the troubled account and connect to the SQL Server myself. I read through it, considered the message, and checked what the author said to check. After a little DuckDuckGo-ing, I came across this reasonably comprehensive page: Troubleshooting specific login failed error messages. Strange – the user exists in other databases. Reason: Token-based server access validation failed with an Until this error appeared when trying to test the data source: The task seemed simple enough – restore a prod database to a dev server, set recovery mode, assign permissions, create data source in SQL Reporting Services, close ticket. TL DR: Check that the user has been granted “connect to SQL Server” before looking for a needle in a haystack.
0 kommentar(er)
