August 12, 2015 at 7:15 am
Hi All,
We have see login failures for windows accounts. Below is the error message.
Description: In our env most logins are windows accounts.
Initially we thought it is an UAC issue and we tried to launch the SSMS using "Run as Administrator". However, we are seeing login failures.
Enviroment:
Microsoft SQL Server 2014 - 12.0.2402.0 (X64)
RTM Enterprise Edition (HyperVisor)
Error Message in Error Log :
2015-08-10 22:36:45.290 Logon Error: 18456, Severity: 14, State: 11.
2015-08-10 22:36:45.290 Logon Login failed for user 'domain\loginname'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 10.xxx.xxx.xxx]
2015-08-10 22:41:23.470 Logon Error: 18456, Severity: 14, State: 11.
2015-08-10 22:41:23.470 Logon Login failed for user 'domain\loginname'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 10.xxx.xxx.xxx]
Troubleshooting done:
- Recreated the windows login in sql server. Doesn't help.
- ran sp_valdidatelogins. it doesn't return any rows.
- I belong to sysadmin role and when I say, getting below error message.
xp_logininfo 'domain\loginname'
/*
Msg 15404, Level 16, State 19, Procedure xp_logininfo, Line 64
Could not obtain information about Windows NT group/user 'domain\loginname', error code 0x5.
*/
We tried dropping this account and re-creating the windows account with same permissions but still result is same.
It throws same error message. Login failure message !!!
Can anybody help me on how to go about further troubleshooting and fix the issue.
Thanks in advance.
August 12, 2015 at 7:49 am
There are many possibilities to this one:
1. Are you sure user has proper permissions on instance and database?
2. Does user have the "Connect" checked/granted in database permissions?
3. Is user part of a "Deny" group?
There could be a combination of them as well.
August 12, 2015 at 7:56 am
It's nothing to do with the logins themselves or their permissions.
SQL can't contact a domain controller to verify the login token it was passed. Speak with your network/domain admin.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 12, 2015 at 10:18 am
Gail, you are absolutely spot on. But what kind of information do I need to share with the AD admins?
I mean what kind of conversation do I need to have with them. Do I need to tell whether SQL Server service account not able to talk to AD or the login account which is failing is not able to talk to AD?
Is there any steps am I missing here? or is there any dirty test I can do to confirm that it is a AD issue?
August 13, 2015 at 1:58 am
vsamantha35 (8/12/2015)
Gail, you are absolutely spot on. But what kind of information do I need to share with the AD admins?I mean what kind of conversation do I need to have with them. Do I need to tell whether SQL Server service account not able to talk to AD or the login account which is failing is not able to talk to AD?
Is there any steps am I missing here? or is there any dirty test I can do to confirm that it is a AD issue?
In the Windows Event logs, examine the Security log at the time of the error. Provide the AD Admins with the authentication failure details from the Security log.
You can test it for yourself by attempting to log on to the server using the service account credentials.
August 13, 2015 at 2:50 am
vsamantha35 (8/12/2015)
Gail, you are absolutely spot on. But what kind of information do I need to share with the AD admins?I mean what kind of conversation do I need to have with them. Do I need to tell whether SQL Server service account not able to talk to AD or the login account which is failing is not able to talk to AD?
Go sit with them and work through it together. Best way, otherwise you'll be going backwards and forwards on this for days.
or is there any dirty test I can do to confirm that it is a AD issue?
The error messages confirm it's an AD issue.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 13, 2015 at 3:08 am
Thank you all.
August 13, 2015 at 4:39 am
one reason for this could be an invalid DNS server IP, check your network settings and work with your network\domain admins to resolve this
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 13, 2015 at 11:28 am
Thanks Perry. will check.
August 14, 2015 at 2:53 am
Hi,
Also check the first lines in the error log if there are messages related to SPN registration like this one.
--The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x200b, state: 15. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
August 14, 2015 at 4:10 am
JManuelN (8/14/2015)
Hi,Also check the first lines in the error log if there are messages related to SPN registration like this one.
--The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x200b, state: 15. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
That's likely just because the domain account running the services does not have permission to register\deregister SPNs
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 17, 2015 at 1:28 pm
In my environment, the issue that presented with this error message was caused by an AD account being deleted, then recreated with the same name.
For example:
Bob Blob has an AD account MYDOMAIN\Bob.Blob.
Bob Blob leaves the company, his AD account gets deleted.
Some time later, Bob Blob is rehired, and MYDOMAIN\Bob.Blob account is re-created.
MYDOMAIN\Bob.Blob is added to a Windows group that has access to the SQL Server, or is added directly to the SQL server and granted sufficient permissions to connect. However, when Bob Blob tries to connect to the SQL Server, he receives an "invalid login" error and the "token infrastructure" error is logged in the event log.
The only workaround is to create a new account for Bob Blob with a different name (MYDOMAIN\Bobbius.Blobbius), and transition Bob to this new account.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply