Replicare dati in colonne crittografate (SQL Server Management Studio)
Si applica a:
SQL ServerIstanza gestita di SQL di Azure
La replica consente di pubblicare dati di colonna crittografati. 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. La replica non rappresenta un meccanismo protetto per il trasporto di chiavi di crittografia. La chiave di crittografia deve essere ricreata manualmente nel Sottoscrittore. In questo argomento verrà illustrato come crittografare una colonna nel server di pubblicazione e garantire che la chiave di crittografia sia disponibile nel Sottoscrittore.
I passaggi di base sono i seguenti:
Creare la chiave simmetrica nel server di pubblicazione.
Crittografare i dati della colonna con la chiave simmetrica.
Pubblicare la tabella con la colonna crittografata.
Sottoscrivere la pubblicazione.
Inizializzare la sottoscrizione.
Ricreare la chiave simmetrica nel Sottoscrittore utilizzando i valori del passaggio 1 per ALGORITHM, KEY_SOURCE e IDENTITY_VALUE.
Accedere ai dati della colonna crittografata.
Nota
Per crittografare i dati della colonna è consigliabile utilizzare una chiave simmetrica. La chiave simmetrica può essere protetta in modi diversi nel server di pubblicazione e nel Sottoscrittore.
Per creare e replicare dati di colonne crittografate
Nel server di pubblicazione eseguire CREATE SYMMETRIC KEY.
Importante
Il valore di KEY_SOURCE è rappresentato da dati importanti, utilizzabili per ricreare la chiave simmetrica e decrittografare i dati. KEY_SOURCE deve pertanto essere sempre archiviato e trasportato in modo protetto.
Eseguire OPEN SYMMETRIC KEY per aprire la nuova chiave.
Utilizzare la funzione EncryptByKey per crittografare i dati della colonna nel server di pubblicazione.
Eseguire CLOSE SYMMETRIC KEY per chiudere la chiave.
Pubblicare la tabella contenente la colonna crittografata. Per altre informazioni, vedere Create a Publication.
Sottoscrivere la pubblicazione. Per altre informazioni, vedere Creare una sottoscrizione pull o Creare una sottoscrizione push.
Inizializzare la sottoscrizione. Per altre informazioni, vedere Creazione e applicazione dello snapshot iniziale.
Nel Sottoscrittore eseguire CREATE SYMMETRIC KEY utilizzando i valori del passaggio 1 per ALGORITHM, KEY_SOURCE e IDENTITY_VALUE. È possibile specificare un valore diverso per ENCRYPTION BY.
Importante
Il valore di KEY_SOURCE è rappresentato da dati importanti, utilizzabili per ricreare la chiave simmetrica e decrittografare i dati. KEY_SOURCE deve pertanto essere sempre archiviato e trasportato in modo protetto.
Eseguire OPEN SYMMETRIC KEY per aprire la nuova chiave.
Utilizzare la funzione DecryptByKey per decrittografare i dati replicati nel Sottoscrittore.
Eseguire CLOSE SYMMETRIC KEY per chiudere la chiave.
Esempi
R. Creare chiavi nel database di pubblicazione
In questo esempio verranno creati una chiave simmetrica, un certificato utilizzato per proteggere la chiave simmetrica e una chiave master. Le chiavi verranno create nel database di pubblicazione e verranno utilizzate per creare una colonna crittografata (EncryptedCreditCardApprovalCode) nella tabella SalesOrderHeader . Questa colonna verrà pubblicata nella pubblicazione AdvWorksSalesOrdersMerge al posto della colonna non crittografata CreditCardApprovalCode. Se possibile, richiedere agli utenti di immettere le credenziali di sicurezza in fase di esecuzione. Se è necessario archiviare le credenziali in un file script, è fondamentale proteggere il file per evitare accessi non autorizzati.
-- 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. Creare chiavi nel database di sottoscrizione
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. Si presume che sia già stata inizializzata una sottoscrizione alla pubblicazione AdvWorksSalesOrdersMerge per la replica della colonna crittografata. Se possibile, richiedere agli utenti di immettere le credenziali di sicurezza in fase di esecuzione. Se è necessario archiviare le credenziali in un file script, è fondamentale proteggere il file durante l'archiviazione e il trasporto per evitare accessi non autorizzati.
-- 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
Vedi anche
Visualizzare e modificare le impostazioni di sicurezza della replica
Creare chiavi simmetriche identiche su due server
Commenti e suggerimenti
Presto disponibile: Nel corso del 2024 verranno gradualmente disattivati i problemi di GitHub come meccanismo di feedback per il contenuto e ciò verrà sostituito con un nuovo sistema di feedback. Per altre informazioni, vedere https://aka.ms/ContentUserFeedback.
Invia e visualizza il feedback per