자습서: Always Encrypted 시작하기

적용 대상: SQL Server Azure SQL 데이터베이스 Azure SQL Managed Instance

이 자습서는 Always Encrypted 시작 방법을 설명합니다. 다음이 설명됩니다.

• 데이터베이스에서 선택한 열을 암호화하는 방법입니다.
• 암호화된 열을 쿼리하는 방법입니다.

참고 항목

보안 Enclave를 사용한 Always Encrypted에 대한 정보를 찾고 있는 경우 다음 자습서를 대신 참조하세요.

• 보안 Enclave를 사용하여 Always Encrypted 시작하기
• 자습서: SQL Server의 보안 enclave를 사용하여 Always Encrypted 시작하기

필수 조건

이 자습서에서는 다음이 필요합니다.

• Azure SQL Database, Azure SQL Managed Instance, 또는 SQL Server 내 빈 데이터베이스. 아래 지침에서는 데이터베이스 이름이 ContosoHR이라고 가정합니다. 데이터베이스의 소유자여야 합니다(db_owner 역할의 멤버). 데이터베이스를 만드는 방법에 대한 자세한 내용은 빠른 시작: 단일 데이터베이스 만들기 - Azure SQL Database 또는 SQL Server에서 데이터베이스 만들기를 참조하세요.
• 선택 사항이지만, 특히 데이터베이스가 Azure에 있는 경우, Azure Key Vault의 키 자격 증명 모음을 사용하는 것이 좋습니다. 키 자격 증명 모음을 만드는 방법에 대한 정보는 빠른 시작: Azure Portal을 사용하여 키 자격 증명 모음 만들기를 참조하세요.
  • 키 자격 증명 모음에서 액세스 정책 권한 모델을 사용하는 경우 키 자격 증명 모음에 get, list, create, unwrap key, wrap key, verify, sign 등의 주요 권한이 있는지 확인합니다. Key Vault 액세스 정책 할당을 참조하세요.
  • Azure RBAC(역할 기반 액세스 제어) 권한 모델을 사용하는 경우 키 자격 증명 모음에 대한 Key Vault Crypto Officer 역할의 멤버인지 확인합니다. Azure 역할 기반 액세스 제어를 사용하여 Key Vault 키, 인증서 및 비밀에 대한 액세스 제공을 참조하세요.
• 최신 버전의 SSMS(SQL Server Management Studio) 또는 최신 버전의 SqlServer 및 Az PowerShell 모듈입니다. Az PowerShell 모듈은 Azure Key Vault를 사용하는 경우에만 필요합니다.

1단계: 데이터베이스 스키마 만들기 및 채우기

이 단계에서는 HR 스키마 및 직원 테이블을 만듭니다. 그런 다음, 테이블을 일부 데이터로 채웁니다.

SSMS

1. 데이터베이스에 연결합니다. SSMS에서 데이터베이스에 연결하는 방법에 대한 지침은 빠른 시작: SSMS(SQL Server Management Studio)를 사용하여 Azure SQL 데이터베이스 또는 Azure SQL 관리형 인스턴스 연결 및 쿼리하기 또는 빠른 시작: SSMS(SQL Server 관리 스튜디오)를 사용하여 SQL Server 인스턴스 연결 및 쿼리하기를 참조하세요.

2. ContosoHR데이터베이스에 대한 새 쿼리 창을 엽니다.

3. 아래 문을 붙여넣고 실행하여 직원이라는 새 테이블을 만듭니다.

   CREATE SCHEMA [HR];
   GO
   
   CREATE TABLE [HR].[Employees]
   (
       [EmployeeID] [int] IDENTITY(1,1) NOT NULL
       , [SSN] [char](11) NOT NULL
       , [FirstName] [nvarchar](50) NOT NULL
       , [LastName] [nvarchar](50) NOT NULL
       , [Salary] [money] NOT NULL
   ) ON [PRIMARY];
   

4. 아래 문을 붙여넣고 실행하여 직원 테이블에 직원 기록 몇 개를 추가합니다.

   INSERT INTO [HR].[Employees]
   (
       [SSN]
       , [FirstName]
       , [LastName]
       , [Salary]
   )
   VALUES
   (
       '795-73-9838'
       , N'Catherine'
       , N'Abel'
       , $31692
   );
   
   INSERT INTO [HR].[Employees]
   (
       [SSN]
       , [FirstName]
       , [LastName]
       , [Salary]
   )
   VALUES
   (
       '990-00-6818'
       , N'Kim'
       , N'Abercrombie'
       , $55415
   );
   

PowerShell

PowerShell세션창에서 다음 명령을 실행합니다. 서버 주소와 데이터베이스에 유효한 인증 설정으로 연결 문자열 업데이트해야 합니다.

Import-Module "SqlServer"

# Set your database connection string
$connectionString = "Server = myServerAddress; Database = ContosoHR; ..."

# Create a new table, named Employees.
$query = @'
    CREATE SCHEMA [HR];
    GO
    
    CREATE TABLE [HR].[Employees]
    (
        [EmployeeID] [int] IDENTITY(1,1) NOT NULL
        , [SSN] [char](11) NOT NULL
        , [FirstName] [nvarchar](50) NOT NULL
        , [LastName] [nvarchar](50) NOT NULL
        , [Salary] [money] NOT NULL
    ) ON [PRIMARY];
'@
Invoke-SqlCmd -ConnectionString $connectionString -Query $query

# Add a few rows to the Employees table.
$query = @'
    INSERT INTO [HR].[Employees]
    (
        [SSN]
        , [FirstName]
        , [LastName]
        , [Salary]
    )
    VALUES
    (
        '795-73-9838'
        , N'Catherine'
        , N'Abel'
        , $31692
    );

    INSERT INTO [HR].[Employees]
    (
        [SSN]
        , [FirstName]
        , [LastName]
        , [Salary]
    )
    VALUES
    (
        '990-00-6818'
        , N'Kim'
        , N'Abercrombie'
        , $55415
    );
'@
Invoke-SqlCmd -ConnectionString $connectionString -Query $query

2단계: 열 암호화

이 단계에서는 Always Encrypted에 대한 열 마스터 키와 열 암호화 키를 프로비저닝합니다. 그런 다음 직원 테이블의 SSN 및 급여 열을 암호화합니다.

SSMS

SSMS는 열 마스터 키, 열 암호화 키를 설정하고 선택한 열을 암호화하여 Always Encrypted를 쉽게 구성할 수 있는 마법사를 제공합니다.

1. 개체 탐색기에서 Databases>ContosoHR>Tables를 확장합니다.

2. 직원 테이블을 마우스 오른쪽 단추로 클릭하고 열 암호화를 선택하여 상시 암호화 마법사를 엽니다.

   

3. 마법사의 소개 페이지에서 다음을 선택합니다.

4. 열 선택 페이지에서 입니다.

   1. SSN 및 급여 열을 선택합니다. SSN 열에는 결정적 암호화를 선택하고 급여 열에는 무작위 암호화를 선택합니다. 결정적 암호화는 암호화된 열에 대한 같음 비교를 포함하는 지점 조회 검색과 같은 쿼리를 지원합니다. 임의 암호화는 암호화된 열에 대한 계산을 지원하지 않습니다.
   2. CEK-Auto1(새로 만들기)을 두 열의 열 암호화 키로 그대로 둡니다. 이 키는 아직 존재하지 않으며 마법사에서 생성됩니다.
   3. 다음을 선택합니다.

   

5. 마스터 키 구성 페이지에서 마법사에서 생성할 새 열 마스터 키를 구성합니다. 먼저 열 마스터 키를 저장할 위치를 선택해야 합니다. 마법사는 다음 두 가지 키 저장소 유형을 지원합니다.

   • Azure Key Vault - 데이터베이스가 Azure에 있는 경우 권장됩니다.
   • Windows 인증서 저장소

   일반적으로 Azure Key Vault는 특히 데이터베이스가 Azure에 있는 경우 권장되는 옵션입니다.

   • Azure 키 자격 증명 모음 사용 방법입니다.

     1. Azure Key Vault를 선택합니다.
     2. 로그인을 선택하고 Azure에 로그인합니다.
     3. 로그인한 후에는 액세스 권한이 있는 구독 및 키 자격 증명 모음 목록이 페이지에 표시됩니다. 사용할 키 자격 증명 모음을 포함하는 Azure 구독을 선택합니다.
     4. 키 자격 증명 모음을 선택합니다.
     5. 다음을 선택합니다.

     

   • Windows 인증서 저장소 사용 방법입니다.

     1. Windows 인증서 저장소를 선택합니다.

     2. 현재 사용자의 기본 선택 항목을 그대로 둡니다. 그러면 마법사에서 현재 사용자 저장소에 인증서(새 열 마스터 키)를 생성하도록 지시합니다.

        

     3. 다음을 선택합니다.

6. 현재 위치 암호화 설정 페이지에서 데이터베이스에 enclave를 사용하도록 설정하지 않았으므로 추가 구성이 필요하지 않습니다. 다음을 선택합니다.

7. 실행 설정 페이지에서 암호화를 진행하거나 나중에 실행할 PowerShell 스크립트를 생성할지 묻는 메시지가 표시됩니다. 기본 설정을 그대로 두고 다음을 선택합니다.

8. 요약 페이지에서 마법사는 실행할 작업에 대해 알려줍니다. 모든 정보가 올바른지 확인하고 마침을 선택합니다.

9. 결과 페이지에서 마법사 작업의 진행률을 모니터링할 수 있습니다. 모든 작업이 성공적으로 완료될 때까지 기다렸다가 닫기를 선택합니다.

   

10. (선택 사항) 마법사가 데이터베이스에서 변경한 내용을 탐색합니다.

    1. ContosoHR>Security>Always Encrypted Keys를 확장하여 마법사에서 만든 열 마스터 키 및 열 암호화에 대한 메타데이터 개체를 탐색합니다.

    2. 키 메타데이터를 포함하는 시스템 카탈로그 뷰에 대해 아래 쿼리를 실행할 수도 있습니다.

       SELECT * FROM sys.column_master_keys;
       SELECT * FROM sys.column_encryption_keys
       SELECT * FROM sys.column_encryption_key_values
       

    3. 개체 탐색기에서 직원 테이블을 마우스 오른쪽 단추로 클릭하고 스크립트 테이블>을 >새 쿼리 편집기 창으로 만들기를 선택합니다. 그러면 직원 테이블에 대한 CREATE TABLE 문이 포함된 새 쿼리 창이 열립니다. SSN 및 급여 열의 정의에 나타나는 ENCRYPTED WITH 절에 유의하세요.

    4. sys.columns에 대해 아래 쿼리를 실행하여 암호화된 두 열에 대한 열 수준 암호화 메타데이터를 검색할 수도 있습니다.

       SELECT
       [name]
       , [encryption_type]
       , [encryption_type_desc]
       , [encryption_algorithm_name]
       , [column_encryption_key_id]
       FROM sys.columns
       WHERE [encryption_type] IS NOT NULL;
       

PowerShell

1. 키 저장소에 열 마스터 키를 만듭니다.

   • Azure Key Vault를 사용하는 경우 아래 명령을 실행하여 키 자격 증명 모음에 비대칭 키를 만듭니다. 구독의 올바른 ID, 키 자격 증명 모음을 포함하는 리소스 그룹의 이름 및 키 자격 증명 모음 이름을 제공해야 합니다.

     Import-Module "Az"
     Connect-AzAccount
     $subscriptionId = "<your Azure subscription ID"
     $resourceGroup = "your resource group name containing your key vault"
     $keyVaultName = "your vault name"
     $keyVaultKeyName = "your key name"
     
     # Switch to your subscription.
     Set-AzConteXt -SubscriptionId $subscriptionId
     
     # To validate the above key vault settings, get the key vault properties.
     Get-AzKeyVault -VaultName $keyVaultName -ResourceGroupName $resourceGroup 
     
     # Create a key in the key vault.
     $keyVaultKey = Add-AzKeyVaultKey -VaultName $keyVaultName -Name $keyVaultKeyName -Destination "Software"
     $keyVaultKey
     

   • Windows 인증서 저장소를 사용하는 경우 아래 명령을 실행하여 현재 사용자 저장소에 인증서를 만듭니다.

     $cert = New-SelfSignedCertificate -Subject "HRCMK" -CertStoreLocation Cert:CurrentUser\My -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage DataEncipherment -KeySpec KeyExchange
     

2. SqlServer PowerShell 모듈을 사용하여 데이터베이스에 연결합니다. 데이터베이스에 유효한 연결 문자열 제공해야 합니다.

   $database = Get-SqlDatabase -ConnectionString $connectionString
   $database
   

3. 데이터베이스에서 열 마스터 키 메타데이터 개체(키 저장소에서 만든 실제 열 마스터 키를 참조)를 프로비전합니다.

   • Azure Key Vault를 사용하는 경우 아래 명령을 실행합니다.

     # Sign in to Azure for the SqlServer PowerShell module
     Add-SqlAzureAuthenticationContext -Interactive
     
     # Create a SqlColumnMasterKeySettings in-memory object referencing the key you've created in your key vault. 
     $cmkSettings = New-SqlAzureKeyVaultColumnMasterKeySettings -KeyURL $keyVaultKey.Key.Kid
     
     # Create column master key metadata object (referencing your certificate), named CMK1, in the database.
     $cmkName = "CMK1"
     New-SqlColumnMasterKey -Name $cmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings
     

   • Windows 인증서 저장소를 사용하는 경우 아래 명령을 실행합니다.

     # Create a SqlColumnMasterKeySettings in-memory object referencing your certificate.
     $cmkSettings = New-SqlCertificateStoreColumnMasterKeySettings -CertificateStoreLocation "CurrentUser" -Thumbprint $cert.Thumbprint
     
     # Create column master key metadata object, named CMK1, in the database.
     $cmkName = "CMK1"
     New-SqlColumnMasterKey -Name $cmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings
     

4. 열 암호화 키를 생성하고, 생성한 열 마스터 키로 암호화한 다음 데이터베이스에 열 암호화 키 메타데이터 개체를 만듭니다.

   $cekName = "CEK1"
   New-SqlColumnEncryptionKey -Name $cekName -InputObject $database -ColumnMasterKey $cmkName
   

5. 직원 테이블의 SSN 및 급여 열을 암호화합니다. SSN 열에는 결정적 암호화를 선택하고 급여 열에는 무작위 암호화를 선택합니다. 결정적 암호화는 암호화된 열에 대한 같음 비교를 포함하는 지점 조회 검색과 같은 쿼리를 지원합니다. 임의 암호화는 암호화된 열에 대한 계산을 지원하지 않습니다.

   # Encrypt the SSN and Salary columns 
   $ces = @()
   $ces += New-SqlColumnEncryptionSettings -ColumnName "HR.Employees.SSN" -EncryptionType "Deterministic" -EncryptionKey $cekName
   $ces += New-SqlColumnEncryptionSettings -ColumnName "HR.Employees.Salary" -EncryptionType "Randomized" -EncryptionKey $cekName
   Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces -LogFileDirectory .
   
   

6. (선택 사항) 데이터베이스에서 변경한 내용을 탐색합니다.

   • 아래 명령을 실행하여 사용자가 만든 열 마스터 키 및 열 암호화 키에 대한 메타데이터가 포함된 시스템 카탈로그 뷰를 쿼리합니다.

     $query = @'
     SELECT * FROM sys.column_master_keys;
     SELECT * FROM sys.column_encryption_keys
     SELECT * FROM sys.column_encryption_key_values
     '@
     
     Invoke-SqlCmd -ConnectionString $connectionString -Query $query
     

   • 아래 명령을 실행하여 sys.columns를 쿼리하여 암호화된 두 열에 대한 열 수준 암호화 메타데이터를 검색합니다.

     $query = @'
     SELECT
     [name]
     , [encryption_type]
     , [encryption_type_desc]
     , [encryption_algorithm_name]
     , [column_encryption_key_id]
     FROM sys.columns
     WHERE [encryption_type] IS NOT NULL;
     '@
     
     Invoke-SqlCmd -ConnectionString $connectionString -Query $query
     

3단계: 암호화된 열 쿼리

SSMS

1. 연결에 대해 Always Encrypted를 사용하지 않도록 설정하여 데이터베이스에 연결합니다.

   1. 새 쿼리 창을 엽니다.
   2. 쿼리 창의 아무 곳이나 마우스 오른쪽 단추로 클릭하고 연결>연결 변경을 선택합니다. 그러면 데이터베이스 엔진 연결 대화 상자가 열립니다.
   3. 옵션<<을 선택합니다. 데이터베이스 엔진 연결 대화 상자에 추가 탭이 보입니다.
   4. Always Encrypted 탭을 선택합니다.
   5. Always Encrypted 사용(열 암호화) 확인란이 선택되어 있지 않은지 확인합니다.
   6. 연결을 선택합니다.

   

2. 다음 쿼리를 붙여넣고 실행합니다. 쿼리는 이진 암호화된 데이터를 반환해야 합니다.

   SELECT [SSN], [Salary] FROM [HR].[Employees]
   

   

3. 연결에서 Always Encrypted를 사용하도록 설정된 데이터베이스에 연결합니다.

   1. 쿼리 창의 아무 곳이나 마우스 오른쪽 단추로 클릭하고 연결>연결 변경을 선택합니다. 그러면 데이터베이스 엔진 연결 대화 상자가 열립니다.
   2. 옵션<<을 선택합니다. 데이터베이스 엔진 연결 대화 상자에 추가 탭이 보입니다.
   3. Always Encrypted 탭을 선택합니다.
   4. Always Encrypted(열 암호화) 사용을 선택합니다.
   5. 연결을 선택합니다.

   

4. 동일한 쿼리를 다시 실행합니다. 데이터베이스 연결에 대해 Always Encrypted를 사용하도록 설정된 상태로 연결되었으므로 SSMS의 클라이언트 드라이버는 두 암호화된 열에 저장된 데이터의 암호를 해독하려고 시도합니다. Azure Key Vault를 사용하는 경우 Azure에 로그인하라는 메시지가 표시될 수 있습니다.

   

5. Always Encrypted에 대한 매개 변수화를 사용합니다. 이 기능을 사용하면 암호화된 열로 데이터를 필터링하는 쿼리를 실행하거나 암호화된 열에 데이터를 삽입할 수 있습니다.

   1. SSMS의 주 메뉴에서 쿼리리 를 선택합니다.
   2. 쿼리 옵션...을 선택합니다.
   3. 실행>고급으로 이동합니다.
   4. Always Encrypted에 대해 매개 변수화 사용이 선택되었는지 확인합니다.
   5. 확인을 선택합니다.

   

6. 암호화된 SSN 열로 데이터를 필터링하는 아래 쿼리를 붙여넣고 실행합니다. 쿼리는 일반 텍스트 값이 포함된 한 행을 반환해야 합니다.

   DECLARE @SSN [char](11) = '795-73-9838'
   SELECT [SSN], [Salary] FROM [HR].[Employees]
   WHERE [SSN] = @SSN
   

7. 선택적으로 액세스 정책 권한 모델로 구성된 Azure Key Vault를 사용하는 경우 아래 단계에 따라 사용자가 데이터를 보호하는 열 마스터 키에 액세스하지 않고 암호화된 열에서 일반 텍스트 데이터를 검색하려고 하면 어떻게 되는지 확인합니다.

   1. 키 unwrap 자격 증명 모음에 대한 액세스 정책에서 직접 키 권한을 제거합니다. 자세한 내용은 Key Vault 액세스 정책 할당을 참조하세요.
   2. SSMS의 클라이언트 드라이버는 키 자격 증명 모음에서 가져온 열 암호화 키를 2시간 동안 캐시하므로 SSMS를 닫고 다시 엽니다. 이렇게 하면 키 캐시가 비어 있습니다.
   3. 연결에서 Always Encrypted를 사용하도록 설정된 데이터베이스에 연결합니다.
   4. 다음 쿼리를 붙여넣고 실행합니다. 필요한 unwrap 권한이 없음을 나타내는 오류 메시지와 함께 쿼리가 실패해야 합니다.

   SELECT [SSN], [Salary] FROM [HR].[Employees]
   

PowerShell

1. Always Encrypted를 사용하지 않도록 설정한 상태로 데이터베이스에 연결하고 쿼리를 실행하여 암호화된 열에서 데이터를 읽습니다. 쿼리는 암호화된 데이터를 이진 배열로 반환해야 합니다.

   $query = "SELECT [SSN], [Salary] FROM [HR].[Employees]"
   Invoke-SqlCmd -ConnectionString $connectionString -Query $query
   

2. Always Encrypted를 사용하도록 설정된 데이터베이스에 연결하고 쿼리를 실행하여 암호화된 열에서 데이터를 읽습니다. 암호화된 열을 보호하는 열 마스터 키에 액세스할 수 있으므로 쿼리는 일반 텍스트 데이터를 반환해야 합니다.

   $query = "SELECT [SSN], [Salary] FROM [HR].[Employees]"
   Invoke-SqlCmd -ConnectionString "$connectionString; Column Encryption Setting = Enabled" -Query $query
   

참고 항목

Invoke-SqlCmd는 암호화된 열에 데이터를 필터링하거나 삽입할 수 있는 쿼리를 지원하지 않습니다. 이러한 쿼리는 매개 변수화되어야 하며 Invoke-SqlCmd는 매개 변수가 있는 쿼리를 지원하지 않습니다.

다음 단계

• Always Encrypted를 사용하여 애플리케이션 개발

참고 항목

• Always Encrypted 설명서
• 보안 Enclave를 사용하는 Always Encrypted에 대한 설명서
• SQL Server Management Studio를 사용하여 Always Encrypted 키 프로비저닝
• PowerShell을 사용하여 상시 암호화 구성
• Always Encrypted 마법사
• SQL Server Management Studio로 Always Encrypted를 사용하는 열 쿼리