对包中敏感数据的访问控制Access Control for Sensitive Data in Packages

为了保护 Integration ServicesIntegration Services 包中的数据,可以设置保护级别,以帮助仅保护包中的敏感数据或包中的所有数据。To protect the data in an Integration ServicesIntegration Services package, you can set a protection level that helps protect just sensitive data or all the data in the package. 另外,可以采用密码或用户密钥对数据加密,或依靠数据库对数据进行加密。Furthermore, you can encrypt this data with a password or a user key, or rely on the database to encrypt the data. 另外,您对包所采用的保护级别不一定是静态的,而是在包的整个生命周期内可能变化。Also, the protection level that you use for a package is not necessarily static, but changes throughout the life cycle of the package. 通常,您可以在包开发阶段设置一个保护级别,在包部署阶段设置另一个保护级别。You often set one protection level during development and another as soon as you deploy the package.

备注

除了本主题中所述的保护级别外,还可以使用固定数据库级角色保护保存到 Integration ServicesIntegration Services 服务器的包。In addition to the protection levels described in this topic, you can use fixed database-level roles to protect packages that are saved to the Integration ServicesIntegration Services server.

定义敏感信息Definition of Sensitive Information

Integration ServicesIntegration Services 包中,下列信息定义为“敏感” 信息:In an Integration ServicesIntegration Services package, the following information is defined as sensitive:

  • 连接字符串的密码部分。The password part of a connection string. 但是,如果选择加密所有数据的选项,则整个连接字符串都将被视为敏感信息。However, if you select an option that encrypts everything, the whole connection string will be considered sensitive.

  • 标记为敏感的任务生成的 XML 节点。The task-generated XML nodes that are tagged as sensitive. XML 节点的标记由 Integration ServicesIntegration Services 控制,用户无法更改。The tagging of XML nodes is controlled by Integration ServicesIntegration Services and cannot by changed by users.

  • 标记为敏感的所有变量。Any variable that is marked as sensitive. 标记的变量由 Integration ServicesIntegration Services控制。The marking of variables is controlled by Integration ServicesIntegration Services.

    Integration ServicesIntegration Services 认为属性是否敏感,主要取决于 Integration ServicesIntegration Services 组件(连接管理器或任务)的开发人员是否将该属性指定为敏感。Whether Integration ServicesIntegration Services considers a property sensitive depends on whether the developer of the Integration ServicesIntegration Services component, such as a connection manager or task, has designated the property as sensitive. 用户不能向被视为敏感的属性列表添加属性,也不能从该列表删除属性。Users cannot add properties to, nor can they remove properties from, the list of properties that are considered sensitive.

加密Encryption

加密(包保护级别所使用的加密)是通过使用 MicrosoftMicrosoft 数据保护 API (DPAPI) 来执行的,DPAPI 是 Cryptography API (CryptoAPI) 的一部分。Encryption, as used by package protection levels, is performed by using the MicrosoftMicrosoft Data Protection API (DPAPI), which is part of the Cryptography API (CryptoAPI).

使用密码加密包的包保护级别还要求您提供密码。The package protection levels that encrypt packages by using passwords require that you provide a password also. 如果将保护级别从不使用密码的级别更改为使用密码的级别,则系统将提示您输入密码。If you change the protection level from a level that does not use a password to one that does, you will be prompted for a password.

另外,对于使用密码的保护级别, Integration ServicesIntegration Services 会使用 Triple DES 加密算法(其密钥长度为 192 位), .NET Framework.NET Framework 类库 (FCL) 中提供该算法。Also, for the protection levels that use a password, Integration ServicesIntegration Services uses the Triple DES cipher algorithm with a key length of 192 bits, available in the .NET Framework.NET Framework Class Library (FCL).

保护级别Protection Levels

下表介绍 Integration ServicesIntegration Services 提供的保护级别。The following table describes the protection levels that Integration ServicesIntegration Services provides. 括号中的值是来自 DTSProtectionLevel 枚举的值。The values in parentheses are values from the DTSProtectionLevel enumeration. SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT)中处理包时,这些值出现在用来配置包属性的“属性”窗口中。These values appear in the Properties window that you use to configure the properties of the package when you work with packages in SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT).

保护级别Protection level DescriptionDescription
不保存敏感数据 (DontSaveSensitive)Do not save sensitive (DontSaveSensitive) 保存包时不保存包中敏感属性的值。Suppresses the values of sensitive properties in the package when the package is saved. 这种保护级别不进行加密,但它防止标记为敏感的属性随包一起保存,因此其他用户将无法使用这些敏感数据。This protection level does not encrypt, but instead it prevents properties that are marked sensitive from being saved with the package and therefore makes the sensitive data unavailable to other users. 如果其他用户打开该包,敏感信息将被替换为空白,用户必须提供这些敏感信息。If a different user opens the package, the sensitive information is replaced with blanks and the user must provide the sensitive information.

当与 dtutil 实用工具 (dtutil.exe) 一起使用时,此保护级别对应的值为 0。When used with the dtutil utility (dtutil.exe), this protection level corresponds to the value of 0.
使用密码加密所有数据 (EncryptAllWithPassword)Encrypt all with password (EncryptAllWithPassword) 使用密码加密整个包。Uses a password to encrypt the whole package. 使用用户在创建包或导出包时提供的密码加密包。The package is encrypted by using a password that the user supplies when the package is created or exported. 用户必须提供包密码,才能在 SSISSSIS 设计器中打开包,或使用 dtexec 命令提示符实用工具运行包。To open the package in SSISSSIS Designer or run the package by using the dtexec command prompt utility, the user must provide the package password. 如果没有密码,用户将无法访问或运行包。Without the password the user cannot access or run the package.

当与 dtutil 实用工具一起使用时,此保护级别对应的值为 3。When used with the dtutil utility, this protection level corresponds to the value of 3.
使用用户密钥加密所有数据 (EncryptAllWithUserKey)Encrypt all with user key (EncryptAllWithUserKey) 使用基于当前用户配置文件的密钥加密整个包。Uses a key that is based on the current user profile to encrypt the whole package. 只有创建或导出了包的用户才能在 SSISSSIS 设计器中打开包,或使用 dtexec 命令提示符实用工具运行包。Only the user who created or exported the package can open the package in SSISSSIS Designer or run the package by using the dtexec command prompt utility.

当与 dtutil 实用工具一起使用时,此保护级别对应的值为 4。When used with the dtutil utility, this protection level corresponds to the value of 4.

注意:对于使用用户密钥的保护级别, Integration ServicesIntegration Services 使用 DPAPI 标准。Note: For protection levels that use a user key, Integration ServicesIntegration Services uses DPAPI standards. 有关 DPAPI 的详细信息,请参阅位于 http://msdn.microsoft.com/library的 MSDN 库。For more information about DPAPI, see the MSDN Library at http://msdn.microsoft.com/library.
使用密码加密敏感数据 (EncryptSensitiveWithPassword)Encrypt sensitive with password (EncryptSensitiveWithPassword) 使用密码只加密包中敏感属性的值。Uses a password to encrypt only the values of sensitive properties in the package. DPAPI 用于此加密。DPAPI is used for this encryption. 敏感数据作为包的一部分保存,但数据是使用当前用户在创建包或导出包时提供的密码加密的。Sensitive data is saved as a part of the package, but that data is encrypted by using a password that the current user supplies when the package is created or exported. 若要在 SSISSSIS 设计器中打开包,用户必须提供包密码。To open the package in SSISSSIS Designer, the user must provide the package password. 如果不提供该密码,则包虽然可以打开但其中不包含敏感数据,当前用户必须为敏感数据提供新值。If the password is not provided, the package opens without the sensitive data and the current user must provide new values for sensitive data. 如果用户试图在不提供密码的情况下执行包,则包执行将会失败。If the user tries to execute the package without providing the password, package execution fails. 有关密码和命令行执行的详细信息,请参阅 dtexec UtilityFor more information about passwords and command line execution, see dtexec Utility.

当与 dtutil 实用工具一起使用时,此保护级别对应的值为 2。When used with the dtutil utility, this protection level corresponds to the value of 2.
使用用户密钥加密敏感数据 (EncryptSensitiveWithUserKey)Encrypt sensitive with user key (EncryptSensitiveWithUserKey) 使用基于当前用户配置文件的密钥只加密包中敏感属性的值。Uses a key that is based on the current user profile to encrypt only the values of sensitive properties in the package. 只有使用同一配置文件的同一个用户才能加载此包。Only the same user who uses the same profile can load the package. 如果其他用户打开该包,敏感信息将被替换为空白,当前用户必须为敏感数据提供新值。If a different user opens the package, the sensitive information is replaced with blanks and the current user must provide new values for the sensitive data. 如果用户试图执行该包,则包执行将会失败。If the user attempts to execute the package, package execution fails. DPAPI 用于此加密。DPAPI is used for this encryption.

当与 dtutil 实用工具一起使用时,此保护级别对应的值为 1。When used with the dtutil utility, this protection level corresponds to the value of 1.

注意:对于使用用户密钥的保护级别, Integration ServicesIntegration Services 使用 DPAPI 标准。Note: For protection levels that use a user key, Integration ServicesIntegration Services uses DPAPI standards. 有关 DPAPI 的详细信息,请参阅位于 http://msdn.microsoft.com/library的 MSDN 库。For more information about DPAPI, see the MSDN Library at http://msdn.microsoft.com/library.
依靠服务器存储进行加密 (ServerStorage)Rely on server storage for encryption (ServerStorage) 使用 SQL ServerSQL Server 数据库角色保护整个包。Protects the whole package using SQL ServerSQL Server database roles. 将包保存到 SQL ServerSQL Server msdb 数据库后,支持此选项。This option is supported when a package is saved to the SQL ServerSQL Server msdb database. 此外,SSISDB 目录使用 ServerStorage 保护级别。In addition, the SSISDB catalog uses the ServerStorage protection level

在将包从 SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT)保存到文件系统时,不支持此选项。This option is not supported when a package is saved to the file system from SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT).

保护级别设置和 SSISDB 目录Protection Level Setting and the SSISDB Catalog

SSISDB 目录使用 ServerStorage 保护级别。The SSISDB catalog uses the ServerStorage protection level. 在向 Integration ServicesIntegration Services 服务器部署 Integration ServicesIntegration Services 项目时,该目录会自动对包数据和敏感值加密。When you deploy an Integration ServicesIntegration Services project to the Integration ServicesIntegration Services server, the catalog automatically encrypts the package data and sensitive values. 该目录还会在检索数据时自动解密数据。The catalog also automatically decrypts the data when you retrieve it.

若将项目(.ispac 文件)从 Integration ServicesIntegration Services 服务器导出到文件系统,该系统会将保护级别自动更改为 EncryptSensitiveWithUserKeyIf you export the project (.ispac file) from the Integration ServicesIntegration Services server to the file system, the system automatically changes the protection level to EncryptSensitiveWithUserKey. 如果使用 中的 “Integration Services 导入项目向导” SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT)导入项目, “属性” 窗口中的 ProtectionLevel 属性将显示值 EncryptSensitiveWithUserKeyIf you import the project by using the Integration Services Import Project Wizard in SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT), the ProtectionLevel property in the Properties window shows a value of EncryptSensitiveWithUserKey.

基于包的生命周期设置保护级别Protection Level Setting Based on Package Life Cycle

SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT) 中初次开发 SQL ServerSQL Server Integration ServicesIntegration Services 包时,可以设置该包的保护级别。You set the protection level of a SQL ServerSQL Server Integration ServicesIntegration Services package when you first develop it in SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT). 以后当部署包时,在 Integration ServicesIntegration Services 中将包导入 SQL Server Management StudioSQL Server Management Studio或从中导出包时,或者在将包从 SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT) 复制到 SQL ServerSQL ServerSSISSSIS 包存储区或文件系统时,都可以更新包的保护级别。Later, when the package is deployed, imported or exported from Integration ServicesIntegration Services in SQL Server Management StudioSQL Server Management Studio, or copied from SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT) to SQL ServerSQL Server, the SSISSSIS Package Store, or the file system, you can update the package protection level. 例如,如果在计算机上使用某个用户密钥保护级别选项创建并保存包,则在将包提供给其他用户时,很可能需要更改保护级别,否则,他们将无法打开该包。For example, if you create and save packages on your computer with one of the user key protection level options, you likely would want to change the protection level when you give the package to other users; otherwise they cannot open the package.

通常,您可以按下面列出的步骤更改保护级别:Typically, you change the protection level as listed in the following steps:

  1. 在部署期间,将包的保护级别保留为默认值 EncryptSensitiveWithUserKeyDuring development, leave the protection level of packages set to the default value, EncryptSensitiveWithUserKey. 此设置可以保证只有开发人员可以看到包中的敏感值。This setting helps ensure that only the developer sees sensitive values in the package. 或者,您可以考虑使用 EncryptAllWithUserKeyDontSaveSensitiveOr, you can consider using EncryptAllWithUserKey, or DontSaveSensitive.

  2. 部署包时,您需要将保护级别更改为不依靠开发人员用户密钥的保护级别。When it is time to deploy the packages, you have to change the protection level to one that does not depend on the developer's user key. 因此,通常需要选择 EncryptSensitiveWithPasswordEncryptAllWithPasswordTherefore you typically have to select EncryptSensitiveWithPassword, or EncryptAllWithPassword. 通过分配一个生产环境中运营团队也知道的临时强密码来加密包。Encrypt the packages by assigning a temporary strong password that is also known to the operations team in the production environment.

  3. 在将包部署到生产环境后,运营团队可以通过分配一个只有他们自己知道的强密码来重新加密部署的包。After the packages have been deployed to the production environment, the operations team can re-encrypt the deployed packages by assigning a strong password that is known only to them. 他们也可以通过选择 EncryptSensitiveWithUserKeyEncryptAllWithUserKey,并使用要运行包的帐户的本地凭据来加密部署的包。Or, they can encrypt the deployed packages by selecting EncryptSensitiveWithUserKey or EncryptAllWithUserKey, and using the local credentials of the account that will run the packages.

设置或更改包的保护级别 Set or Change the Protection Level of Packages

若要控制对包内容以及其中包含的敏感值(如密码)的访问,请设置 ProtectionLevel 属性的值。To control access to the contents of packages and to the sensitive values that they contain, such as passwords, set the value of the ProtectionLevel property. 项目中所含的包需要具有与项目相同的保护级别才能生成项目。The packages contained in a project need to have the same protection level as the project, to build the project. 如果更改项目的 ProtectionLevel 属性设置,需要为包手动更新该属性设置。If you change the ProtectionLevel property setting on the project, you need to manually update the property setting for the packages.

有关 Integration ServicesIntegration Services 中安全功能的概述,请参阅安全性概述 (Integration Services)For an overview of security features in Integration ServicesIntegration Services, see Security Overview (Integration Services).

本主题中的过程介绍如何使用 SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT) 或 dtutil 命令提示实用工具来更改 ProtectionLevel 属性的值。The procedures in this topic describe how to use either SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT) or the dtutil command prompt utility to change the ProtectionLevel property.

备注

除了本主题中的过程外,当导入或导出包时,您通常可以设置或更改包的 ProtectionLevel 属性。In addition to the procedures in this topic, you can typically set or change the ProtectionLevel property of a package when you import or export the package. 当使用 ProtectionLevel 导入和导出向导保存包时,您也可以更改包的 SQL ServerSQL Server 属性。You can also change the ProtectionLevel property of a package when you use the SQL ServerSQL Server Import and Export Wizard to save a package.

在 SQL Server Data Tools 中设置或更改包的保护级别To set or change the protection level of a package in SQL Server Data Tools

  1. 在主题 设置包的保护级别 中查看 ProtectionLevel属性的可用值,然后确定您的包的对应值。Review the available values for the ProtectionLevel property in the topic, Setting the Protection Level of Packages, and determine the appropriate value for your package.

  2. SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT)中,打开包含该包的 Integration ServicesIntegration Services 项目。In SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT), open the Integration ServicesIntegration Services project that contains the package.

  3. SSISSSIS 设计器中打开包。Open the package in the SSISSSIS designer.

  4. 如果“属性”窗口未显示包的属性,请单击设计图面。If the Properties window does not show the properties of the package, click the design surface.

  5. 在“属性”窗口中的 “安全性” 组,为 ProtectionLevel 属性选择相应的值。In the Properties window, in the Security group, select the appropriate value for the ProtectionLevel property.

    如果选择的保护级别需要密码,请输入密码作为 PackagePassword 属性的值。If you select a protection level that requires a password, enter the password as the value of the PackagePassword property.

  6. “文件” 菜单上,选择 “保存选定项” 以保存修改的包。On the File menu, select Save Selected Items to save the modified package.

在命令提示符下设置或更改包的保护级别To set or change the protection level of packages at the command prompt

  1. 查看的可用值ProtectionLevel的部分中,属性设置包保护级别,并确定你的程序包的相应值。Review the available values for the ProtectionLevel property in the section, Setting the Protection Level of Packages, and determine the appropriate value for your package.

  2. 在主题 dtutil Utility 中查看 Encrypt选项的映射,然后确定要用作所选 ProtectionLevel 属性的值的相应整数。Review the mappings for the Encrypt option in the topic, dtutil Utility, and determine the appropriate integer to use as the value of the selected ProtectionLevel property.

  3. 打开命令提示符窗口。Open a Command Prompt window.

  4. 在命令提示符下,导航到您要为其设置 ProtectionLevel 属性的包所在的文件夹。At the command prompt, navigate to the folder that contains the package or packages for which you want to set the ProtectionLevel property.

    下面步骤中所示的语法示例假设此文件夹是当前文件夹。The syntax examples shown in the following step assume that this folder is the current folder.

  5. 使用与下列示例之一相类似的命令,设置或更改包的保护级别。Set or change the protection level of the package or packages by using a command similar to the one of the following examples:

    • 下面的命令将文件系统中的单个包的 ProtectionLevel 属性设置为级别 2“使用密码加密敏感数据”,密码为“strongpassword”:The following command sets the ProtectionLevel property of an individual package in the file system to level 2, "Encrypt sensitive with password", with the password, "strongpassword":

      dtutil.exe /file "C:\Package.dtsx" /encrypt file;"C:\Package.dtsx";2;strongpassword

    • 下面的命令将文件系统中特定文件夹内所有包的 ProtectionLevel 属性设置为级别 2“使用密码加密敏感数据”,密码为“strongpassword”:The following command sets the ProtectionLevel property of all packages in a particular folder in the file system to level 2, "Encrypt sensitive with password", with the password, "strongpassword":

      for %f in (*.dtsx) do dtutil.exe /file %f /encrypt file;%f;2;strongpassword

      如果您在批文件中使用类似的命令,则请输入文件占位符“%f”作为批文件中的“%%f”。If you use a similar command in a batch file, enter the file placeholder, "%f", as "%%f" in the batch file.

包项目保护级别对话框 Package Project Protection Level Dialog Box

可以使用 “包保护级别” 对话框更新包的保护级别。Use the Package Protection Level dialog box to update the protection level of a package. 保护级别决定保护包时所使用的方法、密码或用户密钥以及作用域。The protection level determines the protection method, the password or user key, and the scope of package protection. 可以保护所有数据,也可以只保护敏感数据。Protection can include all data or sensitive data only.

若要了解要求和有关包安全的选项,你可能发现很有用若要查看安全概述 ( Integration Services )To understand the requirements and options for package security, you may find it useful to see Security Overview (Integration Services).

选项Options

Package protection levelPackage protection level
从列表中选择保护级别。Select a protection level from the list.

密码Password
如果使用“使用密码加密敏感数据”或“使用密码加密所有数据”保护级别,请键入密码。If using the Encrypt sensitive data with password or Encrypt all data with password protection level, type a password.

重新键入密码Retype password
再次键入该密码。Type the password again.

包密码对话框 Package Password Dialog Box

可以使用 “包密码” 对话框为使用密码加密的包提供包密码。Use the Package Password dialog box to provide the package password for a package that is encrypted with a password. 如果包使用 “使用密码加密敏感数据”“使用密码加密所有数据” 保护级别,则必须提供密码。You must provide a password if the package uses the Encrypt sensitive with password or Encrypt all with password protection level.

选项Options

密码Password
输入密码。Enter the password.

另请参阅See Also

Integration Services (SSIS) 包 Integration Services (SSIS) Packages
安全概述 ( Integration Services )Security Overview (Integration Services)
dtutil 实用工具dtutil Utility