데이터 열 암호화

적용 대상:SQL 서버Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics분석 플랫폼 시스템(PDW)Microsoft Fabric의 SQL 데이터베이스

적용 대상:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics

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

이 문서의 코드 샘플은 AdventureWorks2025 또는 AdventureWorksDW2025 샘플 데이터베이스를 사용합니다. 이 데이터베이스는 Microsoft SQL Server 샘플 및 커뮤니티 프로젝트 홈페이지에서 다운로드할 수 있습니다.

Security

Permissions

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

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

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

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

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

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

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

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

  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

다음 단계

  • Last updated on