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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql-server

Examples related to sql-server-2005

Examples related to ssis