Post reply

SQL Server Constrained Delegation

  • 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

    sqlcheck

    What am I missing?

    Thanks

     

  • November 13, 2024 at 4:10 pm

    #4481214

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply