December 19, 2008 at 8:25 am
I have a job that pulls data from an AS400 and imports into SQL2005. The AS400 connection is configured to save the login and password. The package executes fine from Visual Studio, or from Integration Services. When the package is sceduled as part of a SQL job, the package fails on authentication to the AS400. The job had been running fine up until this week, when I made a change to the package and redeployed it. The SQL Agent service is running as LocalSystem. The ProtectionLevel of the package is set to "EncryptSensitiveWithUserKey" within the package designer, but it is set to "ServerStorage" when I deploy it to the server (stored in MSDB). Thanks in advance for any help!
December 19, 2008 at 9:39 am
How is "The AS400 connection is configured to save the login and password"?
If you mean the checkbox in the ODBC dialog is checked, you are mistaken. Even the MS documentation indicates that this checkbox is useless and as part of their attempt to secure security information, ODBC passwords do not get saved. You have to use package configurations or set the password in the SQL Agent dialog.
December 19, 2008 at 10:33 am
In the SSIS package, the AS400 connection is set up as an OLE DB data source, using the "IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider" driver. In the Connection Manager detail for this connection, there is a check box for "Allow saving password" and that is what I have checked.
December 19, 2008 at 1:26 pm
December 22, 2008 at 4:21 am
That checkbox will not work either. MS documented it in books online. Use package configurations or dynamically set it with a variable in the package.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply