OLE DB Connection in SSIS Package does not remember password

[urig]

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

[SSIS 2005 Hangs]

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

Post by urig
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
Error at DTSTask_DTSDataPumpTask_1 [OLE DB Destination [73]]: The
AcquireConnection method call to the connection manager ... failed with
error code 0xC0202009.
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

[Matt Yeager]

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

Post by urig
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
Error at DTSTask_DTSDataPumpTask_1 [OLE DB Destination [73]]: The
AcquireConnection method call to the connection manager ... failed with
error code 0xC0202009.
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

[urig]

Hello Matt,

Thank you very much for your reply. I haven't had time to test what you
suggest but it seems like solid advice and when I'll try it I'll post
here to let everyone know how it went.

Regards,
Uri Goldstein,
Israel

[GVAN]

You can work around this by saving the connection information and password in an XML configuration file. This is a better way to to handle it anyways.

[john v]

how exactly do you do that ?

I'm having the same issue with SSIS & passwords


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

[john v]

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


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

[Nuwan K]

Hi John

check this site out...

http://vyaskn.tripod.com/sql_server_2005_making_ssis_packages_portable.h
tm



regards

nu1

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

[murali krishna]

-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 ***

[David Wynn]

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 ***

[Yuri Bulgakov]

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 ***

[j***@googlemail.com]

Post by Yuri Bulgakov
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 Developersdexhttp://www.developersdex.com***

Hi Yuri,

I would recommend using Package Configurations to set your
connections, and also setting the ProtectionLevel of all packages to
"ServerStorage" if you are deploying to MSDB (which I would
recommend). I usually use a configuration database to store
configuration details (along with other useful things like precedence
tables, logs, etc). Each package will have a connection to this
database. This is configured using a package configuration that reads
the connection info from an environment variable on the server (I use
Integrated Security and just make sure that the service account has
access to this db). The connection(s) to actual data sources/
destinations are stored in a configuration table in the configuration
database (encrypted if necessary). As package configurations are
applied in order, as long as the configuration database configuration
(say that 10 times while drunk!) is first on the list the other
configurations should retrieve the correct info from the configuration
table.

Hope this helps!
J

[Ken Ferrell]

I had this same issue with SSIS in 2005. Had nothing to do with the
ProtectionLevel setting.

I had changed the name of the Source and Destination connections to be
more meaningful than the SSIS IMport?Export wizard default names. It
appeared to work as the new connection names did appear in the Source
objects on the data flow task, but I had to open each one and select the
conneciton manager object name again and then select the table and/or re
enter the query. Everything was then fine.

So it appeared the renaming of the connection manager object worked but
it really did not and the connection on the source data flow objects had
to be reaccomplished.





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