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

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

• sjb31988

  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

  
  

• Agostino Marottoli

  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

  
  

• Trashey

  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
  
  

• MSJ17

  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

  
  
  

• pfh

  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

  
  
  

• Trisha1802

  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

   

   

  
  
  

• pkv

  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
  
  

• bdkf13

  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

  
  
  

• benny353

  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

  
  

• Isonduil

  Hi Jamie,

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

  Regards,

  Robinson

  
  

• raghu_grdr

  Hi Jamie,

  Thanks for your reply.

  It does answer my question.

  Regards,

  Robinson

  
  

• Teddy79

  Tim,

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

  -Jamie

  
  
  

• Mat1t

  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

  
  
  

• loulou296

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

  -Jamie

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