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

Published Tuesday, February 1, 2011 9:28 AM by simonsabin

Comments

Tuesday, February 1, 2011 9:51 AM by SimonS Blog on SQL Server Stuff

# 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

# Twitter Trackbacks for Solution - Login failed for user x. Reason Token based server access validation failed and error - 18456 - Simons SQL Blog [sqlblogcasts.com] on Topsy.com

Pingback from  Twitter Trackbacks for                 Solution - Login failed for user x. Reason Token based server access validation failed and error - 18456 - Simons SQL Blog         [sqlblogcasts.com]        on Topsy.com

Saturday, August 20, 2011 3:39 PM by Aaron Bertrand

# SQL Server v.Next (Denali) : Troubleshooting Error 18456

I think we've all dealt with error 18456, whether it be an application unable to access SQL Server, credentials