Login failed for user ''

The most recent issue that caused me to lose sleep...

The dreaded error:

Items that were checked / tried:

• Rebooting server and clients
• Dropping and recreating the SPNs

Oh did I mention that this was SQL Server 2005...

What we determined the fix would be, was to enabled each Active Directory user to support AES 256 bit encryption.

 

Unfortunately this will need to be done on ALL users!

Sure, you can manually open up each and every Active Directory user and check that box, but there's an easier way... PowerShell and dbatools to the rescue!

	# import the modules
    Import-Module dbatools
	Import-Module ActiveDirectory
    
	#initialize the variables
    $sqlserver = 'sqlinstance' #put your SQL Serves name here
	$AES256 = 0x10
	$users = @()

    
	# Recursively get the users from the Windows Groups added to SQL Server
    Get-DbaLogin -SqlInstance $sqlserver |
        Where-Object IsDisabled -eq $false |
        Where-Object HasAccess -eq $true |
        Where-Object LoginType -EQ 'WindowsGroup' |
        Where-Object name -NotMatch 'BUILTIN\\' |
        #Select-Object -First 1 |
        ForEach-Object {
            $group = ($PSItem.name -split "\\")[-1]
            $users +=
                Get-ADGroup -Identity $group -ErrorAction Ignore | 
                    Get-ADGroupMember -Recursive |
                    Get-ADUser -Properties "msDS-SupportedEncryptionTypes" |
                    Select-Object SamAccountName, Enabled, "msDS-SupportedEncryptionTypes",                         
                            @{n='AES256'; e={$enc = $_."msDS-SupportedEncryptionTypes"; ($enc -band $AES256) -eq $AES256}}

	# Get the Windows users from the SQL Server
	Get-DbaLogin -SqlInstance $sqlserver  |
		Where-Object IsDisabled -eq $false |
      	Where-Object HasAccess -eq $true |
      	Where-Object LoginType -EQ 'WindowsUser' | 
      	Where-Object name -NotMatch 'NT AUTHORITY' |
      	ForEach-Object {
        	$login = ($PSItem.name -split "\\")[-1]
        	$users += 
            	Get-ADUser -Identity $login -Properties "msDS-SupportedEncryptionTypes" |
                	Select-Object SamAccountName, Enabled, msds*,                     	
                    	@{n='AES256'; e={$enc = $_."msDS-SupportedEncryptionTypes"; ($enc -band $AES256) -eq $AES256}}
    }

	# list the users that are missing the reqiuired encryption type
	$users |     
		Where-Object enabled -EQ $true |
		Where-Object AES256 -EQ $false |
		Sort-Object AES256, SamAccountName -Unique |
		Format-Table -AutoSize 

It's all fine and good to identify the AD Users that need to be updated, but we still need to update them...

	$users |
		Where-Object enabled -EQ $true |
		Where-Object AES256 -EQ $false |
		ForEach-Object {
    		$user = $PSItem
        	Set-ADUser $user.SamAccountName -Replace @{"msDS-SupportedEncryptionTypes"= ($user.'msDS-SupportedEncryptionTypes' -bor $AES256)} -Confirm
		}

After that's been set, then the users just need to reboot their computers to ensure they get the updated user token.