複寫加密資料行中的資料 (SQL Server Management Studio)

發行項
09/25/2024

複寫讓您可以發行加密的資料行資料。若要在訂閱者端解密及使用此資料，於發行者端用來加密資料的金鑰也必須存在訂閱者端。複寫並不會提供用於傳輸加密金鑰的安全機制。您必須以手動方式於訂閱者端重新建立加密金鑰。本主題示範如何於發行者端加密資料行，並確定訂閱者端可使用加密金鑰。

基本步驟如下：

於發行者端建立對稱金鑰。
使用對稱金鑰加密資料行資料。
發行包含加密資料行的資料表。
訂閱發行集。
初始化訂閱。
使用與步驟 1 相同的 ALGORITHM、KEY_SOURCE 和 IDENTITY_VALUE 值，於訂閱者端重新建立對稱金鑰。
存取加密的資料行資料。

注意

您應該使用對稱金鑰來加密資料行資料。在發行者端和訂閱者端可以用不同方式維護對稱金鑰本身的安全性。

建立和複寫加密的資料行資料

於發行者端，執行 CREATE SYMMETRIC KEY。

重要

KEY_SOURCE 的值是重要的資料，可用於重新建立對稱金鑰和解密資料，所以務必安全地存放和傳輸 KEY_SOURCE。
執行 OPEN SYMMETRIC KEY 開啟新的金鑰。
使用 EncryptByKey 函數於發行者端加密資料行資料。
執行 CLOSE SYMMETRIC KEY 關閉金鑰。
發行包含加密資料行的資料表。如需詳細資訊，請參閱建立發行集。
訂閱發行集。如需詳細資訊，請參閱建立提取訂閱或建立發送訂閱。
初始化訂閱。如需詳細資訊，請參閱建立和套用初始快照集。
在訂閱者端，使用與步驟 1 相同的 ALGORITHM、KEY_SOURCE 和 IDENTITY_VALUE 值來執行 CREATE SYMMETRIC KEY 。您可以針對 ENCRYPTION BY 指定不同的值。

重要

KEY_SOURCE 的值是重要的資料，可用於重新建立對稱金鑰和解密資料，所以務必安全地存放和傳輸 KEY_SOURCE。
執行 OPEN SYMMETRIC KEY 開啟新的金鑰。
使用 DecryptByKey 函數於訂閱者端解密複寫的資料。
執行 CLOSE SYMMETRIC KEY 關閉金鑰。

範例

A. 在發行集資料庫中建立金鑰

此範例會建立對稱金鑰、用來協助維護對稱金鑰安全性的憑證和主要金鑰。這些金鑰建立在複寫資料庫中，然後用來在 SalesOrderHeader 資料表中建立加密資料行 (EncryptedCreditCardApprovalCode)。這個資料行會發行在 AdvWorksSalesOrdersMerge 發行集中，取代未加密的 CreditCardApprovalCode 資料行。可能的話，會在執行階段提示使用者輸入安全性認證。如果您必須將認證儲存在指令碼檔案中，則必須維護這個檔案的安全性，使他人無法在未獲授權的情況下擅自存取。


-- Execute at the Publisher on the publication database.
USE AdventureWorks2022;
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_ReplDataShare'), 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

B. 在訂閱資料庫中建立金鑰

本範例會使用與第一個範例相同的 ALGORITHM、KEY_SOURCE 和 IDENTITY_VALUE 值，在訂閱資料庫中重新建立相同的對稱金鑰。本範例假設您已經初始化訂閱 AdvWorksSalesOrdersMerge 發行集，以複寫加密資料行。可能的話，會在執行階段提示使用者輸入安全性認證。如果您必須將認證儲存在指令碼檔案中，則必須維護這個檔案在儲存和傳輸時的安全性，使他人無法在未獲授權的情況下擅自存取。


-- Execute at the Subscription on the subscription database.
USE AdventureWorks2022Replica;
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_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_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 Management Studio)

建立和複寫加密的資料行資料

範例

A. 在發行集資料庫中建立金鑰

B. 在訂閱資料庫中建立金鑰

意見反應

其他資源

共用方式為

複寫加密資料行中的資料 (SQL Server Management Studio)

建立和複寫加密的資料行資料

範例

A. 在發行集資料庫中建立金鑰

B. 在訂閱資料庫中建立金鑰

相關內容

意見反應

其他資源