Solution - Login failed for user x. Reason Token based server access validation failed and error - 18456
Had a very bizarre situation yesterday where a local machine account couldn’t access SQL Server and was getting
Login failed for user <user>. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <client ip>]
along with
Error: 18456, Severity: 14, State: 11.
The user was in the logins even after a refresh, it was in the users for the database.
I decided to delete and remove the login and heh presto it worked.
I thought you could validate your logins using SUSER_SNAME and SUSER_SID however they will return information from system catalogs and not from the domain/machine if the user/sid exist in the metadata catalog.
So I came up with the following to verify which accounts aren’t valid, i.e. they have the incorrect SID. This uses the ability to alter the NAME of the login, this is designed for when you have an account that has renamed (maybe someone got married), but doesn’t allow the rename if the SIDs are different. I guess thats because the SIDs are propogated everywhere and changing would not be fun. So if you try and alter a login and the SID is different you get an error. In the script we try to alter the login to itself. If that fails it means the account has been recreated.
Whilst there is a way to get database sids to be reassigned to the correct login sid. I don’t know of a way to do the same for logins beyond dropping and recreating the login.
/* This is script verifies which logins have sids that don’t match the correct
sid defined on the machine/domain.
Simon Sabin (c) 2010
http://sqlblogcasts.com/blogs/simons
*/
declare cLogins cursor
for
select Name
from sys.server_principals
where type_desc LIKE 'WINDOWS%'
open cLogins
declare @login varchar(100)
fetch cLogins into @login
while @@FETCH_STATUS=0
begin
begin try
declare @sql nvarchar(max)
set @sql = 'alter login ' + quotename(@login,'[') + ' with name = ' + quotename(@login,'[')
execute (@sql)
end try
begin catch
if ERROR_NUMBER() = 15098
print @login + ' failed validation with the system SID. This user/group account has been recreated. You will need to drop and recreate the login and associated database user accounts'
else
print @login + ' - ' + error_message() + ' (' + cast(error_number() as varchar(10)) + ')'
end catch
fetch cLogins into @login
end
deallocate cLogins