I used to have a dts that had 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 this when I google the problem. http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/c720e694-2f58-483a-9cd7-3feb7de2db7b but no one seem to have given a good resolution. Can anyone of you help please? Thanks in advance
我曾经有一个有sql server身份验证连接的dts。基本上，用户标识密码存储在包本身中。现在当我去SSIS时，密码没有存储到包中。当我谷歌问题时，我看到了这一点。 http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/c720e694-2f58-483a-9cd7-3feb7de2db7b但似乎没有人给出好的解决方案。你们有人可以帮忙吗？提前致谢
8 个解决方案
#1
24
That answer points to this article: http://support.microsoft.com/kb/918760
该答案指向本文：http：//support.microsoft.com/kb/918760
Here are the proposed solutions - have you evaluated them?
以下是建议的解决方案 - 您对它们进行了评估吗？
- Method 1: Use a SQL Server Agent proxy account
- 方法1：使用SQL Server代理代理帐户
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.
创建SQL Server代理代理帐户。此代理帐户必须使用允许SQL Server代理作为创建程序包的帐户或具有所需权限的帐户运行作业的凭据。
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
此方法用于解密秘密并满足用户的关键要求。但是，此方法可能成功有限，因为SSIS包用户密钥涉及当前用户和当前计算机。因此，如果将程序包移动到另一台计算机，即使作业步骤使用正确的代理帐户，此方法仍可能失败。回到顶端
- Method 2: Set the SSIS Package ProtectionLevel property to ServerStorage
- 方法2：将SSIS包ProtectionLevel属性设置为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
将SSIS Package ProtectionLevel属性更改为ServerStorage。此设置将包存储在SQL Server数据库中，并允许通过SQL Server数据库角色进行访问控制。回到顶端
- Method 3: Set the SSIS Package ProtectionLevel property to EncryptSensitiveWithPassword
- 方法3：将SSIS包ProtectionLevel属性设置为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.
将SSIS Package ProtectionLevel属性更改为EncryptSensitiveWithPassword。此设置使用密码进行加密。然后，您可以修改SQL Server代理作业步骤命令行以包含此密码。
- Method 4: Use SSIS Package configuration files
- 方法4：使用SSIS包配置文件
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.
使用SSIS包配置文件存储敏感信息，然后将这些配置文件存储在安全文件夹中。然后，您可以将ProtectionLevel属性更改为DontSaveSensitive，以便该包未加密，并且不会尝试将密钥保存到包中。运行SSIS包时，将从配置文件中加载所需的信息。如果配置文件包含敏感信息，请确保配置文件得到充分保护。
- Method 5: Create a package template
- 方法5：创建包模板
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.
要获得长期解决方案，请创建一个使用与默认设置不同的保护级别的包模板。在将来的包中不会发生此问题。
#2
14
You can store the password in the configuration string by going to properties and adding password=yourpassword but very important put a space before 'password' word and after ';' as shown below:
您可以通过转到属性并添加password = yourpassword将密码存储在配置字符串中，但非常重要的是在'password'字之前和';'之后加上一个空格如下所示：
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;
数据源= 50.21.65.225;用户ID = vc_ssis;密码= d @ mc317Feo;初始目录=销售;提供商= SQLNCLI10.1;坚持安全信息=真;自动翻译=假;应用名称= SSIS-PKG_CustomerData- {2A666833-6095-4486-C04F-350CBCA5C49E} IDM11.Sales.dev;
Hope this helps --Vijay K
希望这会有所帮助--Vijay K.
#3
8
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.
我使用一个变量来存储整个连接字符串并将其传递给ConnectionString表达式。这会覆盖连接的所有设置，并允许您存储密码。
#4
6
The designed behavior in SSIS is to prevent storing passwords in a package, because it's bad practice/not safe to do so.
SSIS中的设计行为是防止将密码存储在包中，因为这样做不好或不安全。
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.
相反，要么使用Windows身份验证，所以你不要在软件包或配置文件中存储机密，或者，如果你的环境中真的不可能（例如，你没有Windows域），那么你必须使用如下所述的解决方法http://support.microsoft.com/kb/918760(Sam是正确的，只是在那篇文章中进一步阅读）。最简单的答案是配置文件与包一起使用，但是你必须担心配置文件是安全存储的，所以有人不能只读它并获取凭据。
#5
0
Please check the configuration file in the project, set ID and password there, so that you execute the package
请检查项目中的配置文件，在那里设置ID和密码，以便执行包
#6
0
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.
双击SSIS包。然后转到连接管理器，选择DestinationConnectionOLDB，然后在登录字段旁边添加密码。
Example: Data Source=SysproDB1;User ID=test;password=test;Initial Catalog=ASBuiltDW;Provider=SQLNCLI11;Auto Translate=false;
示例：Data Source = SysproDB1; User ID = test; password = test; Initial Catalog = ASBuiltDW; Provider = SQLNCLI11; Auto Translate = false;
Do same for SourceConnectionOLDB.
对SourceConnectionOLDB执行相同操作。
#7
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.
在变量中创建基于表达式的连接字符串并保存密码并使用它。
#8
0
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.
创建连接后，选择连接并打开“属性”。在表达式类别中找到密码。重新输入密码并按Enter键。它现在将保存到连接中。
#1
24
That answer points to this article: http://support.microsoft.com/kb/918760
该答案指向本文：http：//support.microsoft.com/kb/918760
Here are the proposed solutions - have you evaluated them?
以下是建议的解决方案 - 您对它们进行了评估吗？
- Method 1: Use a SQL Server Agent proxy account
- 方法1：使用SQL Server代理代理帐户
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.
创建SQL Server代理代理帐户。此代理帐户必须使用允许SQL Server代理作为创建程序包的帐户或具有所需权限的帐户运行作业的凭据。
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
此方法用于解密秘密并满足用户的关键要求。但是，此方法可能成功有限，因为SSIS包用户密钥涉及当前用户和当前计算机。因此，如果将程序包移动到另一台计算机，即使作业步骤使用正确的代理帐户，此方法仍可能失败。回到顶端
- Method 2: Set the SSIS Package ProtectionLevel property to ServerStorage
- 方法2：将SSIS包ProtectionLevel属性设置为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
将SSIS Package ProtectionLevel属性更改为ServerStorage。此设置将包存储在SQL Server数据库中，并允许通过SQL Server数据库角色进行访问控制。回到顶端
- Method 3: Set the SSIS Package ProtectionLevel property to EncryptSensitiveWithPassword
- 方法3：将SSIS包ProtectionLevel属性设置为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.
将SSIS Package ProtectionLevel属性更改为EncryptSensitiveWithPassword。此设置使用密码进行加密。然后，您可以修改SQL Server代理作业步骤命令行以包含此密码。
- Method 4: Use SSIS Package configuration files
- 方法4：使用SSIS包配置文件
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.
使用SSIS包配置文件存储敏感信息，然后将这些配置文件存储在安全文件夹中。然后，您可以将ProtectionLevel属性更改为DontSaveSensitive，以便该包未加密，并且不会尝试将密钥保存到包中。运行SSIS包时，将从配置文件中加载所需的信息。如果配置文件包含敏感信息，请确保配置文件得到充分保护。
- Method 5: Create a package template
- 方法5：创建包模板
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.
要获得长期解决方案，请创建一个使用与默认设置不同的保护级别的包模板。在将来的包中不会发生此问题。
#2
14
You can store the password in the configuration string by going to properties and adding password=yourpassword but very important put a space before 'password' word and after ';' as shown below:
您可以通过转到属性并添加password = yourpassword将密码存储在配置字符串中，但非常重要的是在'password'字之前和';'之后加上一个空格如下所示：
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;
数据源= 50.21.65.225;用户ID = vc_ssis;密码= d @ mc317Feo;初始目录=销售;提供商= SQLNCLI10.1;坚持安全信息=真;自动翻译=假;应用名称= SSIS-PKG_CustomerData- {2A666833-6095-4486-C04F-350CBCA5C49E} IDM11.Sales.dev;
Hope this helps --Vijay K
希望这会有所帮助--Vijay K.
#3
8
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.
我使用一个变量来存储整个连接字符串并将其传递给ConnectionString表达式。这会覆盖连接的所有设置，并允许您存储密码。
#4
6
The designed behavior in SSIS is to prevent storing passwords in a package, because it's bad practice/not safe to do so.
SSIS中的设计行为是防止将密码存储在包中，因为这样做不好或不安全。
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.
相反，要么使用Windows身份验证，所以你不要在软件包或配置文件中存储机密，或者，如果你的环境中真的不可能（例如，你没有Windows域），那么你必须使用如下所述的解决方法http://support.microsoft.com/kb/918760(Sam是正确的，只是在那篇文章中进一步阅读）。最简单的答案是配置文件与包一起使用，但是你必须担心配置文件是安全存储的，所以有人不能只读它并获取凭据。
#5
0
Please check the configuration file in the project, set ID and password there, so that you execute the package
请检查项目中的配置文件，在那里设置ID和密码，以便执行包
#6
0
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.
双击SSIS包。然后转到连接管理器，选择DestinationConnectionOLDB，然后在登录字段旁边添加密码。
Example: Data Source=SysproDB1;User ID=test;password=test;Initial Catalog=ASBuiltDW;Provider=SQLNCLI11;Auto Translate=false;
示例：Data Source = SysproDB1; User ID = test; password = test; Initial Catalog = ASBuiltDW; Provider = SQLNCLI11; Auto Translate = false;
Do same for SourceConnectionOLDB.
对SourceConnectionOLDB执行相同操作。
#7
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.
在变量中创建基于表达式的连接字符串并保存密码并使用它。
#8
0
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.
创建连接后，选择连接并打开“属性”。在表达式类别中找到密码。重新输入密码并按Enter键。它现在将保存到连接中。