Replicar dados em colunas criptografadas (SQL Server Management Studio)

A replicação permite que você publique dados de coluna criptografados. Para descriptografar e usar esses dados no Assinante, a chave usada para criptografar os dados no Publicador também deve estar presente no Assinante. A replicação não fornece um mecanismo seguro para transportar chaves de criptografia. Você deve recriar manualmente a chave de criptografia no Assinante. Este tópico mostra como criptografar uma coluna no Publicador e verificar se a chave de criptografia está disponível no Assinante.

As etapas básicas são as seguintes:

Crie a chave simétrica no Publicador.
Criptografar dados de coluna com a chave simétrica.
Publique a tabela com a coluna criptografada.
Assine a publicação.
Inicialize a assinatura.
Recrie a chave simétrica no Assinante usando os mesmos valores para ALGORITHM, KEY_SOURCE e IDENTITY_VALUE como na etapa 1.
Acesse os dados da coluna criptografada.

Observação

Você deve usar uma chave simétrica para criptografar dados de coluna. A própria chave simétrica pode ser protegida por diferentes meios no Publicador e no Assinante.

Para criar e replicar dados de coluna criptografados

No Publicador, execute CREATE SYMMETRIC KEY.

Importante

O valor de KEY_SOURCE são dados valiosos que podem ser usados para recriar a chave simétrica e descriptografar dados. KEY_SOURCE deve ser sempre armazenado e transportado com segurança.
Execute OPEN SYMMETRIC KEY para abrir a nova chave.
Use a função EncryptByKey para criptografar dados de coluna no Publicador.
Execute CLOSE SYMMETRIC KEY para encerrar a chave.
Publique a tabela que contém a coluna criptografada. Para obter mais informações, consulte Criar uma publicação.
Assine a publicação. Para obter mais informações, consulte Criar uma assinatura pull ou criar uma assinatura push.
Inicialize a assinatura. Para obter mais informações, consulte Criar e aplicar o instantâneo inicial.
No Subscritor, execute CREATE SYMMETRIC KEY usando os mesmos valores para ALGORITHM, KEY_SOURCE e IDENTITY_VALUE como na etapa 1. Você pode especificar um valor diferente para ENCRYPTION BY.

Importante

O valor de KEY_SOURCE são dados valiosos que podem ser usados para recriar a chave simétrica e descriptografar dados. KEY_SOURCE deve ser sempre armazenado e transportado com segurança.
Execute OPEN SYMMETRIC KEY para abrir a nova chave.
Use a função DecryptByKey para descriptografar dados replicados no lado do assinante.
Execute o comando CLOSE SYMMETRIC KEY para fechar a chave.

Exemplo

Este exemplo cria uma chave simétrica, um certificado usado para ajudar a proteger a chave simétrica e uma chave mestra. Essas chaves são criadas no banco de dados de publicação. Em seguida, eles são usados para criar uma coluna criptografada (EncryptedCreditCardApprovalCode) na SalesOrderHeader tabela. Esta coluna é publicada na publicação AdvWorksSalesOrdersMerge em vez da coluna CreditCardApprovalCode não criptografada. Quando possível, solicite aos usuários que insiram credenciais de segurança no runtime. Se você precisar armazenar credenciais em um arquivo de script, deverá proteger o arquivo para impedir o acesso não autorizado.


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

Exemplo

Este exemplo recria a mesma chave simétrica no banco de dados de assinatura usando os mesmos valores para ALGORITHM, KEY_SOURCE e IDENTITY_VALUE do primeiro exemplo. Este exemplo pressupõe que você já inicializou uma assinatura para a publicação AdvWorksSalesOrdersMerge para replicar a coluna criptografada. Quando possível, solicite aos usuários que insiram credenciais de segurança no runtime. Se você precisar armazenar credenciais em um arquivo de script, deverá proteger o arquivo durante o armazenamento e o transporte para impedir o acesso não autorizado.


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

Consulte Também

Segurança de Replicação do SQL Server
Criar chaves simétricas idênticas em dois servidores

Last updated on 2017-03-07

Compartilhar via

Replicar dados em colunas criptografadas (SQL Server Management Studio)

Para criar e replicar dados de coluna criptografados

Exemplo

Exemplo

Consulte Também

Recursos adicionais