암호화된 열에서 데이터 복제(SQL Server Management Studio)
적용 대상:
SQL ServerAzure SQL Managed Instance
복제를 사용하면 암호화된 열 데이터를 게시할 수 있습니다. 구독자에서 이 데이터를 해독하고 사용하려면 게시자에서 데이터를 암호화하는 데 사용된 키도 구독자에 있어야 합니다. 복제는 암호화 키를 전송하는 보안 메커니즘을 제공하지 않습니다. 구독자에서 암호화 키를 수동으로 다시 만들어야 합니다. 이 항목에서는 게시자에서 열을 암호화하고 구독자에서 암호화 키를 사용할 수 있는지 확인하는 방법을 보여줍니다.
기본 단계는 다음과 같습니다.
게시자에서 대칭 키를 만듭니다.
대칭 키를 사용하여 열 데이터를 암호화합니다.
암호화된 열이 있는 테이블을 게시합니다.
게시를 구독합니다.
구독을 초기화합니다.
ALGORITHM, KEY_SOURCE 및 IDENTITY_VALUE에 대해 1단계와 동일한 값을 사용하여 구독자에서 대칭 키를 다시 만듭니다.
암호화된 열 데이터에 액세스합니다.
참고 항목
대칭 키를 사용하여 열 데이터를 암호화해야 합니다. 대칭 키 자체는 게시자와 구독자에서 다른 방법으로 보안을 설정할 수 있습니다.
암호화된 열 데이터를 만들고 복제본(replica)
게시자에서 CREATE SYMMETRIC KEY를 실행 합니다.
Important
KEY_SOURCE 값은 대칭 키를 다시 만들고 데이터를 해독하는 데 사용할 수 있는 중요한 데이터입니다. KEY_SOURCE 항상 안전하게 저장하고 전송해야 합니다.
OPEN SYMMETRIC KEY를 실행하여 새 키를 엽니다.
EncryptByKey 함수를 사용하여 게시자에서 열 데이터를 암호화합니다.
CLOSE SYMMETRIC KEY를 실행하여 키를 닫습니다.
암호화된 열이 포함된 테이블을 게시합니다. 자세한 내용은 게시 만들기를 참조하세요.
게시를 구독합니다. 자세한 내용은 끌어오기 구독 만들기 또는 밀어넣기 구독 만들기를 참조하세요.
구독을 초기화합니다. 자세한 내용은 초기 스냅샷 만들기 및 적용을 참조하세요.
ALGORITHM, KEY_SOURCE 및 IDENTITY_VALUE에 대해 1단계와 동일한 값을 사용하여 구독자에서 CREATE SYMMETRIC KEY 를 실행합니다. ENCRYPTION BY에는 다른 값을 지정할 수 있습니다.
Important
KEY_SOURCE 값은 대칭 키를 다시 만들고 데이터를 해독하는 데 사용할 수 있는 중요한 데이터입니다. KEY_SOURCE 항상 안전하게 저장하고 전송해야 합니다.
OPEN SYMMETRIC KEY를 실행하여 새 키를 엽니다.
구독자에서 DecryptByKey 함수를 사용하여 복제된 데이터를 해독합니다.
CLOSE SYMMETRIC KEY를 실행하여 키를 닫습니다.
예제
A. 게시 데이터베이스에서 키 만들기
이 예제에서는 대칭 키, 대칭 키를 보호하는 데 사용되는 인증서 및 마스터 키를 만듭니다. 이러한 키는 게시 데이터베이스에 만들어집니다. 그런 다음, 테이블에 암호화된 열(EncryptedCreditCardApprovalCode)을 SalesOrderHeader 만드는 데 사용됩니다. 이 열은 암호화되지 않은 CreditCardApprovalCode 열 대신 AdvWorksSalesOrdersMerge 게시에 게시됩니다. 가능한 경우 런타임 시 사용자에게 보안 자격 증명을 입력하라는 메시지가 표시됩니다. 스크립트 파일에 자격 증명을 저장해야 하는 경우 무단 액세스를 방지하기 위해 파일을 보호해야 합니다.
-- 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. 구독 데이터베이스에서 키 만들기
이 예에서는 첫 번째 예의 ALGORITHM, KEY_SOURCE 및 IDENTITY_VALUE에 동일한 값을 사용하여 구독 데이터베이스에서 같은 대칭 키를 다시 만듭니다. 이 예제에서는 암호화된 열을 복제본(replica) 위해 AdvWorksSalesOrdersMerge 게시에 대한 구독을 이미 초기화했다고 가정합니다. 가능한 경우 런타임 시 사용자에게 보안 자격 증명을 입력하라는 메시지가 표시됩니다. 스크립트 파일에 자격 증명을 저장해야 하는 경우에는 스토리지 및 전송 중에 무단으로 액세스하지 못하도록 파일에 보안을 설정해야 합니다.
-- 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
참고 항목
피드백
출시 예정: 2024년 내내 콘텐츠에 대한 피드백 메커니즘으로 GitHub 문제를 단계적으로 폐지하고 이를 새로운 피드백 시스템으로 바꿀 예정입니다. 자세한 내용은 다음을 참조하세요. https://aka.ms/ContentUserFeedback
다음에 대한 사용자 의견 제출 및 보기