Artykuł
07/22/2011

Jak Replikowanie danych w kolumnach zaszyfrowane (SQL Server Management Studio)

Replikacja umożliwia publikowanie danych zaszyfrowanych kolumna.Do odszyfrowania i użyć tych danych przez subskrybenta klucz użyty do zaszyfrowania danych na Wydawca musi być obecny w subskrybenta.Replikacja nie zapewnia bezpieczny mechanizm transportu kluczy szyfrowanie.Należy ręcznie odtworzyć klucz szyfrowanie przez subskrybenta.W tym temacie przedstawiono sposób szyfrowanie kolumna w Wydawca i upewnij się, że klucz szyfrowanie jest dostępna przez subskrybenta.

Podstawowe kroki są następujące:

Utwórz klucz zawartości w Wydawca.
Szyfrowanie danych kolumna z klucz zawartości.
Publikuje tabela z zaszyfrowanego kolumna.
Subskrybuj do publikacja.
Inicjowanie subskrypcja.
Utwórz klucz zawartości subskrybent przy użyciu tej samej wartości dla ALGORYTMU, KEY_SOURCE i IDENTITY_VALUE w kroku 1.
Dostęp do danych zaszyfrowanych kolumna.

Ostrzeżenie

Należy używać klucz zawartości do szyfrowania danych kolumna.klucz zawartości, sam mogą być chronione za pomocą różnych środków na Wydawca i abonenta.

Aby utworzyć i replikowania danych zaszyfrowanych kolumna

Wydawca, należy wykonać Utworzyć klucz SYMETRYCZNY.

Uwaga dotycząca zabezpieczeń
Wartość KEY_SOURCE jest używany do odtworzenia danych klucz zawartości i odszyfrowania danych.Zawsze KEY_SOURCE muszą być przechowywane i transportowane w sposób bezpieczny.

Wykonanie OTWORZYĆ klucz SYMETRYCZNEGO do otwarcia nowego klucz.
Użyj EncryptByKey funkcja szyfrowania danych kolumna na Wydawca.
Wykonanie ZAMKNĄĆ klucz SYMETRYCZNEGO zamknąć klucz.
Publikuje tabela zawierającą kolumna zaszyfrowane.Aby uzyskać więcej informacji, zobacz Jak Utwórz publikację i artykułów (SQL Server Management Studio).
Subskrybuj do publikacja.Aby uzyskać więcej informacji, zobacz Jak Tworzenie subskrypcji ściąganej (SQL Server Management Studio) lub Jak Utwórz subskrypcję wypychaną (SQL Server Management Studio).
Inicjowanie subskrypcja.Aby uzyskać więcej informacji, zobacz Jak Tworzenie i stosowanie migawki początkowego (SQL Server Management Studio).

Subskrybent, wykonać Utworzyć klucz SYMETRYCZNY przy użyciu tej samej wartości dla ALGORYTMU, KEY_SOURCE i IDENTITY_VALUE w kroku 1.Dla szyfrowania przez można określić różne wartości.

Uwaga dotycząca zabezpieczeń
Wartość KEY_SOURCE jest używany do odtworzenia danych klucz zawartości i odszyfrowania danych.Zawsze KEY_SOURCE muszą być przechowywane i transportowane w sposób bezpieczny.

Wykonanie OTWORZYĆ klucz SYMETRYCZNEGO do otwarcia nowego klucz.
Użyj DecryptByKey funkcja odszyfrować replikowanych danych przez subskrybenta.
Wykonanie ZAMKNĄĆ klucz SYMETRYCZNEGO zamknąć klucz.

Przykład

W tym przykładzie tworzony jest klucz zawartości certyfikat, który jest używany do zabezpieczania klucz zawartości i klucza głównego.Klucze te są tworzone w baza danych publikacja.Są one następnie używane do tworzenia zaszyfrowanego kolumna (EncryptedCreditCardApprovalCode) w SalesOrderHeader tabela.W tej kolumnie jest publikowany w publikacja AdvWorksSalesOrdersMerge w niezaszyfrowanej kolumna CreditCardApprovalCode.Jeśli to możliwe, monitują użytkowników o wprowadzenie poświadczenia zabezpieczeń w czasie wykonywania.Jeśli poświadczenia muszą być przechowywane w pliku skryptu, należy zabezpieczyć plik, aby uniemożliwić nieautoryzowany dostęp.

-- Execute at the Publisher on the publication database.
USE AdventureWorks2008R2;
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

W tym przykładzie ponownie tworzy taki sam klucz zawartości w bazie danych subskrypcja przy użyciu tej samej wartości dla ALGORYTMU, KEY_SOURCE i IDENTITY_VALUE w pierwszym przykładzie.W tym przykładzie założono, został już zainicjowany subskrypcja do publikacja AdvWorksSalesOrdersMerge do replikowania zaszyfrowanych kolumna.Jeśli to możliwe, monitują użytkowników o wprowadzenie poświadczenia zabezpieczeń w czasie wykonywania.Jeśli poświadczenia muszą być przechowywane w pliku skryptu, należy zabezpieczyć plik podczas składowania i transportu, aby uniemożliwić nieautoryzowany dostęp.

-- Execute at the Subscription on the subscription database.
USE AdventureWorks2008R2Replica;
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

Zobacz także

Koncepcje

Omówienie zabezpieczeń (replikacji)

Jak Tworzenie kluczy symetrycznych identyczne na dwóch serwerach

Udostępnij za pośrednictwem

Jak Replikowanie danych w kolumnach zaszyfrowane (SQL Server Management Studio)

Aby utworzyć i replikowania danych zaszyfrowanych kolumna

Przykład

Zobacz także

Koncepcje

Dodatkowe zasoby