Tuesday, May 30, 2023

Speaking CV

Bio 

Tony Wilhelm has amassed over two decades of experience working with databases and computers. His expertise in SQL server dates back to 1999, showcasing his extensive knowledge in the field. Throughout his career, Tony has held various roles, including DBA, developer, and manager, in both large and small companies spanning multiple industries. 

 Currently, Tony serves as a senior consultant and technical lead for the Data Management as a Service division at Moser Consulting. In this role, he brings his wealth of experience to the table, providing valuable insights and guidance to drive the implementation of effective solutions.

Speaking Engagements

I'm just starting out on my speaking journey, and am looking forward to adding more to this list in the future. I also have a public Sessionize profile.
  • 2023 - 4 events, 4 presentations
  • 2024 - 4 events, 4 presentations

2024 - 4 events, 4 presentations

  • My Brain Has Blue Screened - Let’s Laugh 
Aug 17, 2024 Data Saturday Columbus 2024
  • My Brain Has Blue Screened - Let’s Laugh 
Aug 2, 2024 SQL Saturday Albany 2024
  • Intro to PowerShell with dbatools 
  • Intro to PowerShell with dbatools

2023 - 4 events, 4 presentations

  • Intro to PowerShell with dbatools
July 15, 2023 – SQL Saturday Columbus 2023
  • Intro to PowerShell with dbatools
  • Intro to PowerShell with dbatools
  • Intro to PowerShell with dbatools





I borrowed this format from Steve Jones, thanks Steve!

Wednesday, December 7, 2022

Login failed for user ''

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

The dreaded error:
login failed for user ''. the user is not associated with a trusted sql server connection.

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.