암호화된 열의 데이터 복제(SQL Server Management Studio)Replicate Data in Encrypted Columns (SQL Server Management Studio)

복제를 사용하여 암호화된 열 데이터를 게시할 수 있습니다.Replication enables you to publish encrypted column data. 구독자에서 이 데이터를 해독하고 사용하려면 게시자에서 데이터 암호화에 사용된 키가 구독자에도 있어야 합니다.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. 복제에서는 암호화 키를 전송하는 보안 메커니즘을 제공하지 않습니다.Replication does not provide a secure mechanism to transport encryption keys. 구독자에서 직접 암호화 키를 다시 만들어야 합니다.You must manually re-create the encryption key at the Subscriber. 이 항목에서는 게시자에서 열을 암호화하고 구독자에서 암호화 키를 사용할 수 있게 하는 방법을 보여 줍니다.This topic shows you how to encrypt a column at the Publisher and make sure that the encryption key is available at the Subscriber.

기본 단계는 다음과 같습니다.The basic steps are as follows:

  1. 게시자에서 대칭 키를 만듭니다.Create the symmetric key at the Publisher.

  2. 대칭 키를 사용하여 열 데이터를 암호화합니다.Encrypt column data with the symmetric key.

  3. 암호화된 열이 있는 테이블을 게시합니다.Publish the table with the encrypted column.

  4. 게시를 구독합니다.Subscribe to the publication.

  5. 구독을 초기화합니다.Initialize the subscription.

  6. ALGORITHM, KEY_SOURCE 및 IDENTITY_VALUE에 대해 1단계와 동일한 값을 사용하여 구독자에서 대칭 키를 다시 만듭니다.Recreate the symmetric key at the Subscriber using same values for ALGORITHM, KEY_SOURCE, and IDENTITY_VALUE as in step 1.

  7. 암호화된 열 데이터에 액세스합니다.Access the encrypted column data.

참고

대칭 키를 사용하여 열 데이터를 암호화해야 합니다.You should use a symmetric key to encrypt column data. 대칭 키 자체는 게시자와 구독자에서 다른 방법으로 보안을 설정할 수 있습니다.The symmetric key itself can be secured by different means at the Publisher and Subscriber.

암호화된 열 데이터를 만들고 복제하려면To create and replicate encrypted column data

  1. 게시자에서 CREATE SYMMETRIC KEY를 실행합니다.At the Publisher, execute CREATE SYMMETRIC KEY.

    중요

    KEY_SOURCE 값은 대칭 키를 다시 만들고 데이터를 해독하는 데 사용할 수 있는 중요한 데이터입니다.The value of KEY_SOURCE is valuable data that can be used to re-create the symmetric key and decrypt data. KEY_SOURCE는 항상 안전하게 저장하고 전송해야 합니다.KEY_SOURCE must always be stored and transported securely.

  2. OPEN SYMMETRIC KEY 를 실행하여 새 키를 엽니다.Execute OPEN SYMMETRIC KEY to open the new key.

  3. EncryptByKey 함수를 사용하여 게시자에서 열 데이터를 암호화합니다.Use the EncryptByKey function to encrypt column data at the Publisher.

  4. CLOSE SYMMETRIC KEY 를 실행하여 키를 닫습니다.Execute CLOSE SYMMETRIC KEY to close the key.

  5. 암호화된 열이 있는 테이블을 게시합니다.Publish the table that contains the encrypted column. 자세한 내용은 Create a Publication을 참조하세요.For more information, see Create a Publication.

  6. 게시를 구독합니다.Subscribe to the publication. 자세한 내용은 끌어오기 구독 만들기 또는 밀어넣기 구독 만들기를 참조하세요.For more information, see Create a Pull Subscription or Create a Push Subscription.

  7. 구독을 초기화합니다.Initialize the subscription. 자세한 내용은 Create and Apply the Initial Snapshot을 참조하세요.For more information, see Create and Apply the Initial Snapshot.

  8. ALGORITHM, KEY_SOURCE 및 IDENTITY_VALUE에 대해 1단계와 동일한 값을 사용하여 구독자에서 CREATE SYMMETRIC KEY 를 실행합니다.At the Subscriber, execute CREATE SYMMETRIC KEY using the same values for ALGORITHM, KEY_SOURCE, and IDENTITY_VALUE as in step 1. ENCRYPTION BY에는 다른 값을 지정할 수 있습니다.You can specify a different value for ENCRYPTION BY.

    중요

    KEY_SOURCE 값은 대칭 키를 다시 만들고 데이터를 해독하는 데 사용할 수 있는 중요한 데이터입니다.The value of KEY_SOURCE is valuable data that can be used to re-create the symmetric key and decrypt data. KEY_SOURCE는 항상 안전하게 저장하고 전송해야 합니다.KEY_SOURCE must always be stored and transported securely.

  9. OPEN SYMMETRIC KEY 를 실행하여 새 키를 엽니다.Execute OPEN SYMMETRIC KEY to open the new key.

  10. 구독자에서 DecryptByKey 함수를 사용하여 복제된 데이터를 해독합니다.Use the DecryptByKey function to decrypt replicated data at the Subscriber.

  11. CLOSE SYMMETRIC KEY 를 실행하여 키를 닫습니다.Execute CLOSE SYMMETRIC KEY to close the key.

예제Example

이 예에서는 대칭 키, 대칭 키의 보안 설정에 사용되는 인증서 및 마스터 키를 만듭니다.This example creates a symmetric key, a certificate that is used to help secure the symmetric key, and a master key. 이러한 키는 게시 데이터베이스에서 생성됩니다.These keys are created in the publication database. 그런 다음 SalesOrderHeader 테이블에서 암호화된 열(EncryptedCreditCardApprovalCode)을 만드는 데 사용됩니다.They are then used to create an encrypted column (EncryptedCreditCardApprovalCode) in the SalesOrderHeader table. 이 열은 암호화되지 않은 CreditCardApprovalCode 열 대신 AdvWorksSalesOrdersMerge 게시에 게시됩니다.This column is published in the AdvWorksSalesOrdersMerge publication instead of the unencrypted CreditCardApprovalCode column. 가능한 경우 런타임 시 사용자에게 보안 자격 증명을 입력하라는 메시지가 표시됩니다.When possible, prompt users to enter security credentials at runtime. 자격 증명을 스크립트 파일에 저장해야 하는 경우에는 파일에 무단으로 액세스하지 못하도록 보안을 설정해야 합니다.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

예제Example

이 예에서는 첫 번째 예의 ALGORITHM, KEY_SOURCE 및 IDENTITY_VALUE에 동일한 값을 사용하여 구독 데이터베이스에서 같은 대칭 키를 다시 만듭니다.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. 암호화된 열을 복제하기 위해 AdvWorksSalesOrdersMerge 게시에 대한 구독을 이미 초기화했다고 가정합니다.This example assumes that you have already initialized a subscription to the AdvWorksSalesOrdersMerge publication to replicate the encrypted column. 가능한 경우 런타임 시 사용자에게 보안 자격 증명을 입력하라는 메시지가 표시됩니다.When possible, prompt users to enter security credentials at runtime. 스크립트 파일에 자격 증명을 저장해야 하는 경우에는 저장 및 전송 중에 무단으로 액세스하지 못하도록 파일에 보안을 설정해야 합니다.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

관련 항목:See Also

보안 개요(복제) Security Overview (Replication)
두 서버에서 동일한 대칭 키 만들기Create Identical Symmetric Keys on Two Servers