November 11, 2022 at 2:38 pm
So if I am on the SSIS server and run the package from there, everything is fine. However the job that runs the package from the main Data Mart server keeps failing due to permissions not being passed. I have gone into the service account, in Active Directory, Delegation tab, and "Trust this user for delegation to any service (Kerberos only)" is selected along with "Use Kerberos only". Also if you run Setspn.exe <domain>\<service acccount> you get the following entries:
MSSQLSvc\<server name>.<domain>:1433
MSSQLSvc\<server name>.<domain>
MSSQLSvc\<server name>:1433
MSSQLSvc\<server name>
When I run the Microsoft Kerberos Configuration Manager from the SSIS server, and putting in the service account, it does not find any issues.
So what is wrong? SQL server for the Main Data Mart is now a 2019 server .. and this worked for the old 2014 that we are replacing.
If I go into SQL Server Configuration Mgr and look at Client Protocals TCP/IP I get Default port of 1433
November 11, 2022 at 4:07 pm
assuming you are using SSIDDB it does not allow kerberos delegation.
SSISDB doesn't support constrained delegation. In a double-hop environment, if the service account of the SQL server that hosts the SSISDB catalog (machine B in our example) is set up for constrained delegation, ISServerExec.exe won't be able to delegate the credentials to the third machine (machine C).
November 14, 2022 at 4:41 pm
Thanks.. I have passed this off to our IT firm (Cap Gemini)
November 15, 2022 at 2:39 pm
So the user account we are using .. in Active Directory IS set for "Trust this user for delegation to any service (Kerberos only).. but the permission is still getting dropped.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply