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

Artikel
05/07/2013

Mithilfe der Replikation können Sie verschlüsselte Spaltendaten veröffentlichen. Zum Entschlüsseln und Verwenden dieser Daten auf dem Abonnenten muss der zum Verschlüsseln der Daten auf dem Verleger verwendete Schlüssel auch auf dem Abonnenten vorhanden sein. Die Replikation bietet keinen sicheren Mechanismus zum Transportieren von Verschlüsselungsschlüsseln. Sie müssen den Verschlüsselungsschlüssel auf dem Abonnenten manuell neu erstellen. In diesem Thema wird veranschaulicht, wie Sie eine Spalte auf dem Verleger verschlüsseln und wie Sie sicherstellen, dass der Verschlüsselungsschlüssel auf dem Abonnenten verfügbar ist.

Dazu müssen die folgenden grundlegenden Schritte ausgeführt werden:

Erstellen Sie den symmetrischen Schlüssel auf dem Verleger.
Verschlüsseln Sie Spaltendaten mit dem symmetrischen Schlüssel.
Veröffentlichen Sie die Tabelle mit der verschlüsselten Spalte.
Abonnieren Sie die Veröffentlichung.
Initialisieren Sie das Abonnement.
Erstellen Sie den symmetrischen Schlüssel auf dem Abonnenten neu, indem Sie für ALGORITHM, KEY_SOURCE und IDENTITY_VALUE dieselben Werte wie in Schritt 1 verwenden.
Greifen Sie auf die verschlüsselten Spaltendaten zu.

Hinweis
Verwenden Sie zum Verschlüsseln von Spaltendaten einen symmetrischen Schlüssel. Der symmetrische Schlüssel kann auf unterschiedliche Weise auf dem Verleger und dem Abonnenten gesichert werden.

So erstellen und replizieren Sie verschlüsselte Spaltendaten

Führen Sie CREATE SYMMETRIC KEY auf dem Verleger aus.

Sicherheitshinweis
Der Wert von KEY_SOURCE stellt wichtige Daten dar, die verwendet werden können, um den symmetrischen Schlüssel neu zu erstellen und Daten zu entschlüsseln. KEY_SOURCE muss immer sicher gespeichert und transportiert werden.

Führen Sie OPEN SYMMETRIC KEY aus, um den neuen Schlüssel zu öffnen.
Verwenden Sie die EncryptByKey-Funktion, um Spaltendaten auf dem Verleger zu verschlüsseln.
Führen Sie CLOSE SYMMETRIC KEY aus, um den Schlüssel zu schließen.
Veröffentlichen Sie die Tabelle, die die verschlüsselte Spalte enthält. Weitere Informationen finden Sie unter Erstellen einer Veröffentlichung.
Abonnieren Sie die Veröffentlichung. Weitere Informationen finden Sie unter Erstellen eines Pullabonnements oder Erstellen eines Pushabonnements.
Initialisieren Sie das Abonnement. Weitere Informationen finden Sie unter Erstellen und Anwenden der Anfangsmomentaufnahme.

Führen Sie auf dem Abonnenten CREATE SYMMETRIC KEY aus, indem Sie für ALGORITHM, KEY_SOURCE und IDENTITY_VALUE die gleichen Werte wie in Schritt 1 verwenden. Für ENCRYPTION BY können Sie einen anderen Wert angeben.

Sicherheitshinweis
Der Wert von KEY_SOURCE stellt wichtige Daten dar, die verwendet werden können, um den symmetrischen Schlüssel neu zu erstellen und Daten zu entschlüsseln. KEY_SOURCE muss immer sicher gespeichert und transportiert werden.

Führen Sie OPEN SYMMETRIC KEY aus, um den neuen Schlüssel zu öffnen.
Verwenden Sie die DecryptByKey-Funktion, um replizierte Daten auf dem Verleger zu entschlüsseln.
Führen Sie CLOSE SYMMETRIC KEY aus, um den Schlüssel zu schließen.

Beispiel

In diesem Beispiel werden ein symmetrischer Schlüssel, ein Zertifikat zum Sichern des symmetrischen Schlüssels und ein Hauptschlüssel erstellt. Diese Schlüssel werden in der Veröffentlichungsdatenbank erstellt. Sie werden dann verwendet, um eine verschlüsselte Spalte (EncryptedCreditCardApprovalCode) in der SalesOrderHeader-Tabelle zu erstellen. Diese Spalte wird in der AdvWorksSalesOrdersMerge-Veröffentlichung anstelle der nicht verschlüsselten CreditCardApprovalCode-Spalte veröffentlicht. Fordern Sie nach Möglichkeit die Benutzer zur Laufzeit auf, die Sicherheitsanmeldeinformationen einzugeben. Wenn Anmeldeinformationen in einer Skriptdatei gespeichert werden müssen, muss die Datei an einem sicheren Ort gespeichert werden, 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

In diesem Beispiel wird derselbe symmetrische Schlüssel in der Abonnementdatenbank mithilfe derselben Werte für ALGORITHM, KEY_SOURCE und IDENTITY_VALUE aus dem ersten Beispiel neu erstellt. Bei diesem Beispiel wird davon ausgegangen, dass Sie bereits ein Abonnement der AdvWorksSalesOrdersMerge-Veröffentlichung initialisiert haben, um die verschlüsselte Spalte zu replizieren. Fordern Sie nach Möglichkeit die Benutzer zur Laufzeit auf, die Sicherheitsanmeldeinformationen einzugeben. Wenn Anmeldeinformationen in einer Skriptdatei gespeichert werden müssen, muss die Datei während des Speicherns und des Transports gesichert werden, um unberechtigten Zugriff zu vermeiden.

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

Konzepte

Sicherheitsübersicht (Replikation)

Erstellen identischer symmetrischer Schlüssel auf zwei Servern

Freigeben über

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

So erstellen und replizieren Sie verschlüsselte Spaltendaten

Beispiel

Siehe auch

Konzepte

Zusätzliche Ressourcen