x

SSIS SQL Server auth vs Windows Auth and agent

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.

more ▼

asked Mar 08, 2011 at 04:02 PM in Default

avatar image

KraXmokr
31 1 1 2

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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".

Good Luck!

more ▼

answered Mar 08, 2011 at 04:23 PM

avatar image

Daniel Ross
2.9k 11 15 18

Oh, WOW...I cant believe it was that easy! here I was thinking proxies were specific to operations on the local server! I never tried against multiple - Thanks a BUNCH! :oD

Mar 08, 2011 at 05:18 PM KraXmokr
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Mar 08, 2011 at 04:11 PM

avatar image

Tim
40.9k 39 92 168

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Mar 08, 2011 at 04:19 PM

avatar image

Andy Leonard
332 2 2

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2030
x1219

asked: Mar 08, 2011 at 04:02 PM

Seen: 5084 times

Last Updated: Mar 08, 2011 at 04:13 PM

Copyright 2017 Redgate Software. Privacy Policy