通过

如何复制加密列中的数据 (SQL Server Management Studio)

  • 项目

新建日期： 2006 年 12 月 12 日

通过复制，您可以发布加密的列数据。若要在订阅服务器上解密并使用此数据，则订阅服务器上也必须有用于在发布服务器上加密该数据的密钥。复制并不提供安全机制来传输加密密钥。您必须在订阅服务器上手动重新创建加密密钥。本主题说明如何在发布服务器上加密列并确保订阅服务器上提供有加密密钥。

基本步骤如下所述：

  1. 在发布服务器上创建对称密钥。
  2. 使用对称密钥加密列数据。
  3. 发布包含加密列的表。
  4. 订阅发布。
  5. 初始化订阅。
  6. 使用与步骤 1 中相同的 ALGORITHM、KEY_SOURCE 和 IDENTITY_VALUE 值在订阅服务器上重新创建对称密钥。
  7. 访问加密列数据。
Bb326115.note(zh-cn,SQL.90).gif注意：
您应当使用对称密钥来加密列数据。可以在发布服务器和订阅服务器上采取不同方式为对称密钥本身进行保护。

创建和复制加密列数据

  1. 在发布服务器上，执行 CREATE SYMMETRIC KEY。

    Bb326115.security(zh-cn,SQL.90).gif安全说明：
    KEY_SOURCE 值是很重要的数据，可用来重新创建对称密钥和解密数据。必须始终安全地存储和传输 KEY_SOURCE。

  2. 执行 OPEN SYMMETRIC KEY 以打开新密钥。

  3. 使用 EncryptByKey 函数在发布服务器上加密列数据。

  4. 执行 CLOSE SYMMETRIC KEY 以关闭密钥。

  5. 发布包含加密列的表。有关详细信息，请参阅如何创建发布和定义项目 (SQL Server Management Studio)。

  6. 订阅发布。有关详细信息，请参阅如何创建请求订阅 (SQL Server Management Studio) 或如何创建推送订阅 (SQL Server Management Studio)。

  7. 初始化订阅。有关详细信息，请参阅如何创建和应用初始快照 (SQL Server Management Studio)。

  8. 在订阅服务器上，使用与步骤 1 中相同的 ALGORITHM、KEY_SOURCE 和 IDENTITY_VALUE 值执行 CREATE SYMMETRIC KEY。您可以为 ENCRYPTION BY 指定不同的值。

    Bb326115.security(zh-cn,SQL.90).gif安全说明：
    KEY_SOURCE 值是很重要的数据，可用来重新创建对称密钥和解密数据。必须始终安全地存储和传输 KEY_SOURCE。

  9. 执行 OPEN SYMMETRIC KEY 以打开新密钥。

  10. 使用 DecryptByKey 函数在订阅服务器上解密复制的数据。

  11. 执行 CLOSE SYMMETRIC KEY 以关闭密钥。

示例

此示例将创建一个对称密钥、一个用来帮助保护对称密钥的证书和一个主密钥。这些密钥在发布数据库中创建，然后可用于在 SalesOrderHeader 表中创建加密列 (EncryptedCreditCardApprovalCode)。将在 AdvWorksSalesOrdersMerge 发布中发布此列而不是未加密的 CreditCardApprovalCode 列。如果可能，请在运行时提示用户输入安全凭据。如果将凭据存储在脚本文件中，则必须确保文件的安全性以防止未经授权的访问。

-- Execute at the Publisher on the publication database.
USE AdventureWorks;
GO

-- Create the database master key if it doesn't exist.
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys 
    WHERE [name] LIKE '%DatabaseMasterKey%')
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pub$p@55w0Rd';

-- Create the cert_keyProtection certificate if it doesn't exist.
IF NOT EXISTS (SELECT * FROM sys.certificates 
    WHERE [name] = 'cert_keyPublisher')
CREATE CERTIFICATE [cert_keyPublisher] 
    WITH SUBJECT = 'Publisher Key Protection';

-- Create the key_ReplDataShare symmetric key if it doesn't exist.
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys 
    WHERE [name] = 'key_ReplDataShare')
CREATE SYMMETRIC KEY [key_ReplDataShare] WITH
    KEY_SOURCE = 'My key generation bits. This is a shared secret!',
    ALGORITHM = AES_256, 
    IDENTITY_VALUE = 'Key Identity generation bits. Also a shared secret'
    ENCRYPTION BY CERTIFICATE [cert_keyPublisher];
GO 

-- Open the encryption key.
OPEN SYMMETRIC KEY [key_ReplDataShare]
    DECRYPTION BY CERTIFICATE [cert_keyPublisher];
GO

-- Create a new CreditCardApprovalCode column in the SalesOrderHeader table.
ALTER TABLE Sales.SalesOrderHeader 
    ADD EncryptedCreditCardApprovalCode VARBINARY(256) NULL;
GO

-- Insert encrypted data from the CreditCardApprovalCode column.
UPDATE Sales.SalesOrderHeader
SET EncryptedCreditCardApprovalCode
    = EncryptByKey(Key_GUID('key_DataShare'), CreditCardApprovalCode);
GO

CLOSE SYMMETRIC KEY [key_ReplDataShare];
GO

DECLARE @publication AS sysname;
DECLARE @table1 AS sysname;
DECLARE @table2 AS sysname;
DECLARE @table3 AS sysname;
DECLARE @salesschema AS sysname;
DECLARE @hrschema AS sysname;
DECLARE @filterclause AS nvarchar(1000);
SET @publication = N'AdvWorksSalesOrdersMerge'; 
SET @table1 = N'Employee'; 
SET @table2 = N'SalesOrderHeader'; 
SET @table3 = N'SalesOrderDetail'; 
SET @salesschema = N'Sales';
SET @hrschema = N'HumanResources';
SET @filterclause = N'Employee.LoginID = HOST_NAME()';

-- Add a filtered article for the Employee table.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table1, 
  @source_object = @table1, 
  @type = N'table', 
  @source_owner = @hrschema,
  @schema_option = 0x0004CF1,
  @description = N'article for the Employee table',
  @subset_filterclause = @filterclause;

-- Add an article for the SalesOrderHeader table that is filtered
-- based on Employee and horizontally filtered.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table2, 
  @source_object = @table2, 
  @type = N'table', 
  @source_owner = @salesschema, 
  @vertical_partition = N'true',
  @schema_option = 0x0034EF1,
  @description = N'article for the SalesOrderDetail table';

-- Add an article for the SalesOrderDetail table that is filtered
-- based on SaledOrderHeader.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table3, 
  @source_object = @table3, 
  @source_owner = @salesschema,
  @description = 'article for the SalesOrderHeader table', 
  @identityrangemanagementoption = N'auto', 
  @pub_identity_range = 100000, 
  @identity_range = 100, 
  @threshold = 80,
  @schema_option = 0x0004EF1;

-- Add all columns to the SalesOrderHeader article.
EXEC sp_mergearticlecolumn 
  @publication = @publication, 
  @article = @table2, 
  @force_invalidate_snapshot = 1, 
  @force_reinit_subscription = 1;

-- Remove the credit card Approval Code column.
EXEC sp_mergearticlecolumn 
  @publication = @publication, 
  @article = @table2, 
  @column = N'CreditCardApprovalCode', 
  @operation = N'drop', 
  @force_invalidate_snapshot = 1, 
  @force_reinit_subscription = 1;

-- Add a merge join filter between Employee and SalesOrderHeader.
EXEC sp_addmergefilter 
  @publication = @publication, 
  @article = @table2, 
  @filtername = N'SalesOrderHeader_Employee', 
  @join_articlename = @table1, 
  @join_filterclause = N'Employee.EmployeeID = SalesOrderHeader.SalesPersonID', 
  @join_unique_key = 1, 
  @filter_type = 1, 
  @force_invalidate_snapshot = 1, 
  @force_reinit_subscription = 1;

-- Add a merge join filter between SalesOrderHeader and SalesOrderDetail.
EXEC sp_addmergefilter 
  @publication = @publication, 
  @article = @table3, 
  @filtername = N'SalesOrderDetail_SalesOrderHeader', 
  @join_articlename = @table2, 
  @join_filterclause = N'SalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID', 
  @join_unique_key = 1, 
  @filter_type = 1, 
  @force_invalidate_snapshot = 1, 
  @force_reinit_subscription = 1;
GO

此示例使用与第一个示例相同的 ALGORITHM、KEY_SOURCE 和 IDENTITY_VALUE 值在订阅数据库中重新创建相同的对称密钥。此示例假设您已初始化 AdvWorksSalesOrdersMerge 发布的订阅以复制加密列。如果可能，请在运行时提示用户输入安全凭据。如果必须将凭据存储在脚本文件中，则必须在存储和传输过程中保护文件以防止未经授权的访问。

-- Execute at the Subscription on the subscription database.
USE AdventureWorksReplica;
GO

-- Create the database master key if it doesn't exist.
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys 
    WHERE [name] LIKE '%DatabaseMasterKey%')
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Sub$p@55w0Rd';

-- Create the cert_keySubscriber certificate if it doesn't exist.
-- This can be a different certificate than at the Publisher.
IF NOT EXISTS (SELECT * FROM sys.certificates 
    WHERE [name] = 'cert_keySubscriber')
CREATE CERTIFICATE [cert_keySubscriber] 
    WITH SUBJECT = 'Subscriber Key Protection';

-- Create the key_DataShare symmetric key if it doesn't exist.
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys 
    WHERE [name] = 'key_ReplDataShare')
CREATE SYMMETRIC KEY [key_ReplDataShare] WITH
    KEY_SOURCE = 'My key generation bits. This is a shared secret!',
    ALGORITHM = AES_256, 
    IDENTITY_VALUE = 'Key Identity generation bits. Also a shared secret'
    ENCRYPTION BY CERTIFICATE [cert_keySubscriber];
GO 

-- Open the encryption key.
OPEN SYMMETRIC KEY [key_ReplDataShare]
    DECRYPTION BY CERTIFICATE [cert_keySubscriber];
GO

-- Return the column that was encrypted at the Publisher and also decrypt it.
SELECT SalesOrderID AS 'Order Number', EncryptedCreditCardApprovalCode AS 'Encrypted Approval Code', 
    CONVERT(VARCHAR(15), DecryptByKey(EncryptedCreditCardApprovalCode)) AS 'Decrypted Approval Code'
FROM Sales.SalesOrderHeader;
GO

CLOSE SYMMETRIC KEY [key_ReplDataShare];
GO

请参阅

概念

复制安全性元素

其他资源

如何在两个服务器上创建相同的对称密钥

帮助和信息

获取 SQL Server 2005 帮助