Connection issues

Question

Connection issues

DKlein

SSCrazy

Points: 2351
More actions
February 19, 2010 at 9:03 am

#145301

Hello all,
I am trying to put together a package that reads data from two different servers and writes to a third, the third server is also, where the package resides. We are having problems with the execution of the package retaining the connection info during execution of a sql job. The connections work when we execute using the IDE but when called by a sql job the read connections to external servers fail. We have the package deployed to Sql Server/msdb.
Thanks,
DK

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 245813 More actions · Answer 1

Does the SQL Agent service user have the requisite privileges to access the other servers?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

COZYROC One Orange Chip Points: 28499 More actions · Answer 2

DKlein (2/19/2010)
Hello all,
I am trying to put together a package that reads data from two different servers and writes to a third, the third server is also, where the package resides. We are having problems with the execution of the package retaining the connection info during execution of a sql job. The connections work when we execute using the IDE but when called by a sql job the read connections to external servers fail. We have the package deployed to Sql Server/msdb.
Thanks,
DK

Where the package is deployed doesn't affect the credentials. What matters is the user account used by the SQL Job Agent to execute the package. You have to make sure you use user account, with the required credentials. You have to either include additional permissions to your default SQL Job Agent account or you have to setup proxy account and setup to use the same account you use when running under the IDE.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

DKlein SSCrazy Points: 2351 More actions · Answer 3

Thanks for the responses, the package is housed in our Dev-environment and one of the data sources is from a production box, so I am not permitted to grant the Sql Agent windows permissions from Dev to Prod. The angle I was using, right or wrong, was to try to use sql authentication in the package. The sql login/user being used in the package is present on both servers, and uses the same password.

“[Connection manager "Web Data PROD"] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'reader'.".”

Am I going about this the wrong way?

Thanks,

DK

COZYROC One Orange Chip Points: 28499 More actions · Answer 4

DKlein (2/19/2010)
Thanks for the responses, the package is housed in our Dev-environment and one of the data sources is from a production box, so I am not permitted to grant the Sql Agent windows permissions from Dev to Prod. The angle I was using, right or wrong, was to try to use sql authentication in the package. The sql login/user being used in the package is present on both servers, and uses the same password.
“[Connection manager "Web Data PROD"] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'reader'.".”
Am I going about this the wrong way?
Thanks,
DK

What package protection level do you use?

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

DKlein SSCrazy Points: 2351 More actions · Answer 5

DKlein

SSCrazy

Points: 2351

February 19, 2010 at 12:37 pm

#1122065

EncryptSensitiveWithUserKey

COZYROC One Orange Chip Points: 28499 More actions · Answer 6

DKlein (2/19/2010)
EncryptSensitiveWithUserKey

This setting will not work unless you use the exact same user account you have used to create the package. I would recommend you setup to use EncryptAllSensitiveWithPassword. Then in your SQL Job Agent you have to change the job step to OS CmdExec step type. You have to modify the command line by including DTEXEC in front and you have to include the package decryption password by using : /DE <password>

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

DKlein SSCrazy Points: 2351 More actions · Answer 7

CozyRoc, all,

I apologize for the delay, and thanks for the help, I ended up using variables and building the connections strings in the package using expressions.

Thanks again,

DK