SSIS 连接管理器不存储 SQL 密码 [英] SSIS Connection Manager Not Storing SQL Password

问题描述

我曾经有一个 dts，它有一个 sql server 身份验证连接.基本上，用户 ID 密码存储在包本身中.现在，当我转到 SSIS 时，密码没有存储到包中.我看到了 SSIS 连接管理器密码，当我用谷歌搜索问题时，但似乎没有人给出好的解决方案.

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.

推荐答案

该答案指向这篇文章:http://support.microsoft.com/kb/918760

以下是建议的解决方案 - 您是否对其进行了评估?

Here are the proposed solutions - have you evaluated them?

• 方法 1:使用 SQL Server 代理代理帐户

创建 SQL Server 代理代理帐户.此代理帐户必须使用凭据，使 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.

此方法用于解密机密并满足用户的密钥要求.但是，此方法的成功率可能有限，因为 SSIS 包用户密钥涉及当前用户和当前计算机.因此，如果将包移动到另一台计算机，即使作业步骤使用正确的代理帐户，此方法仍可能会失败.回到顶部

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

• 方法 2:将 SSIS 包保护级别属性设置为 ServerStorage

将 SSIS 包保护级别属性更改为 ServerStorage.此设置将包存储在 SQL Server 数据库中，并允许通过 SQL Server 数据库角色进行访问控制.回到顶部

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

• 方法 3:将 SSIS 包保护级别属性设置为 EncryptSensitiveWithPassword

将 SSIS 包保护级别属性更改为 EncryptSensitiveWithPassword.此设置使用密码进行加密.然后，您可以修改 SQL Server 代理作业步骤命令行以包含此密码.

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.

• 方法 4:使用 SSIS 包配置文件

使用 SSIS 包配置文件存储敏感信息，然后将这些配置文件存储在安全文件夹中.然后，您可以将 ProtectionLevel 属性更改为 DontSaveSensitive，这样包就不会被加密并且不会尝试将机密保存到包中.运行 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.

• 方法 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.

这篇关于SSIS 连接管理器不存储 SQL 密码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！