Hi,

I have a simple SSIS Package where I'm trying to move data from one SQL
Server instance to another.

When debugging the package in the SQL Server Business Intelligence
Development Studio, I keep getting the following error:

Error at DTSTask_DTSDataPumpTask_1 [OLE DB Destination [73]]: The
AcquireConnection method call to the connection manager ... failed with
error code 0xC0202009.

This is because:

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

The bottom line is that SSIS keeps forgetting the password I feed into
the two OLE DB Connections that I'm using. I double-click a connection,
type the password in, check "Save my password" and hit "OK" but the
password disappears from there whenever I run the package or
double-click the connection again.

Help! Any ideas?


Thanks!
urig

Did you check the box " remember my passowrd" below where you eneter the
userid and password.
SP

"urig" wrote:

Hi Urig,
I encountered the same problem, even on the same server upon deployment.
I ended up contacting Microsoft and opening a support case. After a couple
of hours on the phone, we found that if the SSIS Package's Security setting
"ProtectionLevel" was set to EncryptAllWithUserKey or
EncryptSensativeWithUserKey that the passwords would actually be lost. This
has to do with the fact that the SQL Server Agent process on your server is
running as a different user and cannot validate the user key basically. What
I ended up having to do is switch the Security ProtectionLevel to use
EncryptAllWithPassword or EncryptSensativeWithPassword and specify a
password for the package. I then re-deployed to SQL.

How I scheduled the Job also had to change. I could no longer specify my
package as a SSIS Step in a Job. I had to make my Job execute an "Operating
System (CmdExec)". The command line was :

C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe /DTS
"\MSDB\YOURPACKAGEHERE" /SERVER Q /DECRYPT YOURPASSWORDHERE /MAXCONCURRENT
" -1 " /CHECKPOINTING OFF /REPORTING V

It seems like very much a work-around, but that's roughly the way I was told
to keep the protected passwords. My support case person spoke with the
engineers and that was the desired result evidently. They are working on
documenting the Security Levels more though, as this seems to be coming up a
lot. I honestly wouldn't be suprised if something in Security levels changed
in SP2.


Hope this helps.

-Matt Yeager




"urig" <uri.goldstein (AT) gmail (DOT) com> wrote

"unknown" wrote:

how exactly do you do that ?

I'm having the same issue with SSIS & passwords


*** Sent via Developersdex http://www.developersdex.com ***

How exactly do you save the connection information in an xml file for
SSIS ?


*** Sent via Developersdex http://www.developersdex.com ***

Hi John

check this site out...

http://vyaskn.tripod.com/sql_server_...ges_portable.h
tm



regards

nu1

*** Sent via Developersdex http://www.developersdex.com ***

-John

After you create the XML configuration file...edit the configuration
file by adding password=urpassword after the User ID =***



*** Sent via Developersdex http://www.developersdex.com ***

Urig, first we set up a service account to handle SQL Agent activities.
Once that was done, we did the following after learning that some of
methods do not work correctly. I have been saving my packages on the
file system of the 2005 SQL Server; scheduling the packages while logged
onto the actual server and set them up to use NT authentication. We have
had no success using any other method.

SQL DBA/ETL Developer from OHIO

*** Sent via Developersdex http://www.developersdex.com ***

What I ended up doing was to use Expression for Connection String where
I would specify connection string that used variables for user name and
password.

Then variables' values are included in configuration of the package, and
you type it in when package is deployed.

I know this might be compromising security, but I neede a way to make
this package run tonight.

HTH.

*** Sent via Developersdex http://www.developersdex.com ***