暗号化された列のデータをレプリケートする (SQL Server Management Studio)

レプリケーションを使用すると、暗号化された列データを発行できます。サブスクライバーでこのデータを復号化して使用するには、パブリッシャーでのデータの暗号化に使用されたキーもサブスクライバーに存在する必要があります。レプリケーションでは、暗号化キーを転送するためのセキュリティで保護されたメカニズムは提供されません。サブスクライバーで暗号化キーを手動で再作成する必要があります。このトピックでは、パブリッシャーで列を暗号化し、サブスクライバーで暗号化キーを使用できるようにする方法について説明します。

基本的な手順は次のとおりです。

パブリッシャーで対称キーを作成します。
対称キーを使用して列データを暗号化します。
暗号化された列を使用してテーブルを発行します。
出版物を購読します。
サブスクリプションを初期化します。
手順 1 と同じアルゴリズム、KEY_SOURCE、IDENTITY_VALUEの値を使用して、サブスクライバーで対称キーを再作成します。
暗号化された列データにアクセスします。

注

対称キーを使用して列データを暗号化する必要があります。対称キー自体は、パブリッシャーとサブスクライバーで異なる方法で保護できます。

暗号化された列データを作成してレプリケートするには

パブリッシャーで CREATE SYMMETRIC KEY を実行します。

重要

KEY_SOURCEの値は、対称キーの再作成とデータの暗号化解除に使用できる貴重なデータです。 KEY_SOURCEは常に安全に格納および転送する必要があります。
OPEN SYMMETRIC KEY を実行して新しいキーを開きます。
EncryptByKey 関数を使用して、パブリッシャーの列データを暗号化します。
CLOSE SYMMETRIC KEY を実行してキーを閉じます。
暗号化された列を含むテーブルを発行します。詳細については、「パブリケーションの作成」を参照してください。
出版物を購読します。詳細については、「プルサブスクリプションの作成」または「プッシュサブスクリプションの作成」を参照してください。
サブスクリプションを初期化します。詳細については、「初期スナップショットの作成と適用」を参照してください。
サブスクライバーで、ALGORITHM、KEY_SOURCE、IDENTITY_VALUE について手順 1 と同じ値を使用して CREATE SYMMETRIC KEY を実行します。 ENCRYPTION BY には別の値を指定できます。

重要

KEY_SOURCEの値は、対称キーの再作成とデータの暗号化解除に使用できる貴重なデータです。 KEY_SOURCEは常に安全に格納および転送する必要があります。
OPEN SYMMETRIC KEY を実行して新しいキーを開きます。
DecryptByKey 関数を使用して、サブスクライバーでレプリケートされたデータを復号化します。
CLOSE SYMMETRIC KEY を実行してキーを閉じます。

例

この例では、対称キー、対称キーのセキュリティ保護に使用される証明書、およびマスターキーを作成します。これらのキーはパブリケーションデータベースに作成されます。その後、 SalesOrderHeader テーブルに暗号化された列 (EncryptedCreditCardApprovalCode) を作成するために使用されます。この列は、暗号化されていない CreditCardApprovalCode 列ではなく、AdvWorksSalesOrdersMerge パブリケーションで発行されます。可能な場合は、実行時にセキュリティ資格情報の入力をユーザーに求めます。スクリプトファイルに資格情報を格納する必要がある場合は、未承認のアクセスを防ぐためにファイルをセキュリティで保護する必要があります。


-- 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 レプリケーションのセキュリティ
 2 台のサーバーに同一の対称キーを作成する

Last updated on 2017-03-07

次の方法で共有

暗号化された列のデータをレプリケートする (SQL Server Management Studio)

暗号化された列データを作成してレプリケートするには

例

例

こちらもご覧ください

その他のリソース