SQL Server Kerberos Constrained Delegation Issue

Chris W 0 Reputation points
2024-11-13T11:19:53.9366667+00:00

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 with constrained delegation. Would like to have shared that but kept getting "file upload failed, please try again"

What am I missing?

Thanks

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,165 questions
Active Directory
Active Directory
A set of directory-based technologies included in Windows Server.
6,739 questions
0 comments No comments
{count} votes

4 answers

Sort by: Most helpful
  1. Olaf Helper 45,371 Reputation points
    2024-11-13T13:07:42.8366667+00:00

    Use the MS SQL Server Kerberos Configuration Manager to validate and may fix the issue.

    Free download at

    https://www.microsoft.com/en-us/download/details.aspx?id=39046


  2. LiHongMSFT-4306 29,276 Reputation points
    2024-11-14T02:31:53.3133333+00:00

    Hi @Chris W

    as I see ANONYMOUS in the file server audit log with constrained delegation

    Seems like your SQL Server instance and the SQL Server Agent service is unable to impersonate the user to access the SMB share.

    Please make sure the constrained delegation is configured correctly. Review this tech doc: How to configure Kerberos Constrained Delegation for Web Enrollment proxy pages.

    Best regards,

    Cosmog


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    0 comments No comments

  3. Chris W 0 Reputation points
    2024-11-19T08:51:08.58+00:00

    I've managed to resolve this by swapping from "Use Kerberos only" to "Use any authentication protocol". I don't really understand why it didn't work though, query to SQL says its using Kerberos, eventlog on file server shows Kerberos too - something for another day

    User's image

    User's image

    0 comments No comments

  4. Chris W 0 Reputation points
    2024-11-19T08:54:08.44+00:00

    Edited as accidental duplicate

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.