다음을 통해 공유

데이터 열 암호화

  • 아티클

적용 대상: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics

이 문서에서는 Transact-SQL을 사용하여 SQL Server에서 대칭 암호화를 사용하여 데이터 열을 암호화하는 방법에 대해 설명합니다. 열 수준 암호화 또는 셀 수준 암호화라고 합니다.

이 문서의 Transact-SQL 코드 샘플은 AdventureWorks2022 또는 AdventureWorksDW2022 샘플 데이터베이스를 사용하며, 이는 Microsoft SQL Server 예시 및 커뮤니티 프로젝트(Microsoft SQL Server Samples and Community Projects) 홈 페이지에서 다운로드할 수 있습니다.

보안

사용 권한

아래의 단계를 수행하려면 다음 권한이 필요합니다.

  • 데이터베이스에 대한 CONTROL 권한.
  • 데이터베이스에 대한 CREATE CERTIFICATE 권한. Windows 로그인, SQL Server 로그인 및 애플리케이션 역할만 인증서를 소유할 수 있습니다. 그룹 및 역할은 인증서를 소유할 수 없습니다.
  • 테이블에 대한 ALTER 권한.
  • 키에 대한 일부 권한이 있으며 VIEW DEFINITION 권한이 거부되지 않아야 합니다.

데이터베이스 마스터 키 만들기

다음 예를 사용하려면 데이터베이스 마스터 키가 있어야 합니다. 데이터베이스에 데이터베이스 마스터 키가 아직 없는 경우 새로 만듭니다. 생성하려면 데이터베이스에 연결하고 다음 스크립트를 실행합니다. 복잡한 암호를 사용해야 합니다.

다음 예제를 복사하여 AdventureWorks 샘플 데이터베이스에 연결된 쿼리 창에 붙여넣습니다. 실행을 선택합니다.

CREATE MASTER KEY ENCRYPTION BY   
PASSWORD = '<complex password>';

항상 데이터베이스 마스터 키를 백업하세요. 데이터베이스 마스터 키에 대한 자세한 내용은 CREATE MASTER KEY(Transact-SQL)를 참조하세요.

예: 대칭형 암호화 및 인증자로 암호화

  1. 개체 탐색기에서 데이터베이스 엔진인스턴스에 연결합니다.

  2. 표준 도구 모음에서 새 쿼리를 선택합니다.

  3. 다음 예제를 복사하여 AdventureWorks 샘플 데이터베이스에 연결된 쿼리 창에 붙여넣습니다. 실행을 선택합니다.

    CREATE CERTIFICATE Sales09  
   WITH SUBJECT = 'Customer Credit Card Numbers';  
GO  

CREATE SYMMETRIC KEY CreditCards_Key11  
    WITH ALGORITHM = AES_256  
    ENCRYPTION BY CERTIFICATE Sales09;  
GO  

-- Create a column in which to store the encrypted data.  
ALTER TABLE Sales.CreditCard   
    ADD CardNumber_Encrypted varbinary(160);   
GO  

-- Open the symmetric key with which to encrypt the data.  
OPEN SYMMETRIC KEY CreditCards_Key11  
   DECRYPTION BY CERTIFICATE Sales09;  

-- Encrypt the value in column CardNumber using the  
-- symmetric key CreditCards_Key11.  
-- Save the result in column CardNumber_Encrypted.    
UPDATE Sales.CreditCard  
SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11')  
    , CardNumber, 1, HASHBYTES('SHA2_256', CONVERT( varbinary  
    , CreditCardID)));  
GO  

-- Verify the encryption.  
-- First, open the symmetric key with which to decrypt the data.  

OPEN SYMMETRIC KEY CreditCards_Key11  
   DECRYPTION BY CERTIFICATE Sales09;  
GO  

-- Now list the original card number, the encrypted card number,  
-- and the decrypted ciphertext. If the decryption worked,  
-- the original number will match the decrypted number.  

SELECT CardNumber, CardNumber_Encrypted   
    AS 'Encrypted card number', CONVERT(nvarchar,  
    DecryptByKey(CardNumber_Encrypted, 1 ,   
    HASHBYTES('SHA2_256', CONVERT(varbinary, CreditCardID))))  
    AS 'Decrypted card number' FROM Sales.CreditCard;  
GO

단순 대칭 암호화를 사용하여 암호화

  1. 개체 탐색기에서 데이터베이스 엔진인스턴스에 연결합니다.

  2. 표준 도구 모음에서 새 쿼리를 선택합니다.

  3. 다음 예제를 복사하여 AdventureWorks 샘플 데이터베이스에 연결된 쿼리 창에 붙여넣습니다. 실행을 선택합니다.

     CREATE CERTIFICATE HumanResources037  
   WITH SUBJECT = 'Employee Social Security Numbers';  
GO  

CREATE SYMMETRIC KEY SSN_Key_01  
    WITH ALGORITHM = AES_256  
    ENCRYPTION BY CERTIFICATE HumanResources037;  
GO  

USE [AdventureWorks2022];  
GO  

-- Create a column in which to store the encrypted data.  
ALTER TABLE HumanResources.Employee  
    ADD EncryptedNationalIDNumber varbinary(128);   
GO  

-- Open the symmetric key with which to encrypt the data.  
OPEN SYMMETRIC KEY SSN_Key_01  
   DECRYPTION BY CERTIFICATE HumanResources037;  

-- Encrypt the value in column NationalIDNumber with symmetric   
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.  
UPDATE HumanResources.Employee  
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);  
GO  

-- Verify the encryption.  
-- First, open the symmetric key with which to decrypt the data.  
OPEN SYMMETRIC KEY SSN_Key_01  
   DECRYPTION BY CERTIFICATE HumanResources037;  
GO  

-- Now list the original ID, the encrypted ID, and the   
-- decrypted ciphertext. If the decryption worked, the original  
-- and the decrypted ID will match.  
SELECT NationalIDNumber, EncryptedNationalIDNumber   
    AS 'Encrypted ID Number',  
    CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))   
    AS 'Decrypted ID Number'  
    FROM HumanResources.Employee;  
GO

다음 단계