SSIS Execute Package Fail in SSMS but Agent Job Succeeds
-
Hello,
I created a SSIS package to transfer data from an XML file to a database in SQL Server instance A. It works perfectly in Visual Studio.
Then the SSIS package was deployed to SQL Server instance B.
When I set up a SQL agent job and run as a proxy user "svcSSIS", it works properly. However, if I right click the package and choose Execute, I got an error: Failed to acquire connection "ADOConnectionManager". Connection may not be configured correctly or you may not have the right permissions on this connection.
To my understanding, if I execute directly in SSMS, I am running the package as my account.
The connection manager is used to connect to the destination - SQL Server instance B. Both "svcSSIS" and my user account have permission to read and execute objects. Actually, my account is the sysadmin in both A and B.
How come I cannot acquire connection to B? Any help or information is appreciated.
Thanks in advance!
-
Are you using SSISDB environment variables to override connection configuration?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Thanks for your reply. Yes, the package uses the UAT environment. To ensure my user account can read the settings, I give permission to my account under the Permission tag on both project level and envionment level. It does not help.
Any ideas to make it work?
-
The reason I mentioned it was just to make sure that you'd filled in all the parameter and connection override info on the GUI screen before submitting?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Yes. I mapped all parameters to the corresponding values stored in the UAT environment before execution.
In addition, the connection string used in the execution seems to be correct in the standard execution report.
The connection string looks like: Data Source = [dbserver]; Initial Catalog = [dbName]; Integrated Security = SSPI
My account is a AD domain user.
-
Grant ssis_admin role to the user and give it a try.
You can find additional details about SSIS DB here:
=======================================================================
-
One final idea. Could it be a Kerberos double-hop issue? To test this, try using SQL Server auth for your connection, rather than Windows.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I assigned ssis_admin role to my user account in SSISDB, but it didn't work either. My account is the sysadmin in the instance where the SSIS package executes.
I am afraid the issue is on the instance where the connection manager tries to connect to get data when the package runs. But my account is also the sysadmin in this instance.
-
Thank you, sir.
I believe you're correct - it's a Kerberos double-hop issue.
I checked the sql server logs on instance B and found this line. When I execute the SSIS package, it was actually trying to set the connection with NT AUTHORITY\ANONYMOUS LOGON instead of my account.
Any ideas how to solve this?
- jerryszz wrote:
Thank you, sir.
I believe you're correct - it's a Kerberos double-hop issue.
I checked the sql server logs on instance B and found this line. When I execute the SSIS package, it was actually trying to set the connection with NT AUTHORITY\ANONYMOUS LOGON instead of my account.
Any ideas how to solve this?
Not easily. Do you have a networking guru available to you? If not, use a SQL login.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Just did some research and found it needs some configuration in Windows Server and/or Active Directory. I don't think I have permission to do these stuff. I'll probably have to find a Windows Server Admin to work on that.
A final question: why the package can run through an agent job which runs as a proxy user(also a domain user). My guess is that it only needs one hop(instance A to instance B), is that correct?
- jerryszz wrote:
A final question: why the package can run through an agent job which runs as a proxy user(also a domain user). My guess is that it only needs one hop(instance A to instance B), is that correct?
Yes, that is my understanding. Good luck with the network stuff ... your server admin won't thank you for this task 🙂
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
SSIS catalog DOES NOT allow for double hop - so if you execute on server A (through SSMS on your own PC) and the package needs to run as YOURSELF and connect to SERVER B it won't work unless your package is using SQL Authentication.
see https://learn.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog?view=sql-server-ver16 near the bottom of page
you will also find (the hard way if you try it out) that SSIS catalog procs have a whole bunch of "execute as user" - this also breaks the whole delegation making it even impossible to execute a SP to start the execution of the packages and then having those packages access a remote server through a linked server using windows authentication.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply