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

[アーティクル]
12/15/2008

新規 : 2006 年 12 月 12 日

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

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

パブリッシャで対称キーを作成します。
その対称キーを使用して列データを暗号化します。
暗号化された列を含むテーブルをパブリッシュします。
パブリケーションにサブスクライブします。
サブスクリプションを初期化します。
手順 1 と同じ値を ALGORITHM、KEY_SOURCE、IDENTITY_VALUE に使用して、サブスクライバで対称キーを再作成します。
暗号化された列データにアクセスします。

メモ :
列データを暗号化するには、対称キーを使用する必要があります。対称キー自体は、パブリッシャとサブスクライバで、それぞれ異なる手段で保護できます。

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

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

セキュリティメモ :
KEY_SOURCE の値は、対象キーの再作成およびデータの暗号化解除に使用できる重要なデータです。KEY_SOURCE は、常に安全に格納および送信する必要があります。

OPEN SYMMETRIC KEY を実行して新しいキーを開きます。
EncryptByKey 関数を使用して、パブリッシャで列データを暗号化します。
CLOSE SYMMETRIC KEY を実行してキーを閉じます。
暗号化された列を含むテーブルをパブリッシュします。詳細については、「パブリケーションを作成してアーティクルを定義する方法 (SQL Server Management Studio)」を参照してください。
パブリケーションにサブスクライブします。詳細については、「プルサブスクリプションを作成する方法 (SQL Server Management Studio)」または「プッシュサブスクリプションを作成する方法 (SQL Server Management Studio)」を参照してください。
サブスクリプションを初期化します。詳細については、「初期スナップショットを作成および適用する方法 (SQL Server Management Studio)」を参照してください。

手順 1 と同じ値を ALGORITHM、KEY_SOURCE、および IDENTITY_VALUE に使用して、サブスクライバで 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 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

参照

Share via

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

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

使用例

参照

概念

その他の技術情報

ヘルプおよび情報

その他のリソース