复制加密列中的数据（SQL Server Management Studio）

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

基本步骤如下所示：

在发布服务器上创建对称密钥。
使用对称密钥加密列数据。
发布包含加密列的表。
订阅刊物。
初始化订阅。
使用与步骤 1 中相同的 ALGORITHM、KEY_SOURCE 和 IDENTITY_VALUE 值在订阅服务器上重新创建对称密钥。
访问加密的列数据。

注释

应使用对称密钥来加密列数据。对称密钥本身可以通过发布服务器和订阅服务器的不同方式进行保护。

创建和复制加密列数据

在发布服务器（Publisher）上，执行 CREATE SYMMETRIC KEY。

重要

KEY_SOURCE的价值是有价值的数据，可用于重新创建对称密钥和解密数据。 KEY_SOURCE必须始终安全地存储和传输。
执行 OPEN SYMMETRIC KEY 以打开新密钥。
使用 EncryptByKey 函数加密发布服务器上的列数据。
执行 CLOSE SYMMETRIC KEY 以关闭密钥。
发布包含加密列的表。有关详细信息，请参阅 “创建发布”。
订阅刊物。有关详细信息，请参阅 “创建请求订阅 ”或 “创建推送订阅”。
初始化订阅。有关详细信息，请参阅 “创建并应用初始快照”。
在订阅方，执行 CREATE SYMMETRIC KEY，使用与步骤 1 中 ALGORITHM、KEY_SOURCE 和 IDENTITY_VALUE 相同的值。可以为 ENCRYPTION BY 指定不同的值。

重要

KEY_SOURCE的价值是有价值的数据，可用于重新创建对称密钥和解密数据。 KEY_SOURCE必须始终安全地存储和传输。
执行 OPEN SYMMETRIC KEY 以打开新密钥。
使用 DecryptByKey 函数在订阅服务器上解密复制的数据。
执行 CLOSE SYMMETRIC KEY 以关闭密钥。

示例：

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


-- Execute at the Publisher on the publication database.
USE AdventureWorks2012;
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.BusinessEntityID = 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 AdventureWorks2012Replica;
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 复制安全性
 在两台服务器上创建相同的对称密钥

Last updated on 2017-03-07

通过

复制加密列中的数据 （SQL Server Management Studio）

创建和复制加密列数据

示例：

示例：

另请参阅

其他资源

复制加密列中的数据（SQL Server Management Studio）