Replicar datos en columnas cifradas (SQL Server Management Studio)
Se aplica a: 
SQL Server Azure SQL Managed Instance
La replicación le permite publicar datos de columna cifrados. Para descifrar y usar estos datos en el suscriptor, la clave usada para cifrar los datos en el publicador también debe estar presente en el suscriptor. La replicación no ofrece un mecanismo de seguridad para transportar las claves de cifrado. Debe volver a crear manualmente la clave de cifrado en el suscriptor. En este tema se muestra cómo cifrar una columna en el publicador y asegurarse de que la clave de cifrado esté disponible en el suscriptor.
Los pasos básicos son los siguientes:
Cree la clave simétrica en el publicador.
Cifre los datos de la columna con la clave simétrica.
Publique la tabla con la columna cifrada.
Suscríbase a la publicación.
Inicialice la suscripción.
Vuelva a crear la clave simétrica en el suscriptor con los mismos valores para ALGORITHM, KEY_SOURCE e IDENTITY_VALUE que en el paso 1.
Obtenga acceso a los datos de columna cifrados.
Nota:
Debe usar una clave simétrica para cifrar los datos de columna. La propia clave simétrica puede protegerse mediante distintos métodos en el publicador y el suscriptor.
Para crear y replicar los datos de columna cifrados
En el publicador, ejecute CREATE SYMMETRIC KEY.
Importante
El valor de KEY_SOURCE son datos importantes que pueden utilizarse para volver a crear la clave simétrica y descifrar los datos. KEY_SOURCE siempre debe almacenarse y transportarse de forma segura.
Ejecute OPEN SYMMETRIC KEY para abrir la nueva clave.
Use la función EncryptByKey para cifrar los datos de columna en el publicador.
Ejecute CLOSE SYMMETRIC KEY para cerrar la clave.
Publique la tabla que contiene la columna cifrada. Para obtener más información, vea Crear una suscripción.
Suscríbase a la publicación. Para obtener más información, vea Crear una suscripción de extracción o Crear una suscripción de inserción.
Inicialice la suscripción. Para más información, consulte Crear y aplicar la instantánea inicial.
En el suscriptor, ejecute CREATE SYMMETRIC KEY con los mismos valores para ALGORITHM, KEY_SOURCE e IDENTITY_VALUE que en el paso 1. Puede especificar un valor distinto para ENCRYPTION BY.
Importante
El valor de KEY_SOURCE son datos importantes que pueden utilizarse para volver a crear la clave simétrica y descifrar los datos. KEY_SOURCE siempre debe almacenarse y transportarse de forma segura.
Ejecute OPEN SYMMETRIC KEY para abrir la nueva clave.
Use la función DecryptByKey para descifrar los datos replicados en el suscriptor.
Ejecute CLOSE SYMMETRIC KEY para cerrar la clave.
Ejemplos
A Creación de claves en la base de datos de publicación
En este ejemplo se crea una clave simétrica, un certificado que se usa para ayudar a proteger la clave simétrica y una clave maestra. Estas claves se crean en la base de datos de publicaciones y, a continuación, se usan para crear una columna cifrada (EncryptedCreditCardApprovalCode) en la tabla SalesOrderHeader . Esta columna se publica en la publicación AdvWorksSalesOrdersMerge en lugar de en la columna CreditCardApprovalCode no cifrada. Cuando sea posible, pida a los usuarios que proporcionen credenciales de seguridad en tiempo de ejecución. Si debe almacenar las credenciales en un archivo de script, proteja el archivo para evitar el acceso no autorizado.
-- Execute at the Publisher on the publication database.
USE AdventureWorks2022;
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_ReplDataShare'), 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
B. Creación de claves en la base de datos de suscripciones
En este ejemplo se vuelve a crear la misma clave simétrica en la base de datos de suscripciones con los mismos valores ALGORITHM, KEY_SOURCE e IDENTITY_VALUE del primer ejemplo. En este ejemplo se da por supuesto que ya ha inicializado una suscripción a la publicación AdvWorksSalesOrdersMerge para replicar la columna cifrada. Cuando sea posible, pida a los usuarios que proporcionen credenciales de seguridad en tiempo de ejecución. Si tiene que almacenar credenciales en un archivo de script, debe proteger el archivo durante el almacenamiento y el transporte para evitar el acceso no autorizado.
-- Execute at the Subscription on the subscription database.
USE AdventureWorks2022Replica;
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_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_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