PowerShell을 사용하여 열 암호화 구성Configure Column Encryption using PowerShell

이 항목 적용 대상: 예SQL Server예Azure SQL 데이터베이스없습니다Azure SQL 데이터 웨어하우스 없습니다 병렬 데이터 웨어하우스THIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

이 문서에서는 SqlServer PowerShell 모듈의 Set-SqlColumnEncryption cmdlet을 사용하여 데이터베이스 열에 대한 대상 상시 암호화 구성을 설정하는 단계를 제공합니다.This article provides the steps for setting the target Always Encrypted configuration for database columns using the Set-SqlColumnEncryption cmdlet (in the SqlServer PowerShell module). Set-SqlColumnEncryption cmdlet은 대상 데이터베이스의 스키마와 선택한 열에 저장된 데이터를 둘 다 수정합니다.The Set-SqlColumnEncryption cmdlet modifies both the schema of the target database as well as the data stored in the selected columns. 열에 지정된 대상 암호화 설정과 현재 암호화 구성에 따라 열에 저장된 데이터를 암호화, 다시 암호화 또는 암호 해독할 수 있습니다.The data stored in a column can be encrypted, re-encrypted or decrypted, depending the specified target encryption settings for the columns and the current encryption configuration. SqlServer PowerShell 모듈의 Always Encrypted 지원에 대한 자세한 내용은 PowerShell을 사용하여 Always Encrypted 구성을 참조하세요.For more information about Always Encrypted support in the SqlServer PowerShell module, please see Configure Always Encrypted using PowerShell.

필수 구성 요소Prerequisites

대상 암호화 구성을 설정하려면 다음을 확인해야 합니다.To set the target encryption configuration, you need to make sure:

  • 열 암호화 키가 데이터베이스에 구성되어 있어야 합니다(열을 암호화 또는 다시 암호화하는 경우).a column encryption key is configured in the database (if you are encrypting or re-encrypting a column). 자세한 내용은 PowerShell을 사용하여 상시 암호화 키 구성을 참조하세요.For details, see Configure Always Encrypted Keys using PowerShell.
  • PowerShell cmdlet을 실행하는 컴퓨터에서 암호화, 다시 암호화 또는 암호 해독하려는 각 열에 대한 열 마스터 키에 액세스할 수 있어야 합니다.you can access the column master key for each column you want to encrypt, re-encrypt, or decrypt, from the computer running the PowerShell cmdlets.

성능 및 가용성 고려 사항Performance and Availability Considerations

데이터베이스에 대해 지정된 대상 암호화 설정을 적용하기 위해 Set-SqlColumnEncryption cmdlet은 대상 테이블이 포함된 열에서 모든 데이터를 투명하게 다운로드하고, 데이터를 임시 테이블 집합에 다시 업로드하며(대상 암호화 설정 사용), 마지막으로 원래 테이블을 새 버전의 테이블로 바꿉니다.To apply the specified target encryption settings for the database, the Set-SqlColumnEncryption cmdlet transparently downloads all data from the columns containing the target tables, uploads the data back to a set of temporary table (with the target encrypted settings), and finally replaces the original tables with the new versions of the tables. 기본 .NET Framework Data Provider for SQL Server는 다운로드 및/또는 업로드 시 대상 열의 현재 암호화 구성과 대상 열의 지정된 대상 암호화 설정에 따라 데이터를 암호화 및/또는 암호 해독합니다.The underlying .NET Framework Data Provider for SQL Server encrypts or/and decrypts data on download or/and upload, depending on the current encryption configuration of the target column is and the specified target encryption settings for the target columns. 영향을 받는 테이블의 데이터 크기 및 네트워크 대역폭에 따라 데이터를 이동하는 데 오래 걸릴 수 있습니다.The operation to move the data may take a long time, depending on the size of the data in impacted tables and the network bandwidth.

Set-SqlColumnEncryption cmdlet은 대상 암호화 구성을 설정하는 두 가지 접근 방식(온라인 및 오프라인)을 지원합니다.The Set-SqlColumnEncryption cmdlet supports two approaches for setting up the target encryption configuration: online and offline.

오프라인 접근 방식을 사용하는 경우 대상 테이블(및 대상 테이블과 관련된 모든 테이블, 예: 대상 테이블과 외래 키 관계가 있는 모든 테이블)을 사용하여 전체 작업 기간 동안 트랜잭션을 작성할 수 없습니다.With the offline approach, the target tables (and any tables related to the target tables, e.g. any tables a target table have foreign key relationships with) are unavailable to write transactions throughout the duration of the operation. 외래 키 제약 조건의 의미 체계(CHECK 또는 NOCHECK)는 오프라인 방법을 사용하는 경우 항상 유지됩니다.The semantics of foreign key constraints (CHECK or NOCHECK) are always preserved when using the offline approach.

21.x 이상 버전의 SqlServer PowerShell 모듈이 필요한 온라인 접근 방식을 사용하는 경우 데이터를 복사 및 암호화하거나, 암호를 해독하거나, 다시 암호화하는 작업이 증분적으로 수행됩니다.With the online approach (requires the SqlServer PowerShell module version 21.x or later), the operation of copying and encrypting, decrypting or re-encrypting the data is performed incrementally. 응용 프로그램이 마지막 반복을 제외하고 전체 데이터 이동 작업에서 대상 테이블에서 데이터를 읽고 쓸 수 있으며, 해당 기간은 사용자가 정의할 수 있는 MaxDownTimeInSeconds 매개 변수에 의해 제한됩니다.Applications can read and write data from and to the target tables throughout the data movement operation, except the very last iteration, the duration of which is limited by the MaxDownTimeInSeconds parameter (you can define). 데이터가 복사되는 동안 응용 프로그램에서 적용할 수 있는 변경 내용을 검색하고 처리하기 위해 이 cmdlet은 대상 데이터베이스에서 변경 내용 추적을 지원합니다.To detect and process the changes, applications can make while the data is being copied, the cmdlet enables Change Tracking in the target database. 따라서 온라인 접근 방식은 오프라인 접근 방식보다 서버 쪽의 리소스를 더 많이 사용할 가능성이 높습니다.Because of that, the online approach is likely to consume more resources on the server side than the online approach. 온라인 접근 방식에서는 특히 쓰기 작업이 많은 워크로드가 데이터베이스에 대해 실행되는 경우 작업이 훨씬 오래 걸릴 수도 있습니다.The operation may also take much more time with the online approach, especially if a write-heavy workload is running against the database. 온라인 접근 방식은 한 번에 하나의 테이블을 암호화하는 데 사용될 수 있으며, 테이블에는 기본 키가 있어야 합니다.The online approach can be used to encrypt one table at a time and the table must have a primary key. 기본적으로 외래 키 제약 조건은 응용 프로그램에 대한 영향을 최소화하기 위해 NOCHECK 옵션으로 다시 만들어집니다.By default, foreign key constraints are recreated with the NOCHECK option to minimize the impact on applications. KeepCheckForeignKeyConstraints 옵션을 지정하여 외래 키 제약 조건의 의미 체계 유지를 적용할 수 있습니다.You can enforce preserving the semantics of foreign key constraints by specifying the KeepCheckForeignKeyConstraints option.

다음은 오프라인 접근 방식과 온라인 접근 방식을 선택하는 방법에 대한 지침입니다.Here are the guidelines for choosing between the offline and online approaches:

오프라인 접근 방식 사용:Use the offline approach:

  • 작업 기간을 최소화하기 위해To minimize the duration of the operation.
  • 여러 테이블의 열을 동시에 암호화/암호 해독/다시 암호화하기 위해To encrypt/decrypt/re-encrypt columns in multiple tables at the same time.
  • 대상 테이블에 기본 키가 없는 경우If the target table does not have a primary key.

온라인 접근 방식 사용:Use the online approach:

  • 응용 프로그램에 대한 데이터베이스의 가동 중지 시간/사용 불가를 최소화합니다.To minimize the downtime/unavailability of the database to your applications.

보안 고려 사항Security Considerations

데이터베이스 열에 대한 암호화를 구성하는 데 사용되는 Set-SqlColumnEncryption cmdlet은 상시 암호화 키와 데이터베이스 열에 저장된 데이터를 둘 다 처리합니다.The Set-SqlColumnEncryption cmdlet, used to configure encryption for database columns, handles both Always Encrypted keys and the data stored in database columns. 따라서 보안 컴퓨터에서 cmdlet을 실행하는 것이 중요합니다.Therefore, it is important you run the cmdlet on a secure computer. 데이터베이스가 SQL Server에 있는 경우 SQL Server 인스턴스를 호스트하는 컴퓨터 이외의 다른 컴퓨터에서 cmdlet을 실행합니다.If your database is in SQL Server, execute the cmdlet from a different computer than the computer hosting your SQL Server instance. 상시 암호화의 주요 목표는 데이터베이스 시스템이 손상된 경우에도 암호화된 중요한 데이터를 안전하게 보호하는 것이므로 SQL Server 컴퓨터에서 키 및/또는 중요한 데이터를 처리하는 PowerShell 스크립트를 실행하면 기능의 이점이 감소하거나 무효화될 수 있습니다.Because the primary goal of Always Encrypted is to ensure encrypted sensitive data is safe even if the database system gets compromised, executing a PowerShell script that processes keys and/or sensitive data on the SQL Server computer can reduce or defeat the benefits of the feature.

태스크Task 아티클Article 일반 텍스트 키/키 저장소 액세스Accesses plaintext keys/key store 데이터베이스 액세스Accesses database
1단계.Step 1. PowerShell 환경을 시작하고 SqlServer 모듈을 가져옵니다.Start a PowerShell environment and import the SqlServer module. SqlServer 모듈 가져오기Import the SqlServer module 아니오No 아니오No
2단계.Step 2. 서버 및 데이터베이스에 연결Connect to your server and database 데이터베이스에 연결Connecting to a Database 아니오No Yes
3단계.Step 3. 열 마스터 키(순환할 열 암호화 키 보호)가 Azure 주요 자격 증명 모음에 저장된 경우 Azure에 인증Authenticate to Azure, if your column master key (protecting the column encryption key, to be rotated), is stored in Azure Key Vault Add-SqlAzureAuthenticationContextAdd-SqlAzureAuthenticationContext Yes 아니오No
4단계.Step 4. 암호화, 다시 암호화 또는 암호 해독하려는 각 데이터베이스 열에 대해 하나씩 SqlColumnEncryptionSettings 개체 배열을 생성합니다.Construct an array of SqlColumnEncryptionSettings objects - one for each database column, you want to encrypt, re-encrypt, or decrypt. SqlColumnMasterKeySettings는 PowerShell의 메모리에 있는 개체입니다.SqlColumnMasterKeySettings is an object that exists in memory (in PowerShell). 열에 대한 대상 암호화 체계를 지정합니다.It specifies the target encryption scheme for a column. New-SqlColumnEncryptionSettingsNew-SqlColumnEncryptionSettings 아니오No 아니오No
5단계.Step 5. 이전 단계에서 만든 SqlColumnMasterKeySettings 개체 배열에 지정된 원하는 암호화 구성을 설정합니다.Set the desired encryption configuration, specified in the array of SqlColumnMasterKeySettings objects, you created in the previous step. 열에 지정된 대상 설정과 현재 암호화 구성에 따라 열이 암호화, 다시 암호화 또는 암호 해독됩니다.A column will be encrypted, re-encrypted or decrypted, depending on the specified target settings and the current encryption configuration of the column. Set-SqlColumnEncryptionSet-SqlColumnEncryption

참고: 이 단계는 시간이 오래 걸릴 수 있습니다.Note: This step may take a long time. 선택한 접근 방식(온라인 또는 오프라인)에 따라 응용 프로그램이 전체 작업 또는 그 일부를 통해 테이블에 액세스할 수 없게 됩니다.Your applications will not be able to access the tables through the entire operation or a portion of it, depending on the approach (online vs. offline), you select.
Yes Yes

오프라인 접근 방식을 사용한 열 암호화 - 예제Encrypt Columns using Offline Approach - Example

아래 예제에서는 두 열에 대한 대상 암호화 구성을 설정하는 방법을 보여 줍니다.The below example demonstrates setting the target encryption configuration for a couple of columns. 열이 암호화되어 있지 않으면 암호화됩니다.If either column is not already encrypted, it will be encrypted. 열이 다른 키 및/또는 다른 암호화 유형을 사용하여 이미 암호화되어 있으면 암호가 해독된 다음 지정된 대상 키/유형으로 다시 암호화됩니다.If any column is already encrypted using a different key and/or a different encryption type, it will be decrypted and then re-encrypted with the specified target key/type.

# Import the SqlServer module.
Import-Module "SqlServer"

# Connect to your database.
$serverName = "<server name>"
$databaseName = "<database name>"
$connStr = "Server = " + $serverName + "; Database = " + $databaseName + "; Integrated Security = True"
$connection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
$connection.ConnectionString = $connStr
$connection.Connect()
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($connection)
$database = $server.Databases[$databaseName]

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

온라인 접근 방식을 사용한 열 암호화 - 예제Encrypt Columns using Online Approach - Example

아래 예제에서는 온라인 접근 방식을 사용하여 두 열에 대한 대상 암호화 구성을 설정하는 방법을 보여 줍니다.The below example demonstrates setting the target encryption configuration for a couple of columns using the online approach. 열이 암호화되어 있지 않으면 암호화됩니다.If either column is not already encrypted, it will be encrypted. 열이 다른 키 및/또는 다른 암호화 유형을 사용하여 이미 암호화되어 있으면 암호가 해독된 다음 지정된 대상 키/유형으로 다시 암호화됩니다.If any column is already encrypted using a different key and/or a different encryption type, it will be decrypted and then re-encrypted with the specified target key/type.

# Import the SqlServer module.
Import-Module "SqlServer"

# Connect to your database.
$serverName = "<server name>"
$databaseName = "<database name>"
$connStr = "Server = " + $serverName + "; Database = " + $databaseName + "; Integrated Security = True"
$connection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
$connection.ConnectionString = $connStr
$connection.Connect()
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($connection)
$database = $server.Databases[$databaseName]

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

열 암호 해독 - 예제Decrypt Columns - Example

다음 예제에서는 데이터베이스에서 현재 암호화된 모든 열의 암호를 해독하는 방법을 보여 줍니다.The following example shows how decrypt all columns that are currently encrypted in a database.

# Import the SqlServer module.
Import-Module "SqlServer"

# Connect to your database.
$serverName = "<server name>"
$databaseName = "<database name>"
$connStr = "Server = " + $serverName + "; Database = " + $databaseName + "; Integrated Security = True"
$connection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
$connection.ConnectionString = $connStr
$connection.Connect()
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($connection)
$database = $server.Databases[$databaseName]

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

추가 리소스Additional Resources