OLE DB Connection Manager will not save password

I'm stuck with a very annoying problem - any help would be greatly appreciated.

I created a package using Business Intelligence Project. The package reads from a Flat File source and saves to a SQL Server table.

The package has a Data Source for the database connection. In this I have opted to save the password.

When I run the package in VS on my machine it works fine.

I then deployed the package to our SQL 2005 database server using the Deployment Utility. It then appeared in the MSDB section.

I then tested the package ran from my machine using Management Studio and all was fine.

However, if I try to run the package from the database server itself I run into problems. Specifically, the log gives me the following error:
"The AcquireConnection method call to the connection manager "<My Database>" failed with error code 0xC0202009"
....and further down I get:
"Error: 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 'sa'.".

What seems to be happening is the password is not being saved in the connection string. Sure enough, if I look at the Connection Managers section of the Execute Package Utility in Management Studio, then my database connection manager has only a User ID specified and no "pwd=". If I add "pwd=<my password>" to this connection string here then the package works.

How do I get this password to be remembered by the Package?

Incidentally, I have also tried writing a VB.NET program to call the package programmatically. Using this, I have tried to set the connection string of my Data Source in the code to try to get round the problem above. However, even after this I still find the only machine the package runs on is mine, and everywhere else it fails to connect.

Please help! Thanks in advance.

Richard FPlease search the forums. SSIS does not save sensitive information in the packages. You'll need to look at using the /SET command line option to pass in a password.|||Thanks for the swift reply.

Apologies for not checking the forums sufficiently before posting. Anyway I have the answer now.

For anybody else struggling with a similar problem, here is your solution. It involves using XML Package Configurations:
http://vyaskn.tripod.com/sql_server_2005_making_ssis_packages_portable.htm

It has to be said in my defence that the MSDN help on SSIS makes no mention of potential deployment problems with regard to connection strings and passwords.
Nowhere is it made clear that using Package Configurations is the answer AND

the password needs to be manually added to the XML after creating the

config file.

It seems intuitive that the "Save Password" box you tick in when creating the Connection Manager in Visual Studio should do just that, but this is a red herring.
This, along with the fact that DTS didn't have such problems, has clearly led to quite a few people getting caught out.

Thanks anyway.

RichardF|||

fagster wrote:

Thanks for the swift reply.

Apologies for not checking the forums sufficiently before posting. Anyway I have the answer now.

For anybody else struggling with a similar problem, here is your solution. It involves using XML Package Configurations:
http://vyaskn.tripod.com/sql_server_2005_making_ssis_packages_portable.htm

It has to be said in my defence that the MSDN help on SSIS makes no mention of potential deployment problems with regard to connection strings and passwords.
Nowhere is it made clear that using Package Configurations is the answer AND

the password needs to be manually added to the XML after creating the

config file.

It seems intuitive that the "Save Password" box you tick in when creating the Connection Manager in Visual Studio should do just that, but this is a red herring.
This, along with the fact that DTS didn't have such problems, has clearly led to quite a few people getting caught out.

Thanks anyway.

RichardF

Microsoft DOES NOT WANT to be in the business of saving sensitive information. Hence the reason they designed it the way they did.

As soon as SSIS touches that config file, you'll find that the password will get removed -- even if you manually entered it. The work around is to set that file to read only, I guess.

Saving passwords is generally a bad idea anyway, given other, better means such as single sign-on, pass-through authentication, etc...|||

Well they did save such information in DTS, this is my point. Then didn't tell us how to do what was elementary in DTS using SSIS. So anyone who uses SQL Authentication and who wants to deploy a simple package to import say some flat file data to their server is going to run into these problems it seems to me.

So could you possibly point me in the direction of these other better methods that don't involve having a connection string with a password? We don't want to move away form SQL Authentication for our database server though if that's what is required with these methods.

Thanks again for your assistance Phil.

RichardF

|||

fagster wrote:

Well they did save such information in DTS, this is my point. Then didn't tell us how to do what was elementary in DTS using SSIS. So anyone who uses SQL Authentication and who wants to deploy a simple package to import say some flat file data to their server is going to run into these problems it seems to me.

So could you possibly point me in the direction of these other better methods that don't involve having a connection string with a password? We don't want to move away form SQL Authentication for our database server though if that's what is required with these methods.

Thanks again for your assistance Phil.

RichardF

Active Directory would be much better than using isolated user names in SQL Server.