Replicare dati in colonne crittografate (SQL Server Management Studio)Replicate Data in Encrypted Columns (SQL Server Management Studio)

La replica consente di pubblicare dati di colonna crittografati.Replication enables you to publish encrypted column data. Per decrittografare e utilizzare tali dati nel Sottoscrittore, la chiave utilizzata per crittografare i dati nel server di pubblicazione deve essere presente anche nel Sottoscrittore.To decrypt and use this data at the Subscriber, the key that was used to encrypt the data at the Publisher must also be present on the Subscriber. La replica non rappresenta un meccanismo protetto per il trasporto di chiavi di crittografia.Replication does not provide a secure mechanism to transport encryption keys. La chiave di crittografia deve essere ricreata manualmente nel Sottoscrittore.You must manually re-create the encryption key at the Subscriber. In questo argomento verrà illustrato come crittografare una colonna nel server di pubblicazione e garantire che la chiave di crittografia sia disponibile nel Sottoscrittore.This topic shows you how to encrypt a column at the Publisher and make sure that the encryption key is available at the Subscriber.

I passaggi principali sono i seguenti:The basic steps are as follows:

  1. Creare la chiave simmetrica nel server di pubblicazione.Create the symmetric key at the Publisher.

  2. Crittografare i dati della colonna con la chiave simmetrica.Encrypt column data with the symmetric key.

  3. Pubblicare la tabella con la colonna crittografata.Publish the table with the encrypted column.

  4. Sottoscrivere la pubblicazione.Subscribe to the publication.

  5. Inizializzare la sottoscrizione.Initialize the subscription.

  6. Ricreare la chiave simmetrica nel Sottoscrittore utilizzando i valori del passaggio 1 per ALGORITHM, KEY_SOURCE e IDENTITY_VALUE.Recreate the symmetric key at the Subscriber using same values for ALGORITHM, KEY_SOURCE, and IDENTITY_VALUE as in step 1.

  7. Accedere ai dati della colonna crittografata.Access the encrypted column data.

Nota

Per crittografare i dati della colonna è consigliabile utilizzare una chiave simmetrica.You should use a symmetric key to encrypt column data. La chiave simmetrica può essere protetta in modi diversi nel server di pubblicazione e nel Sottoscrittore.The symmetric key itself can be secured by different means at the Publisher and Subscriber.

Per creare e replicare dati di colonne crittografateTo create and replicate encrypted column data

  1. Nel server di pubblicazione eseguire CREATE SYMMETRIC KEY.At the Publisher, execute CREATE SYMMETRIC KEY.

    Importante

    Il valore di KEY_SOURCE è rappresentato da dati importanti, utilizzabili per ricreare la chiave simmetrica e decrittografare i dati.The value of KEY_SOURCE is valuable data that can be used to re-create the symmetric key and decrypt data. KEY_SOURCE deve pertanto essere sempre archiviato e trasportato in modo protetto.KEY_SOURCE must always be stored and transported securely.

  2. Eseguire OPEN SYMMETRIC KEY per aprire la nuova chiave.Execute OPEN SYMMETRIC KEY to open the new key.

  3. Utilizzare la funzione EncryptByKey per crittografare i dati della colonna nel server di pubblicazione.Use the EncryptByKey function to encrypt column data at the Publisher.

  4. Eseguire CLOSE SYMMETRIC KEY per chiudere la chiave.Execute CLOSE SYMMETRIC KEY to close the key.

  5. Pubblicare la tabella contenente la colonna crittografata.Publish the table that contains the encrypted column. Per altre informazioni, vedere Create a Publication.For more information, see Create a Publication.

  6. Sottoscrivere la pubblicazione.Subscribe to the publication. Per altre informazioni, vedere Creare una sottoscrizione pull o Creare una sottoscrizione push.For more information, see Create a Pull Subscription or Create a Push Subscription.

  7. Inizializzare la sottoscrizione.Initialize the subscription. Per altre informazioni, vedere Create and Apply the Initial Snapshot.For more information, see Create and Apply the Initial Snapshot.

  8. Nel Sottoscrittore eseguire CREATE SYMMETRIC KEY utilizzando i valori del passaggio 1 per ALGORITHM, KEY_SOURCE e IDENTITY_VALUE.At the Subscriber, execute CREATE SYMMETRIC KEY using the same values for ALGORITHM, KEY_SOURCE, and IDENTITY_VALUE as in step 1. È possibile specificare un valore diverso per ENCRYPTION BY.You can specify a different value for ENCRYPTION BY.

    Importante

    Il valore di KEY_SOURCE è rappresentato da dati importanti, utilizzabili per ricreare la chiave simmetrica e decrittografare i dati.The value of KEY_SOURCE is valuable data that can be used to re-create the symmetric key and decrypt data. KEY_SOURCE deve pertanto essere sempre archiviato e trasportato in modo protetto.KEY_SOURCE must always be stored and transported securely.

  9. Eseguire OPEN SYMMETRIC KEY per aprire la nuova chiave.Execute OPEN SYMMETRIC KEY to open the new key.

  10. Utilizzare la funzione DecryptByKey per decrittografare i dati replicati nel Sottoscrittore.Use the DecryptByKey function to decrypt replicated data at the Subscriber.

  11. Eseguire CLOSE SYMMETRIC KEY per chiudere la chiave.Execute CLOSE SYMMETRIC KEY to close the key.

EsempioExample

In questo esempio verranno creati una chiave simmetrica, un certificato utilizzato per proteggere la chiave simmetrica e una chiave master.This example creates a symmetric key, a certificate that is used to help secure the symmetric key, and a master key. Le chiavi verranno create nel database di pubblicazione eThese keys are created in the publication database. verranno utilizzate per creare una colonna crittografata (EncryptedCreditCardApprovalCode) nella tabella SalesOrderHeader .They are then used to create an encrypted column (EncryptedCreditCardApprovalCode) in the SalesOrderHeader table. Questa colonna verrà pubblicata nella pubblicazione AdvWorksSalesOrdersMerge al posto della colonna non crittografata CreditCardApprovalCode.This column is published in the AdvWorksSalesOrdersMerge publication instead of the unencrypted CreditCardApprovalCode column. Se possibile, richiedere agli utenti di immettere le credenziali di sicurezza in fase di esecuzione.When possible, prompt users to enter security credentials at runtime. Se è necessario archiviare le credenziali in un file script, è fondamentale proteggere il file per evitare accessi non autorizzati.If you must store credentials in a script file, you must secure the file to prevent unauthorized access.


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

EsempioExample

In questo esempio verrà ricreata la stessa chiave simmetrica nel database di sottoscrizione utilizzando i valori di ALGORITHM, KEY_SOURCE e IDENTITY_VALUE del primo esempio.This example recreates the same symmetric key in the subscription database using the same values for ALGORITHM, KEY_SOURCE, and IDENTITY_VALUE from the first example. Si presume che sia già stata inizializzata una sottoscrizione alla pubblicazione AdvWorksSalesOrdersMerge per la replica della colonna crittografata.This example assumes that you have already initialized a subscription to the AdvWorksSalesOrdersMerge publication to replicate the encrypted column. Se possibile, richiedere agli utenti di immettere le credenziali di sicurezza in fase di esecuzione.When possible, prompt users to enter security credentials at runtime. Se è necessario archiviare le credenziali in un file script, è fondamentale proteggere il file durante l'archiviazione e il trasporto per evitare accessi non autorizzati.If you must store credentials in a script file, you must secure the file during storage and transport to prevent unauthorized access.


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

Vedere ancheSee Also

Panoramica della sicurezza (replica) Security Overview (Replication)
Creare chiavi simmetriche identiche su due serverCreate Identical Symmetric Keys on Two Servers