Re: How to store SQL UserName and Password into configruation Table?

Question

Hi All,

I store SQL Server's connection string/Username/Password into configuration table. My package's ProtectionLevel Property is EncryptSensitiveWithPassword. When I enable package configuration, Package can't connect to the database server. I have seen in my configuration table password stored as ********. I have changed it to my original password but can't connect to the database server.

Is anybody know what's i am doing wrong?

Regards,

Balwant.

Answer 1

Change the package protectionlevel to DontSaveSensitive. Then put the password in the config table. it should work.

---

Hope this helps !!
Sudeep   |    My Blog

Answer 2

Hi Sudeep,

Thank you very much for you quick response.

It doesn't work.

I have stored connection string,username and password in my config table and changed ProtectionLevel to DontSaveSensitive.

here is my connectionstring:

Data Source=ServerName;User ID=UserName;Initial Catalog=DLGCartographer;Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False;


Following is the error on package.
Error 1 Validation error. Validation_QA: Validate_M_Reponses [1472]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Genesis.DLGCartographer" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed. ETLStandardAuto_ValidateQA_4.dtsx 0 0 


Regards,

Balwant.

Answer 3

Data Source=ServerName;User ID=UserName;password = password;Initial Catalog=DLGCartographer;Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False;

Use the above string which includes password. By default config wont store password information.

---

Raju

Answer 4

Data Source=ServerName;User ID=UserName;Password=Password; Initial Catalog=DLGCartographer;Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False;

You need to add the password part to the connection string manually.
Also if you have configured the connection string there is no need to set up the user name and password in different configuration.

---

Hope this helps !!
Sudeep   |    My Blog

Answer 5

Thank you very much Sudeep & Raj for you kind support.