We have faced these issues several times with many reasons like Cluster issues, network problems, Domain issues, Login Transfer Issues.
But apart from all these issues there are some uncommon issues as well, one of which i am explain below.
I came across one this issue recently
Issue:
User X is not able to login to SQL Server (application or SSMS).The user was able to login to SQL Server before the issue started occurring from … Date.
User X is not able to login to SQL Server (application or SSMS).The user was able to login to SQL Server before the issue started occurring from … Date.
Logon Error: 18456,Severity: 14, State: 11.
Logon Login failed for user
<Server name>Name’. Reason: Token-based server access validation failed
with an infrastructure error. Check for previous errors. [CLIENT:]
<Server name>Name’. Reason: Token-based server access validation failed
with an infrastructure error. Check for previous errors. [CLIENT:]
Login Failed for User <ServerName>Username’?
Reason:
We have added a User from AD (Windows Integrated) to SQL Server as Login, Later due to some reason we have to drop that user from the AD and created it again. But we did not drop the user from SQL Server.
The SID of the user has changed on the AD whereas SQL Server still has the old SID which invoked the error.
Login Error State 11 means “Valid login but server access failure”, which further points that SQL Server Login is valid but missing certain security privileges which would grant access to the instance.
Resolution
1. Look into the SQL Error log and verify that that the login failed message for the user has a State 11. You can alternatively verify the state number from the SQL Server default traces as well (available from SQL Server 2005 and above).
2. Next look into the Ring Buffers output and find out what was the API that failed.
SELECT CONVERT (varchar(30), GETDATE(), 121) as runtime, dateadd (ms, (a.[Record Time] - sys.ms_ticks), GETDATE()) as [Notification_Time], a.* , sys.ms_ticks AS [Current Time] FROM (SELECT x.value('(//Record/Error/ErrorCode)[1]', 'varchar(30)') AS [ErrorCode], x.value('(//Record/Error/CallingAPIName)[1]', 'varchar(255)') AS [CallingAPIName], x.value('(//Record/Error/APIName)[1]', 'varchar(255)') AS [APIName], x.value('(//Record/Error/SPID)[1]', 'int') AS [SPID], x.value('(//Record/@id)[1]', 'bigint') AS [Record Id], x.value('(//Record/@type)[1]', 'varchar(30)') AS [Type], x.value('(//Record/@time)[1]', 'bigint') AS [Record Time] FROM (SELECT CAST (record as xml) FROM sys.dm_os_ring_buffers WHERE ring_buffer_type = 'RING_BUFFER_SECURITY_ERROR' ) AS R(x)) a CROSS JOIN sys.dm_os_sys_info sys ORDER BY a.[Record Time] ASC
In case of errors , the output will look like as below
Calling API Name: NLShimImpersonate
API Name: ImpersonateSecurityContext
Error Code: 0x139F
(The group or resource is not in the correct state to perform the
requested operation)
4. If the login is directly mapped to the list of available logins in the SQL instance, then check if the SID of the login matches the SID of the3. Check if that login is directly mapped to one of the SQL Server logins by looking into the output of sys.server_principals.
Windows Login.
If the result in setup 2 is as mentioned drop the SQL Server login and create it again.
Try to fix the Orphaned User. Search in BOL Orphaned User
i like it
cool