Recently we had an issue with one of our customers who was not able to login into her SQL Server. In SQL’s errorlog we found this message:
At first we checked the permissions, they seemed to be ok.
So we went back to the original message in the log. To be honest, we didn’t read the message well enough, and were a little bit confused about the first part of the message: “Token-based server access validation failed with an infrastructure error.” Since the database permissions were fine, we thought there had to be an issue with the AD account itself, since we were able to connect without any issue.
Unfortunately, that was not the case either. So we had to dig a little deeper. Now we took the time to read the second part the error message in the error log (“Login lacks Connect SQL permission”), which is, to be totally honest quite clear.
It turned out our customer was member of a domain group called Marketing. We used this query to check any instance level securables on the group:
|
1 2 3 4 5 6 7 8 9 |
USE master go SELECT P.name , SP.permission_name , SP.state_desc FROM sys.server_principals AS P INNER JOIN sys.server_permissions AS SP ON P.principal_id = SP.grantee_principal_id WHERE SP.state_desc = N'DENY'; |
And look, there it was… The AD group our customer is part of, has no permission to connect to the SQL Server.
Conclusion
Remember, DENY overrules all other permissions. The customer could not connect because of one of her groups was denied access, even though her permissions were just fine. So we granted the correct permissions to the group, removed the customer’s individual login and after that, she was able to connect again.
|
1 |
GRANT CONNECT SQL TO [BATENICT\Marketing] AS [sa]; |