다음을 통해 공유

암호화된 열에서 데이터 복제(SQL Server Management Studio)

복제를 사용하면 암호화된 열 데이터를 게시할 수 있습니다. 구독자에서 이 데이터를 해독하고 사용하려면 게시자에서 데이터를 암호화하는 데 사용된 키도 구독자에 있어야 합니다. 복제는 암호화 키를 전송하는 보안 메커니즘을 제공하지 않습니다. 구독자에서 암호화 키를 수동으로 다시 만들어야 합니다. 이 항목에서는 게시자에서 열을 암호화하고 구독자에서 암호화 키를 사용할 수 있는지 확인하는 방법을 보여줍니다.

기본 단계는 다음과 같습니다.

  1. 게시자에서 대칭 키를 만듭니다.

  2. 대칭 키를 사용하여 열 데이터를 암호화합니다.

  3. 암호화된 열을 사용하여 테이블을 게시합니다.

  4. 간행물을 구독합니다.

  5. 구독을 초기화합니다.

  6. 1단계와 마찬가지로 ALGORITHM, KEY_SOURCE 및 IDENTITY_VALUE 동일한 값을 사용하여 구독자에서 대칭 키를 다시 만듭니다.

  7. 암호화된 열 데이터에 액세스합니다.

비고

대칭 키를 사용하여 열 데이터를 암호화해야 합니다. 대칭 키 자체는 게시자와 구독자에서 서로 다른 방법을 통해 보호될 수 있습니다.

암호화된 열 데이터를 만들고 복제하려면

  1. 게시자에서 CREATE SYMMETRIC KEY를 실행합니다.

    중요합니다

    KEY_SOURCE 값은 대칭 키를 다시 만들고 데이터의 암호를 해독하는 데 사용할 수 있는 중요한 데이터입니다. KEY_SOURCE 항상 안전하게 저장하고 전송해야 합니다.

  2. OPEN SYMMETRIC KEY를 실행하여 새 키를 엽니다.

  3. EncryptByKey 함수를 사용하여 게시자에서 열 데이터를 암호화합니다.

  4. CLOSE SYMMETRIC KEY를 실행하여 키를 닫습니다.

  5. 암호화된 열이 포함된 테이블을 게시합니다. 자세한 내용은 게시 만들기를 참조하세요.

  6. 출판물을 구독하세요. 자세한 내용은 끌어오기 구독 만들기 또는 밀어넣기 구독 만들기를 참조하세요.

  7. 구독을 초기화합니다. 자세한 내용은 초기 스냅샷 만들기 및 적용을 참조하세요.

  8. 구독자에서 CREATE SYMMETRIC KEY를 실행하되, 1단계에서처럼 ALGORITHM, KEY_SOURCE 및 IDENTITY_VALUE와 동일한 값을 사용합니다. ENCRYPTION BY에 대해 다른 값을 지정할 수 있습니다.

    중요합니다

    KEY_SOURCE 값은 대칭 키를 다시 만들고 데이터의 암호를 해독하는 데 사용할 수 있는 중요한 데이터입니다. KEY_SOURCE 항상 안전하게 저장하고 전송해야 합니다.

  9. OPEN SYMMETRIC KEY를 실행하여 새 키를 엽니다.

  10. DecryptByKey 함수를 사용하여 구독자에서 복제된 데이터의 암호를 해독합니다.

  11. CLOSE SYMMETRIC KEY를 실행하여 키를 닫습니다.

예시

이 예제에서는 대칭 키, 대칭 키를 보호하는 데 사용되는 인증서 및 마스터 키를 만듭니다. 이러한 키는 게시 데이터베이스에 만들어집니다. 그런 다음, 이러한 데이터는 SalesOrderHeader 테이블에 암호화된 열(EncryptedCreditCardApprovalCode)을 만드는 데 사용됩니다. 이 열은 암호화되지 않은 CreditCardApprovalCode 열 대신 AdvWorksSalesOrdersMerge 게시에 게시됩니다. 가능한 경우 런타임 시 사용자에게 보안 자격 증명을 입력하라는 메시지가 표시됩니다. 스크립트 파일에 자격 증명을 저장해야 하는 경우 무단 액세스를 방지하기 위해 파일을 보호해야 합니다.


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

예시

이 예제에서는 첫 번째 예제에서 ALGORITHM, KEY_SOURCE 및 IDENTITY_VALUE 동일한 값을 사용하여 구독 데이터베이스에서 동일한 대칭 키를 다시 만듭니다. 이 예제에서는 암호화된 열을 복제하기 위해 AdvWorksSalesOrdersMerge 게시에 대한 구독을 이미 초기화했다고 가정합니다. 가능한 경우 런타임 시 사용자에게 보안 자격 증명을 입력하라는 메시지가 표시됩니다. 스크립트 파일에 자격 증명을 저장해야 하는 경우 무단 액세스를 방지하기 위해 스토리지 및 전송 중에 파일을 보호해야 합니다.


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

또한 참조하십시오

SQL Server 복제 보안
두 서버에서 동일한 대칭 키 만들기

  • Last updated on