PowerShell로 Always Encrypted를 사용하여 열 암호화 구성

아티클
08/21/2023

적용 대상:SQL Server Azure SQL DatabaseAzure SQL Managed Instance

이 문서에서는 Set-SqlColumnEncryption cmdlet(SqlServer PowerShell 모듈)을 사용하여 데이터베이스 열에 대한 대상 Always Encrypted 구성을 설정하는 단계를 제공합니다. Set-SqlColumnEncryption cmdlet은 대상 데이터베이스의 스키마와 선택한 열에 저장된 데이터를 모두 수정합니다. 열에 저장된 데이터는 열에 대해 지정된 대상 암호화 설정 및 현재 암호화 구성에 따라 암호화, 다시 암호화 또는 암호 해독할 수 있습니다.

참고 항목

SQL Server 2019(15.x)를 사용하고 SQL Server 인스턴스가 보안 Enclave로 구성된 경우 데이터베이스에서 데이터를 이동하지 않고도 암호화 작업을 현재 위치에서 실행할 수 있습니다. 보안 enclave를 사용한 Always Encrypted를 이용하여 내부 열 암호화 구성을 참조하세요. PowerShell은 현재 위치 암호화를 지원하지 않습니다.

SqlServer PowerShell 모듈의 Always Encrypted 지원에 대한 자세한 내용은 PowerShell을 사용하여 Always Encrypted 구성을 참조하세요.

전제 조건

대상 암호화 구성을 설정하려면 다음을 확인해야 합니다.

열 암호화 키는 데이터베이스에서 구성됩니다(열을 암호화하거나 다시 암호화하는 경우). 자세한 내용은 PowerShell을 사용하여 Always Encrypted 키 구성을 참조하세요.
PowerShell cmdlet을 실행하는 컴퓨터에서 암호화, 다시 암호화 또는 암호 해독하려는 각 열에 대한 열 마스터 키에 액세스할 수 있어야 합니다.
SqlServer PowerShell 모듈 버전 22.0.50 이상을 사용합니다.

성능 및 가용성 고려 사항

데이터베이스 에 대해 지정된 대상 암호화 설정을 적용하기 위해 Set-SqlColumnEncryption cmdlet은 대상 테이블이 포함된 열에서 모든 데이터를 투명하게 다운로드하고, 데이터를 임시 테이블 집합(대상 암호화 설정 포함)에 다시 업로드하고, 마지막으로 원래 테이블을 새 테이블 버전으로 바꿉니다. 기본 .NET Framework Data Provider for SQL Server는 대상 열의 현재 암호화 구성 및 대상 열에 대해 지정된 대상 암호화 설정에 따라 다운로드 또는 업로드 시 데이터를 암호화 또는/및 암호 해독합니다. 영향을 받는 테이블의 데이터 크기 및 네트워크 대역폭에 따라 데이터를 이동하는 데 오래 걸릴 수 있습니다.

Set-SqlColumnEncryption cmdlet은 대상 암호화 구성을 설정하는 두 가지 접근 방식(온라인 및 오프라인)을 지원합니다.

오프라인 방식을 사용하면 대상 테이블과 대상 테이블과 관련된 테이블(예: 대상 테이블에 외래 키 관계가 있는 테이블)은 작업 기간 동안 트랜잭션을 작성할 수 없습니다. 오프라인 접근 방식을 사용할 때 외래 키 제약 조건(CHECK 또는 NOCHECK)의 의미 체계는 항상 유지됩니다.

온라인 접근 방식(SqlServer PowerShell 모듈 버전 21.x 이상 필요)을 사용하면 데이터를 복사 및 암호화, 암호 해독 또는 다시 암호화하는 작업이 증분 방식으로 수행됩니다. 애플리케이션이 마지막 반복을 제외하고 전체 데이터 이동 작업에서 대상 테이블에서 데이터를 읽고 쓸 수 있으며, 해당 기간은 사용자가 정의할 수 있는 MaxDownTimeInSeconds 매개 변수에 의해 제한됩니다. 데이터를 복사하는 동안 애플리케이션에서 수행할 수 있는 변경 내용을 검색하고 처리하기 위해 cmdlet을 사용하면 대상 데이터베이스에서 변경 내용 추적을 사용할 수 있습니다. 따라서 온라인 접근 방식은 오프라인 접근 방식보다 서버 쪽에서 더 많은 리소스를 사용할 가능성이 높습니다. 특히 데이터베이스에 대해 쓰기가 많은 워크로드가 실행되는 경우 온라인 접근 방식을 사용하는 데 훨씬 더 많은 시간이 걸릴 수 있습니다. 온라인 접근 방식을 사용하여 한 번에 하나의 테이블을 암호화할 수 있으며 테이블에 기본 키가 있어야 합니다. 기본적으로 외래 키 제약 조건은 애플리케이션에 미치는 영향을 최소화하기 위해 NOCHECK 옵션을 사용하여 다시 만들어집니다. KeepCheckForeignKeyConstraints 옵션을 지정하여 외래 키 제약 조건의 의미 체계를 유지하도록 적용할 수 있습니다 .

다음은 오프라인 및 온라인 접근 방식 중에서 선택하기 위한 지침입니다.

오프라인 방법을 사용합니다.

작업 기간을 최소화합니다.
여러 테이블의 열을 동시에 암호화/암호 해독/다시 암호화하려면
대상 테이블에 기본 키가 없는 경우

온라인 접근 방식 사용:

애플리케이션에 대한 데이터베이스의 가동 중지 시간/사용 불가를 최소화합니다.

보안 고려사항

데이터베이스 열에 대한 암호화를 구성하는 데 사용되는 Set-SqlColumnEncryption cmdlet은 상시 암호화 키와 데이터베이스 열에 저장된 데이터를 둘 다 처리합니다. 따라서 보안 컴퓨터에서 cmdlet을 실행하는 것이 중요합니다. 데이터베이스가 SQL Server에 있는 경우 SQL Server 인스턴스를 호스트하는 컴퓨터 이외의 다른 컴퓨터에서 cmdlet을 실행합니다. 상시 암호화의 주요 목표는 데이터베이스 시스템이 손상된 경우에도 암호화된 중요한 데이터를 안전하게 보호하는 것이므로 SQL Server 컴퓨터에서 키 및/또는 중요한 데이터를 처리하는 PowerShell 스크립트를 실행하면 기능의 이점이 감소하거나 무효화될 수 있습니다.

Task	아티클	일반 텍스트 키/키 저장소에 액세스	데이터베이스에 액세스
1단계 PowerShell 환경을 시작하고 SqlServer 모듈을 가져옵니다.	SqlServer 모듈 가져오기	없음	없음
2단계. 서버 및 데이터베이스에 연결	데이터베이스에 연결	없음	네
3단계: 열 마스터 키(순환할 열 암호화 키 보호)가 Azure Key Vault에 저장된 경우 Azure에 인증	Connect-AzAccount	네	없음
4단계. 열 마스터 키가 Azure Key Vault에 저장된 경우 Azure Key Vault에 대한 액세스 토큰을 가져옵니다.	Get-AzAccessToken	없음	없음
5단계. SqlColumnEncryptionSettings 개체의 배열을 생성합니다. 각 데이터베이스 열에 대해 하나씩 암호화, 다시 암호화 또는 암호 해독하려고 합니다. SqlColumnMasterKeySettings는 PowerShell의 메모리에 있는 개체입니다. 열의 대상 암호화 체계를 지정합니다.	New-SqlColumnEncryptionSettings	없음	없음
6단계 이전 단계에서 만든 SqlColumnMasterKeySettings 개체 배열에 지정된 원하는 암호화 구성을 설정합니다. 열은 지정된 대상 설정 및 열의 현재 암호화 구성에 따라 암호화, 다시 암호화 또는 암호 해독됩니다.	Set-SqlColumnEncryption 참고: 이 단계는 시간이 오래 걸릴 수 있습니다. 애플리케이션은 선택한 방법(온라인 및 오프라인)에 따라 전체 작업 또는 일부 작업을 통해 테이블에 액세스할 수 없습니다.	네	네

오프라인 접근 방식을 사용하여 열 암호화 - 예제

아래 예제에서는 두 열에 대한 대상 암호화 구성을 설정하는 방법을 보여 줍니다. 두 열 중 하나가 아직 암호화되지 않은 경우 암호화됩니다. 다른 키 및/또는 다른 암호화 형식을 사용하여 이미 암호화된 열이 있으면 암호가 해독된 다음 지정된 대상 키/형식으로 다시 암호화됩니다.

# Import the SqlServer module.
Import-Module "SqlServer" -MinimumVersion 22.0.50
Import-Module Az.Accounts -MinimumVersion 2.2.0

#Connect to Azure
Connect-AzAccount

# Obtain an access token for key vaults.
$keyVaultAccessToken = (Get-AzAccessToken -ResourceUrl https://vault.azure.net).Token  

# Connect to your database.
$serverName = "<server name>"
$databaseName = "<database name>"
# Change the authentication method in the connection string, if needed.
$connStr = "Server = " + $serverName + "; Database = " + $databaseName + "; Integrated Security = True"
$database = Get-SqlDatabase -ConnectionString $connStr

# Encrypt the selected columns (or re-encrypt, if they are already encrypted using keys/encrypt types, different than the specified keys/types.
$ces = @()
$ces += New-SqlColumnEncryptionSettings -ColumnName "dbo.Patients.SSN" -EncryptionType "Deterministic" -EncryptionKey "CEK1"
$ces += New-SqlColumnEncryptionSettings -ColumnName "dbo.Patients.BirthDate" -EncryptionType "Randomized" -EncryptionKey "CEK1"
Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces -LogFileDirectory . -KeyVaultAccessToken $keyVaultAccessToken

온라인 방법을 사용하여 열 암호화 - 예제

아래 예제에서는 온라인 방법을 사용하여 몇 개의 열에 대한 대상 암호화 구성을 설정하는 방법을 보여 줍니다. 두 열 중 하나가 아직 암호화되지 않은 경우 암호화됩니다. 다른 키 및/또는 다른 암호화 형식을 사용하여 이미 암호화된 열이 있으면 암호가 해독된 다음 지정된 대상 키/형식으로 다시 암호화됩니다.

# Import the SqlServer module.
Import-Module "SqlServer" -MinimumVersion 22.0.50
Import-Module Az.Accounts -MinimumVersion 2.2.0

#Connect to Azure
Connect-AzAccount

# Obtain an access token for key vaults.
$keyVaultAccessToken = (Get-AzAccessToken -ResourceUrl https://vault.azure.net).Token  

# Connect to your database.
$serverName = "<server name>"
$databaseName = "<database name>"
# Change the authentication method in the connection string, if needed.
$connStr = "Server = " + $serverName + "; Database = " + $databaseName + "; Integrated Security = True"
$database = Get-SqlDatabase -ConnectionString $connStr

# Encrypt the selected columns (or re-encrypt, if they are already encrypted using keys/encrypt types, different than the specified keys/types.
$ces = @()
$ces += New-SqlColumnEncryptionSettings -ColumnName "dbo.Patients.SSN" -EncryptionType "Deterministic" -EncryptionKey "CEK1"
$ces += New-SqlColumnEncryptionSettings -ColumnName "dbo.Patients.BirthDate" -EncryptionType "Randomized" -EncryptionKey "CEK1"
Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces -UseOnlineApproach -MaxDowntimeInSeconds 180 -LogFileDirectory . -KeyVaultAccessToken $keyVaultAccessToken

열 암호 해독 - 예제

다음 예제에서는 데이터베이스에서 현재 암호화된 모든 열의 암호를 해독하는 방법을 보여 줍니다.

# Import the SqlServer module.
Import-Module "SqlServer" -MinimumVersion 22.0.50
Import-Module Az.Accounts -MinimumVersion 2.2.0

#Connect to Azure
Connect-AzAccount

# Obtain an access token for key vaults.
$keyVaultAccessToken = (Get-AzAccessToken -ResourceUrl https://vault.azure.net).Token  

# Connect to your database.
$serverName = "<server name>"
$databaseName = "<database name>"
# Change the authentication method in the connection string, if needed.
$connStr = "Server = " + $serverName + "; Database = " + $databaseName + "; Integrated Security = True"
$database = Get-SqlDatabase -ConnectionString $connStr

# Find all encrypted columns, and create a SqlColumnEncryptionSetting object for each column.
$ces = @()
$tables = $database.Tables
for($i=0; $i -lt $tables.Count; $i++){
    $columns = $tables[$i].Columns
    for($j=0; $j -lt $columns.Count; $j++) {
        if($columns[$j].isEncrypted) {
            $threeColPartName = $tables[$i].Schema + "." + $tables[$i].Name + "." + $columns[$j].Name 
            $ces += New-SqlColumnEncryptionSettings -ColumnName $threeColPartName -EncryptionType "Plaintext" 
        }
    }
}

# Decrypt all columns.
Set-SqlColumnEncryption -ColumnEncryptionSettings $ces -InputObject $database -LogFileDirectory . -KeyVaultAccessToken $keyVaultAccessToken

다음 단계

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

다음을 통해 공유