[sql-server] SSIS Connection Manager Not Storing SQL Password

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.

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.

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.

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.

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;

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

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.

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.

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.

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.