SQL Server Constrained Delegation

• cw255

  Valued Member

  Points: 64

  More actions

  November 12, 2024 at 3:25 pm

  #4480640

  Hi, hoping someone can help. We're in the process of migrating to a new SQL instance and bulk load / insert from SMB share isn't working in SMSS or via SQL agent job

  I'm reasonably confident its Kerberos delegation as I see ANONYMOUS in the file server audit log with constrained delegation and when I enable temporarily enable unconstrained delegation it works fine showing my own domain account in the audit log of the file server

  We're using a managed service account

  PS C:\>setspn -L msa$
  Registered ServicePrincipalNames for CN=MSA,CN=Managed Service Accounts,DC=Domain,DC=com:
  MSSQLSvc/sql02.domain.com:1433
  MSSQLSvc/sql02.domain.com

  Have allowed for constrained delegation

  Set-ADAccountControl -Identity msa$ -TrustedForDelegation $false -TrustedToAuthForDelegation $false
  Set-ADAccountControl -Identity sql02$ -TrustedForDelegation $false -TrustedToAuthForDelegation $false

  Have set SPN's for CIFS on file servers

  PS C:\> setspn -L server04
  Registered ServicePrincipalNames for CN=server04,OU=Servers,DC=domain,DC=com:
  cifs/server04.domain.com
  cifs/server04

  Confirmed delegation is set

   

  PS C:\Get-ADServiceAccount -Identity msa -Properties * | select msds-allowedtodelegateto,hostcomputers
  
  msds-allowedtodelegateto                                                                         hostcomputers
  ------------------------                                                                         -------------
  {cifs/server04.domain.com, cifs/server04} 
  {CN=SQL02,OU=Servers,DC=domain,DC=com}

  I ran sqlcheck from Microsoft and this looks fine, only warning trusted for delegation is false but I believe that is the expected result constrained delegation. Had to paste as an image to keep formatting for readability

  

  What am I missing?

  Thanks