SSIS Connection Manager Not Storing SQL Password

Question

I used to have a dts that had a sql server authentication connection. Basically, the userid password is stored in the package itself. Now, when I go to SSIS, the password is not getting stored to the package. I saw SSIS Connection Manager passwords when I googled the problem, but no one seems to have given a good resolution.

Yeah I think we need to go for windows auth... thanks all – OpenSource Aug 14 '09 at 18:10 — OpenSource, Aug 14 '09 at 18:10

score 29 · Answer 1 · edited Nov 01 '19 at 16:35

29

You can store the password in the configuration string by going to properties and adding password=yourpassword, but it's very important to put a space after the ; on the line before password and after the ; on the password line, as shown below:

Data Source=50.21.65.225;User ID=vc_ssis; 
password=D@mc317Feo; 
Initial Catalog=Sales;
Provider=SQLNCLI10.1;
Persist Security Info=True;Auto Translate=False;
Application Name=SSIS-PKG_CustomerData-{2A666833-6095-4486-C04F-350CBCA5C49E}IDM11.Sales.dev;

edited Nov 01 '19 at 16:35

Trenton McKinney

56,955
33
144
158

answered Jul 26 '16 at 20:08

VijayK

291
3
2

This answer helped me. – Kalim Oct 12 '16 at 10:18
Helped me as well. May not be the best option for prod, but works for testing – Artur Udod Nov 09 '16 at 14:59
I can't believe something as particular as the extra spaces matters. Then again, I've been in coding long enough where I CAN believe it. Thanks! – hurleystylee Jan 25 '18 at 14:25
I am shocked that this is the answer, but thank you so much!!! – Henry Mar 03 '20 at 19:03

score 27 · Accepted Answer · edited Jun 24 '16 at 16:29

That answer points to this article: http://support.microsoft.com/kb/918760

Here are the proposed solutions - have you evaluated them?

Method 1: Use a SQL Server Agent proxy account

Create a SQL Server Agent proxy account. This proxy account must use a credential that lets SQL Server Agent run the job as the account that created the package or as an account that has the required permissions.

This method works to decrypt secrets and satisfies the key requirements by user. However, this method may have limited success because the SSIS package user keys involve the current user and the current computer. Therefore, if you move the package to another computer, this method may still fail, even if the job step uses the correct proxy account. Back to the top

Method 2: Set the SSIS Package ProtectionLevel property to ServerStorage

Change the SSIS Package ProtectionLevel property to ServerStorage. This setting stores the package in a SQL Server database and allows access control through SQL Server database roles. Back to the top

Method 3: Set the SSIS Package ProtectionLevel property to EncryptSensitiveWithPassword

Change the SSIS Package ProtectionLevel property to EncryptSensitiveWithPassword. This setting uses a password for encryption. You can then modify the SQL Server Agent job step command line to include this password.

Method 4: Use SSIS Package configuration files

Use SSIS Package configuration files to store sensitive information, and then store these configuration files in a secured folder. You can then change the ProtectionLevel property to DontSaveSensitive so that the package is not encrypted and does not try to save secrets to the package. When you run the SSIS package, the required information is loaded from the configuration file. Make sure that the configuration files are adequately protected if they contain sensitive information.

Method 5: Create a package template

For a long-term resolution, create a package template that uses a protection level that differs from the default setting. This problem will not occur in future packages.

I saw that that link - it doesnt deal with this particular issue. My problem is previously in a DTS connection you can store the user id/password right there inside the package but in SSIS apparently we cannot. The link is talking about running a package from a job in sql agent. — OpenSource, Aug 13 '09 at 22:58
Maybe you could set the DTS to use NTLM and let it pass thru from the proxy account. — Sam, Aug 14 '09 at 01:25
Should the password from package configuration update connection manager at design time? How is it even supposed to work? — Stan Bashtavenko, Apr 22 '13 at 17:55
What's the situation with the `Package Deployment Model` and sensitive project-level Parameters? Are these saved? Is there a setting for them? — PeterX, Oct 16 '15 at 04:56

score 9 · Answer 3 · answered Nov 28 '12 at 13:38

9

I use a variable to store the entire connection string and pass it into the ConnectionString expression. This overwrites all settings for the connection and allows you store the password.

answered Nov 28 '12 at 13:38

Dave Sexton

10,768
3
42
56

score 6 · Answer 4 · answered Aug 14 '09 at 00:06

The designed behavior in SSIS is to prevent storing passwords in a package, because it's bad practice/not safe to do so.

Instead, either use Windows auth, so you don't store secrets in packages or config files, or, if that's really impossible in your environment (maybe you have no Windows domain, for example) then you have to use a workaround as described in http://support.microsoft.com/kb/918760 (Sam's correct, just read further in that article). The simplest answer is a config file to go with the package, but then you have to worry that the config file is stored securely so someone can't just read it and take the credentials.

score 0 · Answer 5 · edited Mar 21 '14 at 08:05

0

Please check the configuration file in the project, set ID and password there, so that you execute the package

edited Mar 21 '14 at 08:05

Scoregraphic

7,110
4
42
64

answered Mar 21 '14 at 07:43

Prabish VT

11

score 0 · Answer 6 · answered May 14 '18 at 12:06

0

It happened with me as well and fixed in following way:

Created expression based connection string and saved password in a variable and used it.

answered May 14 '18 at 12:06

Ramanujam Allam

1,300
12
11

score 0 · Answer 7 · answered Jun 21 '19 at 13:50

0

Try storing the connection string along with the password in a variable and assign the variable in the connection string using expression.I also faced the same issue and I solved like dis.

answered Jun 21 '19 at 13:50

Arijit Roy Chowdhury

1

score 0 · Answer 8 · answered Sep 23 '20 at 06:35

0

Check the text contents of the connection manager file itself, the password field might be configured in the Project.params file, in which case entering the password into the connection manager window will cause it to not save.

answered Sep 23 '20 at 06:35

ladiesman217

1

Helps if you mention how to access the connection manager file. – gbeaven Mar 04 '21 at 21:26

score -1 · Answer 9 · answered May 24 '18 at 17:09

Here is a simpler option that works when I encounter this.

After you create the connection, select the connection and open the Properties. In the Expressions category find Password. Re-enter the password and hit Enter. It will now be saved to the connection.

score -2 · Answer 10 · edited Sep 13 '16 at 22:00

-2

There is easy way of doing this. I don't know why people are giving complicated answers.

Double click SSIS package. Then go to connection manager, select DestinationConnectionOLDB and then add password next to login field.

Example: Data Source=SysproDB1;User ID=test;password=test;Initial Catalog=ASBuiltDW;Provider=SQLNCLI11;Auto Translate=false;

Do same for SourceConnectionOLDB.

edited Sep 13 '16 at 22:00

user353gre3

2,747
4
24
27

answered Sep 13 '16 at 21:41

Tarak

1

SSIS Connection Manager Not Storing SQL Password

10 Answers10

Linked