Possible double hop issue - SSIS Package / SSIS Catalogue

  • Wanted to check if anyone else was able to resolve this and how they did it.

    In a nutshell we have a package stored in SSIS catalogue on Server – S1 which reads files from Server – S2 ( NAS share) and then the  package stored in SSIS catalogue is executed via a C# app from Server – S3. All the servers are in the same domain and the process account executing package on S3 has full access to the  NAS location on S2.  Package is failing with an error message saying it cannot access the share, my hunch is the creds that is initiating the call from C# app is not propagating all the way to the NAS share, basically when it hits the NAS share the login is something like NT\Anonymous.  Looks like enabling delegation at the account level should fix this issue, is that the only solution. Also i think i will need to create SPN, what do i create SPN to? Is it the SSIS service?

  • This is definitely a double hop issue. When you run an SSIS package stored in the SSISDB via SQL (in the case the C# app running the appropriate ones), the credentials of the user that starts the process is used; not that of the SQL Server Service account. As a result you do have 2 steps (or a double hop) when interacting with things like file shares: C# -> SQL Server -> File Share.

    Personally, however, this isn't something I know how to fix, as I leave such things to our Network Administration team. I did, however, want to chime in to effectively say "Yes, you're right, the problem is the double hop." and give a little explanation.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Have you checked your current SQL Server Log to see if SPN (Service Principle Name) was successfully registered the last time the SQL Service was started? This is crucial for Kerberos Authentication. Without it connections fall back to NT Lan Manager which then cause sever double hop issues.

  •  

    SPN's are all good , verified with Kerberos manager. Sounds like i need to enable constrained delegation which i what i am targeting towards

  • Ever get anywhere with this?  I came across https://techcommunity.microsoft.com/t5/sql-server-support/getting-cross-domain-kerberos-and-delegation-working-with-ssis/ba-p/318361 that explains it, but falls short of identifying what is needed for constrained delegation.  TIA.

Viewing 5 posts - 1 through 4 (of 4 total)

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