Remarque
L’accès à cette page nécessite une autorisation. Vous pouvez essayer de vous connecter ou de modifier des répertoires.
L’accès à cette page nécessite une autorisation. Vous pouvez essayer de modifier des répertoires.
La réplication vous permet de publier des données de colonne chiffrées. Pour déchiffrer et utiliser ces données sur l’Abonné, la clé utilisée pour chiffrer les données sur le serveur de publication doit également être présente sur l’Abonné. La réplication ne fournit pas de mécanisme sécurisé pour transporter les clés de chiffrement. Vous devez recréer manuellement la clé de chiffrement sur l’Abonné. Cette rubrique vous montre comment chiffrer une colonne sur le serveur de publication et vous assurer que la clé de chiffrement est disponible sur l’Abonné.
Les étapes de base sont les suivantes :
Créez la clé symétrique sur le serveur de publication.
Chiffrer les données de 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é à l’aide des mêmes valeurs pour ALGORITHM, KEY_SOURCE et IDENTITY_VALUE que lors de l’étape 1.
Accédez aux données de colonne chiffrées.
Remarque
Vous devez utiliser une clé symétrique pour chiffrer les données de colonne. La clé symétrique elle-même peut être sécurisée par différents moyens au niveau du serveur de publication et de l’abonné.
Pour créer et répliquer des données de colonne chiffrées
Sur le serveur de l'éditeur, exécutez CREATE SYMMETRIC KEY.
Important
La valeur de KEY_SOURCE est des données précieuses qui peuvent être utilisées pour recréer la clé symétrique et déchiffrer les données. KEY_SOURCE doivent toujours être stockées et transportées en toute sécurité.
Exécutez OPEN SYMMETRIC KEY pour ouvrir la nouvelle clé.
Utilisez la fonction EncryptByKey pour chiffrer les données des colonnes au niveau du Publisher.
Exécutez CLOSE SYMMETRIC KEY pour fermer la clé.
Publiez la table qui contient la colonne chiffrée. Pour plus d’informations, consultez Créer une publication.
Abonnez-vous à la publication. Pour plus d’informations, consultez Créer un abonnement par extraction ou créer un abonnement Push.
Initialisez l’abonnement. Pour plus d’informations, consultez Créer et appliquer l’instantané initial.
Du côté de l'abonné, exécutez CREATE SYMMETRIC KEY à l’aide des mêmes valeurs pour ALGORITHM, KEY_SOURCE et IDENTITY_VALUE que dans l’étape 1. Vous pouvez spécifier une valeur différente pour ENCRYPTION BY.
Important
La valeur de KEY_SOURCE est des données précieuses qui peuvent être utilisées pour recréer la clé symétrique et déchiffrer les données. KEY_SOURCE doivent toujours être stockées et transportées en toute sécurité.
Exécutez OPEN SYMMETRIC KEY pour ouvrir la nouvelle clé.
Utilisez la fonction DecryptByKey pour déchiffrer les données répliquées chez l’Abonné.
Exécutez CLOSE SYMMETRIC KEY pour fermer la clé.
Exemple :
Cet exemple crée une clé symétrique, un certificat utilisé pour sécuriser la clé symétrique et une clé principale. Ces clés sont créées dans la base de données de publication. Ils sont ensuite utilisés pour créer une colonne chiffrée (EncryptedCreditCardApprovalCode) dans la SalesOrderHeader table. Cette colonne est publiée dans la publication AdvWorksSalesOrdersMerge au lieu de la colonne CreditCardApprovalCode non chiffrée. Si possible, invitez les utilisateurs à entrer des informations d’identification de sécurité 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 l’accès non autorisé.
-- 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
Exemple :
Cet exemple recrée la même clé symétrique dans la base de données d’abonnement à l’aide des mêmes valeurs pour ALGORITHM, KEY_SOURCE et IDENTITY_VALUE à partir du premier exemple. Cet exemple suppose que vous avez déjà initialisé un abonnement à la publication AdvWorksSalesOrdersMerge pour répliquer la colonne chiffrée. Si possible, invitez les utilisateurs à entrer des informations d’identification de sécurité au moment de l’exécution. Si vous devez stocker les informations d’identification dans un fichier de script, vous devez sécuriser le fichier pendant le stockage et le transport pour empêcher l’accès non autorisé.
-- 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
Voir aussi
Sécurité de la réplication SQL Server
Créer des clés symétriques identiques sur deux serveurs