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
I had this situation happen to me today on an SQL Agent service account receiving the error: Error: 18456, Severity: 14, State: 11.
Login failed for user ‘domain\app_service_account’. Reason: Token-based server access validation failed with an infrastructure error.
I found that the password was reset in AD so the SQL Agent was using the wrong password. If we had restarted the SQL Agent service we would have discovered that was the case. But since it was already logged into the domain it wasn’t giving the normal wrong password error. The Token-based error occurred when a SQL Agent job was executed that was accessing another instance where it had permissions to access.
This has become a common issue in our environment since we moved to Cyberark to manage our service account passwords.
hi
i get the error when trying to add a linked server. there are 2 server. server A and B
i am trying to add a linked server on server A for B. it works fine if rdp to server A and use SSMS to add the linked server.
but if i use SSMS running on a thirds PC/server and connect to server A instance then try to add the linked server it fails
Ì get this error when I try to connect with Windows authentication that has no own login at the SQL server. We have added the “builtin\administrators” group but SQL server doesn’t resolve group memberships by default.
I am having this error with no resolution yet. It’s just on one server and this started suddenly when nothing was changed that we are aware of. It started with connections from our dev web server AD accounts to our Dev database. Now our developers AD accounts are having issues connecting.
The accounts can connect to other servers but not to server where many AD accounts are having issues.
Ideas?
The other thing that may cause this is if you are using AD Groups and a user exists in both groups and one of those groups is disabled.
thank you Pinal sir… this helped me
Hi Pinal, thanks so much. I had installed a new instance and applied a security group to it. It turns out I had to run SSMS as an administrator for this new instance.
you can use the PowerShell command to solve the problem in step 4.
set-itemproperty -path HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\policies\system -name EnableLUA -value 0x00000000
The command will disable the UAC Admin Approval Mode policy
For State 11 solution 4 solved the problem for me.
The connection worked from other machines, but locally on the SQL server machine I hade to run using elevation to get it to work.