Mereplikasi Data di Kolom Terenkripsi (SQL Server Management Studio)

Berlaku untuk: SQL Server Azure SQL Managed Instance

Replikasi memungkinkan Anda menerbitkan data kolom terenkripsi. Untuk mendekripsi dan menggunakan data ini di Pelanggan, kunci yang digunakan untuk mengenkripsi data di Penerbit juga harus ada di Pelanggan. Replikasi tidak menyediakan mekanisme aman untuk mengangkut kunci enkripsi. Anda harus membuat ulang kunci enkripsi secara manual di Pelanggan. Topik ini memperlihatkan kepada Anda cara mengenkripsi kolom di Publisher dan memastikan bahwa kunci enkripsi tersedia di Pelanggan.

Langkah-langkah dasarnya adalah sebagai berikut:

Buat kunci simetris pada Publisher.
Kriptokan data kolom dengan kunci simetris.
Terbitkan tabel dengan kolom terenkripsi.
Berlangganan publikasi tersebut.
Menginisialisasi langganan.
Buat ulang kunci simetris pada Pelanggan menggunakan nilai yang sama untuk ALGORITHM, KEY_SOURCE, dan IDENTITY_VALUE seperti pada langkah 1.
Akses data kolom terenkripsi.

Catatan

Anda harus menggunakan kunci simetris untuk mengenkripsi data kolom. Kunci simetris itu sendiri dapat diamankan dengan cara yang berbeda di Penerbit dan Pengguna.

Untuk membuat dan mereplikasi data kolom terenkripsi

Di Publisher, jalankan CREATE SYMMETRIC KEY.

Penting

Nilai KEY_SOURCE adalah data berharga yang dapat digunakan untuk menghasilkan kembali kunci simetris dan mendekripsi data. KEY_SOURCE harus selalu disimpan dan diangkut dengan aman.
Jalankan OPEN SYMMETRIC KEY untuk membuka kunci baru.
Gunakan fungsi EncryptByKey untuk mengenkripsi data kolom di Publisher.
Jalankan CLOSE SYMMETRIC KEY untuk menutup kunci.
Terbitkan tabel yang berisi kolom terenkripsi. Untuk informasi selengkapnya, lihat Membuat Publikasi.
Langganan penerbitan tersebut. Untuk informasi selengkapnya, lihat Membuat Langganan Pull atau Membuat Langganan Push.
Menginisialisasi langganan. Untuk informasi selengkapnya, lihat Membuat dan Menerapkan Rekam Jepret Awal.
Pada pihak berlangganan, jalankan CREATE SYMMETRIC KEY menggunakan nilai yang sama untuk ALGORITHM, KEY_SOURCE, dan IDENTITY_VALUE seperti pada langkah 1. Anda dapat menentukan nilai yang berbeda untuk ENCRYPTION BY.

Penting

Nilai KEY_SOURCE adalah data berharga yang dapat digunakan untuk membuat ulang kunci simetris dan mendekripsi data. KEY_SOURCE harus selalu disimpan dan diangkut dengan aman.
Jalankan OPEN SYMMETRIC KEY untuk membuka kunci baru.
Gunakan fungsi DecryptByKey untuk mendekripsi data yang direplikasi di Pengguna langganan.
Jalankan CLOSE SYMMETRIC KEY untuk menutup kunci.

Contoh

A. Membuat kunci di database publikasi

Contoh ini membuat kunci simetris, sertifikat yang digunakan untuk membantu mengamankan kunci simetris, dan kunci master. Kunci ini dibuat dalam database publikasi. Mereka kemudian digunakan untuk membuat kolom terenkripsi (EncryptedCreditCardApprovalCode) di tabel SalesOrderHeader. Kolom ini diterbitkan dalam publikasi AdvWorksSalesOrdersMerge alih-alih kolom CreditCardApprovalCode yang tidak terenkripsi. Jika memungkinkan, minta pengguna untuk memasukkan kredensial keamanan saat runtime. Jika Anda harus menyimpan kredensial dalam file skrip, Anda harus mengamankan file untuk mencegah akses yang tidak sah.


-- 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. Membuat kunci di database langganan

Contoh ini membuat ulang kunci simetris yang sama dalam database subskripsi menggunakan nilai yang sama untuk ALGORITHM, KEY_SOURCE, dan IDENTITY_VALUE dari contoh pertama. Contoh ini mengasumsikan bahwa Anda telah menginisialisasi langganan ke publikasi AdvWorksSalesOrdersMerge untuk mereplikasi kolom terenkripsi. Jika memungkinkan, minta pengguna untuk memasukkan kredensial keamanan saat runtime. Jika Anda harus menyimpan kredensial dalam file skrip, Anda harus mengamankan file selama penyimpanan dan transportasi untuk mencegah akses yang tidak sah.


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

Saran dan Komentar

Apakah halaman ini membantu?

Last updated on 2025-03-23