Replizieren von Daten in verschlüsselten Spalten (SQL Server Management Studio)

Mit der Replikation können Sie verschlüsselte Spaltendaten veröffentlichen. Um diese Daten beim Abonnenten zu entschlüsseln und zu verwenden, muss der Schlüssel, der zum Verschlüsseln der Daten im Publisher verwendet wurde, auch auf dem Abonnenten vorhanden sein. Die Replikation bietet keinen sicheren Mechanismus zum Transport von Verschlüsselungsschlüsseln. Sie müssen den Verschlüsselungsschlüssel beim Abonnenten manuell erneut erstellen. In diesem Thema erfahren Sie, wie Sie eine Spalte im Publisher verschlüsseln und sicherstellen, dass der Verschlüsselungsschlüssel beim Abonnenten verfügbar ist.

Die grundlegenden Schritte lauten wie folgt:

1. Erstellen Sie den symmetrischen Schlüssel im Publisher.

2. Verschlüsseln sie Spaltendaten mit dem symmetrischen Schlüssel.

3. Veröffentlichen Sie die Tabelle mit der verschlüsselten Spalte.

4. Abonnieren Sie die Publikation.

5. Initialisieren Sie das Abonnement.

6. Erstellen Sie den symmetrischen Schlüssel beim Abonnenten neu, indem Sie dieselben Werte für ALGORITHM, KEY_SOURCE und IDENTITY_VALUE wie in Schritt 1 verwenden.

7. Greifen Sie auf die verschlüsselten Spaltendaten zu.

So erstellen und replizieren Sie verschlüsselte Spaltendaten

1. Führen Sie im Publisher CREATE SYMMETRIC KEY aus.

   Von Bedeutung

   Der Wert von KEY_SOURCE ist wertvolle Daten, mit denen die symmetrischen Schlüssel und Entschlüsselungsdaten neu erstellt werden können. KEY_SOURCE müssen immer sicher gelagert und transportiert werden.

2. Führen Sie OPEN SYMMETRIC KEY aus, um den neuen Schlüssel zu öffnen.

3. Verwenden Sie die EncryptByKey-Funktion , um Spaltendaten im Publisher zu verschlüsseln.

4. Führen Sie CLOSE SYMMETRIC KEY aus, um den Schlüssel zu schließen.

5. Veröffentlichen Sie die Tabelle, die die verschlüsselte Spalte enthält. Weitere Informationen finden Sie unter Erstellen einer Publikation.

6. Abonnieren Sie die Publikation. Weitere Informationen finden Sie unter Erstellen eines Pullabonnements oder Erstellen eines Pushabonnements.

7. Initialisieren Sie das Abonnement. Weitere Informationen finden Sie unter Erstellen und Anwenden der anfänglichen Momentaufnahme.

8. Führen Sie beim Abonnenten den Befehl CREATE SYMMETRIC KEY mit denselben Werten für ALGORITHM, KEY_SOURCE und IDENTITY_VALUE wie in Schritt 1 aus. Sie können einen anderen Wert für ENCRYPTION BY angeben.

   Von Bedeutung

   Der Wert von KEY_SOURCE sind wertvolle Daten, die zur Wiederherstellung des symmetrischen Schlüssels und zum Entschlüsseln von Daten verwendet werden können. KEY_SOURCE müssen immer sicher gelagert und transportiert werden.

9. Führen Sie OPEN SYMMETRIC KEY aus, um den neuen Schlüssel zu öffnen.

10. Verwenden Sie die DecryptByKey-Funktion , um replizierte Daten beim Abonnenten zu entschlüsseln.

11. Führen Sie CLOSE SYMMETRIC KEY aus, um den Schlüssel zu schließen.

Beispiel

In diesem Beispiel wird ein symmetrischer Schlüssel, ein Zertifikat erstellt, das zum Sichern des symmetrischen Schlüssels und eines Masterschlüssels verwendet wird. Diese Schlüssel werden in der Publikationsdatenbank erstellt. Sie werden dann verwendet, um eine verschlüsselte Spalte (EncryptedCreditCardApprovalCode) in der SalesOrderHeader Tabelle zu erstellen. Diese Spalte wird in der AdvWorksSalesOrdersMerge-Publikation anstelle der nicht verschlüsselten CreditCardApprovalCode-Spalte veröffentlicht. Wenn möglich, fordern Sie Benutzer auf, Zur Laufzeit Sicherheitsanmeldeinformationen einzugeben. Wenn Sie Anmeldeinformationen in einer Skriptdatei speichern müssen, müssen Sie die Datei schützen, um nicht autorisierten Zugriff zu verhindern.

-- 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

Beispiel

In diesem Beispiel wird der gleiche symmetrische Schlüssel in der Abonnementdatenbank mit denselben Werten für ALGORITHM, KEY_SOURCE und IDENTITY_VALUE aus dem ersten Beispiel neu erstellt. In diesem Beispiel wird davon ausgegangen, dass Sie bereits ein Abonnement für die AdvWorksSalesOrdersMerge-Publikation initialisiert haben, um die verschlüsselte Spalte zu replizieren. Wenn möglich, fordern Sie Benutzer auf, Zur Laufzeit Sicherheitsanmeldeinformationen einzugeben. Wenn Sie Anmeldeinformationen in einer Skriptdatei speichern müssen, müssen Sie die Datei während des Speichers und des Transports sichern, um nicht autorisierten Zugriff zu verhindern.

-- 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

Siehe auch

SQL Server-Replikationssicherheit
Erstellen identischer symmetrischer Schlüssel auf zwei Servern