“AcquireConnection method call to the OLEDB Connection Manager for SQL Server failed” Error

Question

I have an SSIS package which takes input from Flat file and transfer the data to SQL Server using OLEDB Destination Data Flow Item. The OLEDB Connection Manager used for the destination is configured to use SQL Server Authentication for the user ‘sa’. The package works fine without enabling the configurations. But when I enable package configuration and save the configuration of the various connection managers in an XML configuration file and then run the package it gives the following error in the validation phase: 

[OLE DB Destination [21]] Error: The AcquireConnection method call to the connection manager "<Connection Manager Name>" failed with error code 0xC0202009.  

And after the validation phase is complete the following error message is given for the package execution: 

[Connection manager "<Connection Manager Name>"] 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'." 

Has anyone else run into this?

I am running

SQL 2005 9.0.1399 and VS 2005 8.0.50727.42 (RTM.50727.4200) on Windows Server 2003 Enterprise Edition SP1. 

Any suggestions would be welcome.

TIA,

Robinson

Answer 1

Have you looked into the config files? If you have you will know that SSIS will not store passwords in there - you have to add them manually yourself.

Could this be the problem?

-Jamie

 

Answer 2

Hi Jamie,

Thanks for your instant reply to the query. The OLEDB Connection Manager works fine when the configuration file is in XML format where in we can insert the value of password in the file. But when we use SQL Server for storing the configuration values the default value for password is  shown something as “*****” which is nothing but indeed *’s and not actual value. When we give in the actual value of password field and save the configuration database, the package validation still fails and gives the same error.

Any suggestions would be welcome.

TIA,

Robinson

Answer 3

Hi Jamie

 

I have the same problem as Robinson had regarding to the AcquireConnection error. I  would like to know would you mind to work me thur the solution. I went into the config file, and scroll down to the passward field which I don't see the value there for the password, it is only showed passward type in string. Please advise.

 

Tim

Answer 4

Tim,

Just add the password into the connection string. This site may help: http://www.connectionstrings.com/

-Jamie

 

 

Answer 5

Hi Jamie,

How do we encrypt the password if added to the ConnectionString.

Regards,

Robinson

Answer 6

Hi,

Came across this post as I was having the same problem -
http://www.developersdex.com/sql/message.asp?p=1921&ID=%3C1146409399.447345.7470%40j73g2000cwa.googlegroups.com%3E

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

However this didn't work for me.  My package was already set to "EncryptSensitiveWithPassword" and still the password won't save.

H

Answer 7

Robinson wrote:

Hi Jamie, How do we encrypt the password if added to the ConnectionString. Regards, Robinson

 

If its in a config file then you can't encrypt it. Is that what you mean?

Encrypted config files don't yet exist in SSIS. A few people have asked for them. It'd be a nice option to have but I don't see it as an important feature. At some point someone somewhere needs to know a password so I don't really see the advantage of encrypted config files. The thing to do is to make sure that no-one has access to the folder where the config files are stored. To do that you cna use AD security which is much more secure and auditable than a username and password.

-Jamie

 

 

Answer 8

Hi Jamie,

 

Thanks for your reply.

But the question I asked was I different context.

 

You replied to Tim's question [on 02-28-2006] saying.

Just add the password into the connection string. This site may help:

http://www.connectionstrings.com/

If we add password to the connection string it would be visible to all in the Connection String and cannot be encrypted.

So my question was that how do we encrypt the password if added to the Connection-String?

 

Regards,

Robinson

Answer 9

Robinson wrote:

Hi Jamie,   Thanks for your reply. But the question I asked was I different context.   You replied to Tim's question [on 02-28-2006] saying. Just add the password into the connection string. This site may help: http://www.connectionstrings.com/ If we add password to the connection string it would be visible to all in the Connection String and cannot be encrypted. So my question was that how do we encrypt the password if added to the Connection-String?   Regards, Robinson

Robinson,

Sorry, I obviously didn't explain myself very well before. You cannot encrypt the passwod that is stored in a config file. Does that answer the question?

-Jamie

 

Answer 10

Hi Jamie,

 

Thanks for your reply.

It does answer my question.

 

 

Regards,

Robinson

Answer 11

I have a similar error to this.  Right now the config file DOES have the password.  However when using Dts.Connections("db").ConnectionString

it seems to omit the password from the config file.  Which throws an exception of "Login failed with User: sa"  Why is this?

Answer 12

tchen777 wrote:

I have a similar error to this.  Right now the config file DOES have the password.  However when using Dts.Connections("db").ConnectionString it seems to omit the password from the config file.  Which throws an exception of "Login failed with User: sa"  Why is this?

 

Referencing the connection string via the Dts object doesn't include the password either. This would expose it to any calling application and is considered a security risk. I guess that is the jsutification anyway.

-Jamie

 

Answer 13

that makes sense.  Then is there any other way to reference that config file?  Right now I just placed the connection string in another property (aka Description) and that seems to allow me to work around it.  But is there a cleaner way to do this?

Answer 14

Not unless you use the .Net classes to parse the config file yourself. You could use XPath to get the value you want.

 

-Jamie

 

Answer 15

Sorry to tag onto an ongoing or old conversation.  But... "the advantage of encrypted config files" is simple.  Storing passwords in an unencrypted string in a file is not smart, and probably violates an untold number of standard security rules.  MS has got to believe in this as well, since they encrypt passwords in other systems...  My SQL Server is secure (just like your AD folder idea), but the passwords are encrypted within for that violation event that 'will' happen.  For example, a server admin could see the contents of the AD controlled folder, but should not be able to read a sensitive password.

Sorry to rant.... ;)

Answer 16

Hi Jamie,

I hv also getting this problem. u hv told "Have you looked into the config files? If you have you will know that SSIS will not store passwords in there - you have to add them manually yourself".

 

But i don't hv any config file. then what should i do...

 

 

 

-Harsh

 

Answer 17

SSIS also won't store passwords in the package unless you have the ProtectionLevel set to one of the "EncryptSensitiveWith.." options. Probably the easiest one to use is the EncryptSensitiveWithPassword.

Answer 18

mdgraves wrote:

Sorry to tag onto an ongoing or old conversation.  But... "the advantage of encrypted config files" is simple.  Storing passwords in an unencrypted string in a file is not smart, and probably violates an untold number of standard security rules.  MS has got to believe in this as well, since they encrypt passwords in other systems...  My SQL Server is secure (just like your AD folder idea), but the passwords are encrypted within for that violation event that 'will' happen.  For example, a server admin could see the contents of the AD controlled folder, but should not be able to read a sensitive password. Sorry to rant....

That's OK, ranting can be constructive

 

I work at an organisation where security is tighter than a drum and they have proper information protection policies. We store passwords in unencrypted files and then secure the folder from prying eyes using Windows auth. Not only are they OK about this - they PREFER it. Windows auth is (I think) regarded as a more secure storage format than username/password.

 

Encyrpted config files would be a great addition -no doubt about that- and I've spoken to people from MSFT who agree. I think its important to point out though that there are other options that work just as well and for that reason, I don't think encrypted config files are going to be a priority for a future version.

 

If you feel strongly that it needs to be a new feature then submit it at https://connect.microsoft.com/SQLServer/feedback/

 

-Jamie

 

Answer 19

Harsh wrote:

I hv also getting this problem.

 

What problem exactly? Sorry - I've lost what this thread was originally about.

 

-Jamie

 

 

Answer 20

I have created a dts on my Sql server. which gets the data from my log file and insert into my sql 2005 table. In dts i am using 'Flat File Source' and 'SQL Server Destination'. When i execute this through 'SQL Server Business Intelligence Development Studio' it works good.

 

My C# code

 

Application app = new Application();

Package package2 = app.LoadFromSqlServer(\\Maintenance Plans\\PackageName, "SqlServer_Name", "UserID", "password", null);

package2.ImportConfigurationFile("d:\\MyPackage.dtsConfig");

//package2.PackagePassword = "password";

 

DTSExecResult result2 = package2.Execute();

string str = result2.ToString();

string strErrorDesc = "";

if (result2 == DTSExecResult.Failure)

{

foreach (DtsError local_DtsError in package2.Errors)

{

strErrorDesc = strErrorDesc + " .... " + local_DtsError.Description;

}

}

 

when i execute this with my c# code, it is showing me following error......

 

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

2. The AcquireConnection method call to the connection manager "EIIA41SQLDB1.Project101.sa1" failed with error code 0xC0202009.

3.  component "SQL Server Destination" (36) failed validation and returned error code 0xC020801C.

4.  One or more component failed validation.

5.  There were errors during task validation.

 

i hv set protection level 'DontSaveSensitive' and setting in dtsconfig file, but it is not working. earlier i hv used EncryptAllWithUserKey, EncryptSensativeWithUserKey, EncryptSensativeWithPassword, EncryptAllWithPassword. but it is not working.

 

please tell me a way step by step to execute this with my c# code. when i execute this through 'SQL Server Business Intelligence Development Studio' it works good.

 

 

please help me to solve this.

thanks

-Harsh

Answer 21

Jamie Thomson wrote:

Have you looked into the config files? If you have you will know that SSIS will not store passwords in there - you have to add them manually yourself. Could this be the problem? -Jamie

 

you can  see my code and problems in just above....

 

i hv gone through with what u hv said but i hv set protection level 'DontSaveSensitive' and put setting in dtsconfig file, but it is not working. earlier i hv used EncryptAllWithUserKey, EncryptSensativeWithUserKey, EncryptSensativeWithPassword, EncryptAllWithPassword. but it is not working.

 

please tell me a way step by step to execute this with my c# code. when i execute this through 'SQL Server Business Intelligence Development Studio' it works good.

 

 

-Harsh

Answer 22

Harsh wrote:

Jamie Thomson wrote: Have you looked into the config files? If you have you will know that SSIS will not store passwords in there - you have to add them manually yourself. Could this be the problem? -Jamie     you can  see my code and problems in just above....   i hv gone through with what u hv said but i hv set protection level 'DontSaveSensitive' and put setting in dtsconfig file, but it is not working. earlier i hv used EncryptAllWithUserKey, EncryptSensativeWithUserKey, EncryptSensativeWithPassword, EncryptAllWithPassword. but it is not working.   please tell me a way step by step to execute this with my c# code. when i execute this through 'SQL Server Business Intelligence Development Studio' it works good.     -Harsh

Harsh,

 

What happens when you try to execute your package by going through SSMS?

 

-Jamie

 

Answer 23

Jamie,

 

I am having a problem using SSIS. All that I am doing is, just bring data from Oracle to SQL2k5. This is a simple package and no fancy or complicated stuff.

 

I deployed the package and calling thro' my VB.NET program. First of all,  I am not getting the data from Oracle. Also, when I did execute this package, it works fine. At times, it throws error as follows:

 

"Error SSIS error code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Server.TestUserId" failed with error code 0xC0202009. There may be error messages......."

 

Why I am getting this error.

(a) Do I need to save the password in the package? If so, where to edit the password and in which file?

(b) Do I need to install the SQL Server 2005 Service Pack 2?\

 

Any help will be highly, highly appreciated.

 

thx,

Amritha

Answer 24

If you use EncryptSensitiveWithPassword, you can save the password in the package itself. But you'll need to provide the password when you open the package.

Answer 25

 

Hi,

 

Thanks for your response.

 

Please correct me if I modify the package and change the settings. This is what I am doing.

 

(1) Open the package.

(2) Create configuration and name it.

(3) Select XML Configuration.

(4) What parameters I should select for the config file. There are quite a lot. All that I am saving is only the server name, username and password.

 

Can you please let me know.

 

Thx,

Amritha

Answer 26

 

I am sorry, I forgot to include this in my previous mail.

 

"you can save the password in the package itself. But you'll need to provide the password when you open the package."

 

Though I save the password in the config file, how can I provide the password when opening the package? just confused here...

 

 

Answer 27

Sorry for confusing the issue, I thought you wanted to store the passwords in the package. If you are using an XML file, you can open it (outside of Visual Studio, in Notepad) and add the password yourself. Visual Studio won't save the password to the config file directly.

 

To use it, when you are setting up the configuration in the wizard, locate the connection manager in the package browser, and check the server, password, etc that you want to store in the configuration.

Answer 28

 

harsh, try not being so dumb and it might work for you

Answer 29

I need to have this confirmed scince it does not make sense to me. SSIS does not automatically save the password in the connectionstring. The solution to that is to maually add the password to the connectionstring in the config file. When the complete connectionstring including the password is given in the config file SSIS still does not include the password when reading the configuration file. Is this correct?

 

Connectionstrings are commonly stored in configuration files to enable moving packages between environments. Is there an easy way to achieve this?

Answer 30

No, if you put the password into the connectionstring in the XML file manually, then SSIS will read it in again each time the package is opened.

 

Answer 31

try this it will help;

select the connection manger which you are working for;

go to the properties

in that you have connection string field

insert your password also there eg., Data Source=........; passwd=rose; Initial.....

then save it and build the package.

definitely your package password would be saved in that dtsconfig file. so  it might not give any error.

 

still its giving error,

 

one good solution, go with windows authentication, instead sqlserver authentication;

 

still you are having problem, i have no idea.

 

Answer 32

 

Jamie - you just saved my day.

 

I had the similar issue where my SSIS packages were throwing errors because of a DB2 connection. I had the config file set up, but noticed no passwords on it. I added the tag 'password' ( as opposed to my first try as 'PWD' which obviously made no difference as it was the wrong syntax) with the supplied password, and everything settled down.

 

All I have to do now is to secure this folder. That is fairly easy in my world.

 

Thanks!

Anil.

Answer 33

Hey Guys,

    Just  to add ... If still you guys get the same error like how I got , try changing the name of connection ... SSIS is having silly bugs built in , which may disaaper in latest versions...

 

Thanks to Ken Ferrell ... who gave the suggestion in his post given below...

http://www.developersdex.com/sql/message.asp?p=1921&r=4984790&page=2