This is one of the most common error searched on my blog search (http://search.sqlauthority.com) and lately I realized that I have not written any blog about the cause of such error and fixing that. Let us learn about how to fix the error login failed.
If you ever talk to an SQL Expert about login failed for user, he/she might ask for the state of the message or complete error message. All login failed for user message would have error number 18456 but the state of the message in the ERRORLOG would tell the exact cause of login failure. It is important to note that SQL Server does not tell the exact state to the client and it would be a state 1 always.
Login failed for user ‘sa’. (.Net SqlClient Data Provider)
——————————
Server Name: BIGPINAL
Error Number: 18456
Severity: 14
State: 1
Line Number: 65536
If you are new to SQL Server then use below to find ERRORLOG
SQL SERVER – Where is ERRORLOG? Various Ways to Find its Location
Here is the message in ERRORLOG file
Error: 18456, Severity: 14, State: 58.
Login failed for user ‘sa’. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: <local machine>]
As we can see, the message in ERRORLOG file is having state 58 and exact reason.
Coming back to message in the title, here is the complete message
Error: 18456, Severity: 14, State: 11.
Login failed for user ‘domain\user$’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 10.10.10.10]
There are few things to note about this message.
- It would come for windows accounts only.
- If you are seeing $ in the user name, then it is a machine account / computer account. This mostly happens when a “service” running on the remote machine is connecting to SQL.
- If you are not seeing $ in the user name, then it is a user account.
Possible causes and solutions for State 11
- If you are seeing login failure for machine account, then you may want to give permission to machine account. You need to run T-SQL like below (replace domain and machine name)
CREATE LOGIN [<Domain>\<Machine account>$] FROM WINDOWS
- If it’s a windows domain user account, then it needs to have connect permission
GRANT CONNECT SQL TO [DOMAIN\User]
- If few cases, account is part of a group (or many groups) then you need to make sure there is no “DENY” permission inherited via group membership.
SELECT sp.[name],sp.type_desc FROM sys.server_principals sp INNER JOIN sys.server_permissions PERM ON sp.principal_id = PERM.grantee_principal_id WHERE PERM.state_desc = 'DENY'
- If the message only comes with local connectivity and same account works fine remotely then it could be a UAC issue. This means that Elevation is required to connect properly, but you do not see any of the normal prompts to elevate. To use elevation (while launching the SSMS to connect to a locally running instance of SQL) Right click->Select “Run as administrator”.
Depending on the situation, out of four, any option might work. If this didn’t work for you, please comment and let me know. I would love to learn from you too.
Reference: Pinal Dave (https://blog.sqlauthority.com)
33 Comments. Leave new
Hi Pinal.
Yesterday we had a case where we got this error with a Domain User that was given sysadmin rights, was not part of any deny group and running SSMS as Admin also didn’t work. (First time I’ve ever seen it happen)
We had to delete the login and re-add it before it worked.
Just mentioning what worked for us after none of the above 4 steps worked.
As for what caused it – it might have something to do with our AD server that underwent patching and maintenance during the time we first added the login – that might’ve given it a broken token of some sort?
Thank You! it worked for me after dropping and recreating the login.
If I may submit another possible cause and resolution.
If you get this error while you’re connected with remote desktop into the server running management studio, try running management studio as an administrator (right-click, run as administrator).
If that works, the issue is that the only access you have to that server is because you’re a member, directly or via inheritance, of the BUILTINadministrators group, and the only way you can get token-based security using that group is to run as an administrator. If you want to get rid of the error, add another Active Directory group that you’re a member of (that isn’t an administrator) and give that group sysadmin. You could also add your own account, but you should be doing administration via groups.
Hi Pinal, first of all, thank for your amazing web site. It has been such a help to me this past year. I am experiencing this issue on one of our test SQL Servers. Access is supposed to be provided via windows group membership, but when they attempt to login, they are denied access and their individual windows login shows up in the SQL error log exactly like your state 11 error in your blog.
I verified no security principals are denied using your script above.
If I add the individual windows login directly, it works, but this is not a good solution as the windows group which requires access has 71 members, of which 61 are groups.
I tried dropping the windows group and re-adding it. Still doesn’t fix it.
This same windows group works fine with the production server and on the old test server.
Any ideas?
Have you check the status of the windows group?
Someone (or a policy) took away sysadmin from the BuiltInAdministrators group.
could be!
I’m still struggling with this after the following scenario: our wintel team virtualized one of SQL Servers which was acting as a database mirroring SQL Server. When the server came back up, mirroring didn’t work any more as all the logs of the mirrored DBs were corrupt.
When trying to drop/recreate the mirroring, I got the dreaded infrastructre error. Note that sysadmin rights are given on NT-group level and not directly to the technical account which runs the SQL Server services.
Although the account was still member of that NT-group it could no longer connect to either the publisher nor the mirror. On other servers this works without problem and before the virtualization it also worked perfectly.
The only workaround I found was to create the technical user and make it member of sysadmin which is an ugly solution and doesn’t explain where the root cause of the problem is.
Any ideas how to correct this?
Hello,
I am see the same error message in SQL error log, while setting up SQL Mirroring in SQL 2008 . Initially. it was set up by another one, who later moved on. His login was removed from AD as per policy. I ended up reset up again, after mirroring failed to maintain the state. When I set up, mirroring still tries to connect to secondary server using that login and failed to setup. I removed endpoints in both places and, I tried again. SQL still uses to same login to connect secondary server thru end point. I am wondering what could objects that mirroring-set-up uses to set up and, why it still use same login, instead of one who set up at this time ?
Dropping and re-creating the login has worked for me.
I just add another scenario that recreates it. Server “A” is running the SQL agent with a machine account (GMSA). I added that account to a group. Than I added that group as a login on Server “B” (SSIS scenario). Even with the proper setup, I was still getting the “Token base server validation failed message”…
Reason was fairly simple and logical at the end, Since the Agent was already running when I added it to the group, the token was not updated on server A.
Resolution : I simply restarted the Agent services on server A to refresh the security token of my GMSA account, and it worked.
“Run as administrator” worked for me. Thank you!
GRANT CONNECT did the trick for me. Thanks for the post!
Great! Thanks @anon
Running as administrator solves the problem
Thanks for letting me know Aione.
GRANT CONNECT did the trick for me. Thanks for the post!
My issue related to a linked server using incorrect credentials.
Hi,
Came across this awesome site when searching for a resolution to this problem.
A domain group, after giving read, user was getting above error: token based server access validation failed.
The finding of DENY Script helped as it pulled another group that the user was part of and had the DENY permission to connect to SQL, and hence, he was not able to login, I granted connect to another group and it worked.
Thanks Pinal.
Many Thx. [Runas Administrator] did the job.
Hi, what is the user is ‘NT AUTHORITY\ANONYMOUS LOGON’? How can I resolve this?
Great! Thanks for the solution!
We changed our Active Directory configuration and we got the Token-based server validation error as well. The fix as noted above was to delete the login from the server logins and then reinsert it.
Hi, I’m getting this error on my sql server 2008 every minute
Message
Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 2002…….
Error: 18456, Severity: 14, State: 11.
It seems the server itself is trying to connect with the anonymous logon? every minute? it does not looks ok to me.. could you please help