Replikace dat v šifrovaných sloupcích (SQL Server Management Studio)

platí pro:SQL Server azure SQL Managed Instance

Replikace umožňuje publikovat šifrovaná data sloupců. K dešifrování a používání těchto dat odběratelem musí být u odběratele přítomen i klíč, který byl použit pro šifrování dat u vydavatele. Replikace neposkytuje zabezpečený mechanismus přenosu šifrovacích klíčů. Šifrovací klíč musíte znovu vytvořit ručně u odběratele. V tomto tématu se dozvíte, jak zašifrovat sloupec v Publisheru a ujistit se, že je šifrovací klíč dostupný u odběratele.

Základní kroky jsou následující:

Vytvořte symetrický klíč v Publisheru.
Šifrování dat sloupců pomocí symetrického klíče
Publikujte tabulku se zašifrovaným sloupcem.
Přihlaste se k odběru publikace.
Inicializace předplatného
Znovu vytvořte symetrický klíč u odběratele pomocí stejných hodnot pro ALGORITMUS, KEY_SOURCE a IDENTITY_VALUE jako v kroku 1.
Přístup k šifrovaným datům sloupců

Poznámka

K šifrování dat sloupců byste měli použít symetrický klíč. Symetrický klíč může být u Publisheru a Subscriberu zabezpečen různými způsoby.

Vytvoření a replikace šifrovaných dat sloupců

V Publisheru spusťte CREATE SYMMETRIC KEY.

Důležitý

Hodnota KEY_SOURCE je cenná data, která lze použít k opětovnému vytvoření symetrického klíče a dešifrování dat. KEY_SOURCE musí být vždy bezpečně uloženy a přenášeny.
Spuštěním OPEN SYMMETRIC KEY otevřete nový klíč.
K šifrování dat sloupců v Publisheru použijte funkci EncryptByKey.
Proveďte ZAVŘÍT SYMETRICKÝ KLÍČ pro zavření klíče.
Publikujte tabulku, která obsahuje šifrovaný sloupec. Další informace naleznete v tématu Vytvoření publikace.
Přihlaste se k odběru publikace. Další informace najdete v tématu Vytvoření předplatného vyžádané replikace nebo Vytvoření předplatného nabízené replikace.
Inicializace předplatného Další informace najdete v tématu Vytvoření a použití počátečního snímku.
U odběratele spusťte CREATE SYMMETRIC KEY pomocí stejných hodnot pro ALGORITMUS, KEY_SOURCE a IDENTITY_VALUE jako v kroku 1. Pro ŠIFROVÁNÍ BY můžete zadat jinou hodnotu.

Důležitý

Hodnota KEY_SOURCE je cenná data, která lze použít k opětovnému vytvoření symetrického klíče a dešifrování dat. KEY_SOURCE musí být vždy bezpečně uloženy a přenášeny.
Spuštěním OPEN SYMMETRIC KEY otevřete nový klíč.
Pomocí funkce DecryptByKey dešifrujte replikovaná data u odběratele.
Proveďte ZAVŘÍT SYMETRICKÝ KLÍČ, abyste klíč zavřeli.

Příklady

A. Vytvoření klíčů v databázi publikace

Tento příklad vytvoří symetrický klíč, certifikát, který slouží k zabezpečení symetrického klíče a hlavního klíče. Tyto klíče se vytvoří v databázi publikace. Pak se používají k vytvoření šifrovaného sloupce (EncryptedCreditCardApprovalCode) v tabulce SalesOrderHeader. Tento sloupec je publikován v publikaci AdvWorksSalesOrdersMerge místo nešifrovaného sloupce CreditCardApprovalCode. Pokud je to možné, vyzve uživatele, aby za běhu zadali přihlašovací údaje zabezpečení. Pokud musíte přihlašovací údaje uložit do souboru skriptu, musíte ho zabezpečit, abyste zabránili neoprávněnému přístupu.


-- 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 = '<password>';

-- 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. Vytvoření klíčů v databázi předplatného

Tento příklad znovu vytvoří stejný symetrický klíč v databázi předplatného pomocí stejných hodnot pro ALGORITMUS, KEY_SOURCE a IDENTITY_VALUE z prvního příkladu. Tento příklad předpokládá, že jste již inicializovali odběr publikace AdvWorksSalesOrdersMerge pro replikaci šifrovaného sloupce. Pokud je to možné, vyzve uživatele, aby za běhu zadali přihlašovací údaje zabezpečení. Pokud musíte přihlašovací údaje uložit do souboru skriptu, musíte ho zabezpečit během ukládání a přenosu, abyste zabránili neoprávněnému přístupu.


-- 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 = '<password>';

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

Váš názor

Byla tato stránka užitečná?

Last updated on 2025-03-15