SSIS package as a job fails
Is it possible to create a proxy account to run SSIS package as a job using a SQL Login (Testlogin) created on ServerA on a different domain?
I get "Description: Failed to acquire connection "ADO.Net.linked servername." The password to the connection blanks out when running it as an SSIS package job.
I created a proxy to run this job using the sql login (TESTLogin) on ServerA and I now get this error "Unable to start execution of step 1 (reason: Could not get proxy data for proxy_id = 1).
Any imputs how to run this ssis package as a job. I can execute the package ok when I hard code the sql login password in the connection manager--ssis designer
I get "Description: Failed to acquire connection "ADO.Net.linked servername." The password to the connection blanks out when running it as an SSIS package job.
I created a proxy to run this job using the sql login (TESTLogin) on ServerA and I now get this error "Unable to start execution of step 1 (reason: Could not get proxy data for proxy_id = 1).
Any imputs how to run this ssis package as a job. I can execute the package ok when I hard code the sql login password in the connection manager--ssis designer
lcohan
Can the two SQL Servers "talk" to each other in any way? I'm asking because it looks like it can't connect to "ADO.Net.linked servername" regardles of the login/pw.
ASKER
Yes. I can access the database on ServerA with the sql login and password from ServerB ok via management studio.
Ok that's good however...I don't think that creating a proxy on SQL logins is possible.
"SQL Server Agent proxies use credentials to store information about Windows user accounts."
https://technet.microsoft.com/en-us/library/ms189064(v=sql.105).aspx
So.do you have those credenials in the SSIS connection manager and the pw saved then when you execute the package it fails on that step with that error?
Some workaround option (as tedious as it may look) would be to put that code in a SQL Stored proc on server B and use EXECUTE AS LOGIN = 'PutThatSQLUserNameHere" to do what is needed against that linked server.
Assuming the domains are trusted you could try use a NT login from domainA where ServerA is and crete a Proxy/Credential on the ServerB and use that in the SSIS.
"SQL Server Agent proxies use credentials to store information about Windows user accounts."
https://technet.microsoft.com/en-us/library/ms189064(v=sql.105).aspx
So.do you have those credenials in the SSIS connection manager and the pw saved then when you execute the package it fails on that step with that error?
Some workaround option (as tedious as it may look) would be to put that code in a SQL Stored proc on server B and use EXECUTE AS LOGIN = 'PutThatSQLUserNameHere" to do what is needed against that linked server.
Assuming the domains are trusted you could try use a NT login from domainA where ServerA is and crete a Proxy/Credential on the ServerB and use that in the SSIS.
ASKER
No. The package executes successfully because the password is saved in the SSIS connection manager. However, even though the save password box is checked the package does not save the password.. I have to retype the password each time to execute the package successfully.
ASKER
any idea why the save password does not save the password. I have to input each time to execute the package ok. it is a sql login
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks