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
Nana HemaaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
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.
Nana HemaaAuthor Commented:
Yes.  I can access the database on  ServerA with  the sql login and password  from ServerB  ok via management studio.
lcohanDatabase AnalystCommented:
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.
SolarWinds® IP Control Bundle (IPCB)

Combines SolarWinds IP Address Manager and User Device Tracker to help detect IP conflicts, quickly identify affected systems, and help your team take near instantaneous action. Help improve visibility and enhance reliability with SolarWinds IP Control Bundle.

Nana HemaaAuthor Commented:
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.
Nana HemaaAuthor Commented:
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
lcohanDatabase AnalystCommented:
I believe that is due to http://support.microsoft.com/kb/918760
Some of the options are:

Set the SSIS Package ProtectionLevel property to ServerStorage Change the SSIS Package ProtectionLevel property to ServerStorage. This setting stores the package in a SQL Server database and allows access control through SQL Server database roles.
OR
Set the SSIS Package ProtectionLevel property to EncryptSensitiveWithPassword and change the SSIS Package ProtectionLevel property to EncryptSensitiveWithPassword. This setting uses a password for encryption. You can then modify the SQL Server Agent job step command line to include this password.
OR
You could use the SSIS Package configuration files Use SSIS Package configuration files to store sensitive information, and then store these configuration files in a secured folder. You can then change the ProtectionLevel property to DontSaveSensitive so that the package is not encrypted and does not save the password in the package. When you run the SSIS package, the required information is loaded from the configuration file. Make sure that the configuration files are safely protected as they have password in clear text!!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nana HemaaAuthor Commented:
Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.