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
Line Number: 65536
If you are new to SQL Server then use below to find ERRORLOG
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)