Hi All, I'm hoping someone can steer me in the proper direction. I have a sql db that uses sql server auth (which I control) and I need to use SSIS to drag from a sql server (i dont control) that only uses windows auth. I can obviously can get the jobs to work in visual studio, but how can I get the sql agent to connect (from sql auth server) and run the job to the windows auth server? I always get login failed for user (not associated with a trusted connection) and "Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER."
I've been searching for a while so any solution is greatly appreciated (i'd like to keep it within the server and SSIS for consistency)
BTW - running a similar job from the server that requires windows auth to the sql auth is not a problem, but i need to run the job from the sql auth server to pull from the windows auth server.
First of all, you need to create a Credential in SQL server. This credential will be the windows account.
using 2005 Goto Security, right click credentials and select NEW CREDENTIAL. Enter the credential name, identity (the windows account) and the password twice. It is better to use a generic windows account, whose sole purpose is to run SSIS packages, it is not advisable to use your own account as if you change your password or your account is removed the package will stop working.
After you have created your credential, expand SQL server agent, expand Proxies and right click on SSIS Package Execution and select New Proxy. Enter a name for the proxy, and select the credential you just created. Enter a description of the account, and select SQL Server Integration Services Package from the subsystems.
Once the proxy had been created, open your job, and then goto the steps of the job and edit the step that runs the package. Change the Run As: Drop down from the SQL Agent Service Account to the proxy account you just created.
Now the package will run as the windows credential, as long as the credential has access to the SQL server you are trying to access.
To confirm the job is running as that user, after the job has run, open the history of the job and expand the last run and select the step that runs the SSIS package, in the details in the Message section is should say "Executed as user: domain\\username".
Your SQL Server Agent needs to run under an account that has the proper rights on the DB server you don't control, or the SSIS package needs to use a SQL Account that has been provisioned for you on the server you don't control if it is running in mixed mode. There may be a way to impersonate an account for a specific SSIS package but I have not explored that option myself.
answered Mar 08, 2011 at 04:11 PM
If you set the Package ProtectionLevel property to either EncryptAllWithPassword or EncryptSensitiveWithPassword, supply a PackagePassword, then re-apply the SQLLogin credentials to your Connection Manager(s), the package will store the user id and password with the package. You will have to re-deploy and alter the SQL Agent job afterwards so it has the password.
Hope this helps, Andy
answered Mar 08, 2011 at 04:19 PM