Répliquer des données dans des colonnes chiffrées (SQL Server Management Studio)
S’applique à : 
SQL Server Azure SQL Managed Instance
La réplication permet de publier des données de colonnes chiffrées. Pour déchiffrer et exploiter ces données sur l'Abonné, la clé employée pour chiffrer les données sur le serveur de publication doit également être présente sur l'Abonné. La réplication n'offre pas un mécanisme sécurisé de transport des clés de chiffrement. Vous devez manuellement recréer la clé de chiffrement sur l'Abonné. Cette rubrique explique comment chiffrer une colonne sur le serveur de publication et vous assurer que la clé de chiffrement est disponible au niveau de l'Abonné.
Les étapes de base sont les suivantes :
Créez la clé symétrique sur le serveur de publication.
Chiffrez les données de la colonne avec la clé symétrique.
Publiez la table avec la colonne chiffrée.
Abonnez-vous à la publication.
Initialisez l'abonnement.
Recréez la clé symétrique sur l'Abonné en utilisant les mêmes valeurs ALGORITHM, KEY_SOURCE et IDENTITY_VALUE qu'à l'étape 1.
Accédez aux données chiffrées de la colonne.
Remarque
Il est préférable que vous utilisiez une clé symétrique pour chiffrer les données de la colonne. La clé symétrique elle-même peut être sécurisée selon différents moyens sur le serveur de publication et sur l'Abonné.
Pour créer et répliquer les données chiffrées de la colonne
Sur le serveur de publication, exécutez CREATE SYMMETRIC KEY.
Important
La valeur KEY_SOURCE est un ensemble de données précieuses qu'il est possible d'utiliser pour recréer la clé symétrique et déchiffrer des données. La valeur KEY_SOURCE doit toujours être stockée et transportée de manière sécurisée.
Exécutez OPEN SYMMETRIC KEY pour ouvrir la nouvelle clé.
Utilisez la fonction EncryptByKey pour chiffrer les données de la colonne sur le serveur de publication.
Exécutez CLOSE SYMMETRIC KEY pour fermer la clé.
Publiez la table contenant la colonne chiffrée. Pour plus d’informations, voir Create a Publication.
Abonnez-vous à la publication. Pour plus d’informations, consultez Créer un abonnement par extraction de données (pull) ou Créer un abonnement par émission (push).
Initialisez l'abonnement. Pour plus d’informations, voir Create and Apply the Initial Snapshot.
Sur l'abonné, exécutez CREATE SYMMETRIC KEY en utilisant les mêmes valeurs pour ALGORITHM, KEY_SOURCE et IDENTITY_VALUE qu'à l'étape 1. Vous pouvez spécifier une valeur différente pour la clause ENCRYPTION BY.
Important
La valeur KEY_SOURCE est un ensemble de données précieuses qu'il est possible d'utiliser pour recréer la clé symétrique et déchiffrer des données. La valeur KEY_SOURCE doit toujours être stockée et transportée de manière sécurisée.
Exécutez OPEN SYMMETRIC KEY pour ouvrir la nouvelle clé.
Utilisez la fonction DecryptByKey pour déchiffrer les données répliquées sur l'Abonné.
Exécutez CLOSE SYMMETRIC KEY pour fermer la clé.
Exemples
R. Créer des clés dans la base de données de publication
Cet exemple crée une clé symétrique, un certificat servant à sécuriser la clé symétrique et une clé principale. Ces clés sont créées dans la base de données de publication. Elles sont ensuite utilisées pour créer une colonne chiffrée (EncryptedCreditCardApprovalCode) dans la table SalesOrderHeader . Cette colonne est publiée dans la publication AdvWorksSalesOrdersMerge au lieu de la colonne non chiffrée CreditCardApprovalCode. Lorsque c'est possible, demande aux utilisateurs de fournir les informations d'identification au moment de l'exécution. Si vous devez enregistrer les informations d'identification dans un fichier de script, vous devez sécuriser le fichier pour empêcher un accès non autorisé.
-- 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. Créer des clés dans la base de données d’abonnement
Cet exemple recrée la même clé symétrique dans la base de données d'abonnement en utilisant les mêmes valeurs ALGORITHM, KEY_SOURCE et IDENTITY_VALUE que dans le premier exemple. Cet exemple part du principe que vous avez déjà initialisé un abonnement dans la publication AdvWorksSalesOrdersMerge dans le but de répliquer la colonne chiffrée. Lorsque c'est possible, demande aux utilisateurs de fournir les informations d'identification au moment de l'exécution. Si vous devez stocker des informations d'identification dans un fichier de script, vous devez sécuriser le fichier pour empêcher tout accès non autorisé.
-- 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
Voir aussi
Afficher et modifier les paramètres de sécurité de la réplication
Créer des clés symétriques identiques sur deux serveurs