다음을 통해 공유

Transact-SQL을 사용하여 열 암호화 인플레이스 구성

  • 아티클

적용 대상: SQL Server 2019(15.x) 이상 - Windows만 해당 Azure SQL 데이터베이스

이 문서에서는 ALTER TABLE/ALTER COLUMN 문으로 보안 enclave를 사용한 Always Encrypted를 이용하여 열에서 내부 암호화 작업을 수행하는 방법을 설명합니다. 바로 암호화 및 일반 사전 요구 사항은 보안 Enclave를 사용한 Always Encrypted를 사용하여 바로 열 암호화 구성을 참조하세요.

ALTER TABLE 또는 ALTER COLUMN 문을 사용하여 열에 대한 대상 암호화 구성을 설정할 수 있습니다. 문을 실행하면 서버 쪽 보안 Enclave는 문의 열 정의에 지정된 현재 및 대상 암호화 구성에 따라 열에 저장된 데이터를 암호화, 다시 암호화 또는 암호 해독합니다.

  • 열이 암호화되어 있지 않은 경우, 열 정의에 ENCRYPTED WITH 절을 지정하면 해당 열이 암호 해독됩니다.
  • 열이 암호화되어 있는 경우, 열 정의에 ENCRYPTED WITH 절을 지정하지 않으면 해당 열이 암호 해독됩니다(일반 텍스트 열로 변환됨).
  • 열이 암호화되어 있는 경우, ENCRYPTED WITH 절을 지정하고 지정된 열 암호화 유형 또는 열 암호화 키가 현재 사용된 암호화 유형 또는 열 암호화 키와 다르면 열이 다시 암호화됩니다.

참고 항목

열을 NULL 또는 NOT NULL로 변경하거나 데이터 정렬을 변경하는 경우를 제외하고는 암호화 작업을 단일 ALTER TABLE/ALTER COLUMN 문의 다른 변경 내용과 결합할 수 없습니다. 예를 들어 단일 ALTER TABLE/ALTER COLUMN Transact-SQL 문에서는 열을 암호화하고 열의 데이터 형식을 변경할 수 없습니다. 두 개의 별도 명령문을 사용합니다.

서버 쪽 보안 Enclave를 사용하는 모든 쿼리에서는 Always Encrypted 및 Enclave 계산을 사용하도록 설정된 연결을 통해 현재 위치 암호화를 트리거하는 ALTER TABLE/ALTER COLUMN문을 보내야 합니다.

이 문서의 나머지 부분에서는 SQL Server Management Studio에서 ALTER TABLE/ALTER COLUMN 문을 사용하여 내부 암호화를 트리거하는 방법을 설명합니다. 또는 Azure Data Studio 또는 애플리케이션에서 ALTER TABLE/ALTER COLUMN을 발급할 수도 있습니다.

참고 항목

현재 SqlServer PowerShell 모듈 및 sqlcmd의 Invoke-Sqlcmd cmdlet은 바로 암호화 작업에 ALTER TABLE/ALTER COLUMN의 사용을 지원하지 않습니다.

SSMS에서 Transact-SQL을 사용하여 현재 위치 암호화 수행

필수 구성 요소

단계

  1. 데이터베이스 연결에서 Always Encrypted 및 Enclave 계산을 사용하도록 설정하여 쿼리 창을 엽니다. 상세 정보는 데이터베이스 연결에 Always Encrypted 사용 및 사용 안 함을 참조하세요.

  2. 쿼리 창에서 ALTER TABLE/ALTER COLUMN문을 실행하여 암호화, 암호 해독 또는 다시 암호화하려는 열에 대한 대상 암호화 구성을 지정합니다. 열을 암호화하거나 다시 암호화하는 경우 ENCRYPTED WITH절을 사용합니다. 열이 문자열 열(예: char, varchar, nchar, nvarchar)인 경우에도 데이터 정렬을 BIN2 데이터 정렬로 변경해야 할 수 있습니다.

    참고 항목

    열 마스터 키가 Azure Key Vault에 저장된 경우 Azure에 로그인하라는 메시지가 표시될 수 있습니다.

  3. 테이블에 액세스하는 모든 일괄 처리 및 저장 프로시저의 계획 캐시를 지워 매개 변수 암호화 정보를 새로 고칩니다.

    ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

    참고 항목

    영향을 받는 쿼리 계획을 캐시에서 제거하지 않으면 암호화 후 첫 번째 쿼리 실행이 실패할 수 있습니다.

    임시 쿼리 성능 저하가 발생할 수 있으므로 ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE 또는 DBCC FREEPROCCACHE를 사용하여 계획 캐시를 신중하게 지우세요. 캐시 지우기의 부정적인 영향을 최소화하기 위해 영향을 받은 쿼리에 대한 계획만 선택적으로 제거할 수 있습니다.

  4. sp_refresh_parameter_encryption을 호출하여 sys.parameters에 보관되었으며 열 암호화으로 인해 무효화되었을 수 있는 각 모듈(저장 프로시저, 함수, 뷰, 트리거)의 매개 변수 메타데이터를 업데이트합니다.

예제

현재 위치에서 열 암호화

아래 예제에서는 다음을 가정합니다.

  • CEK1는 Enclave가 사용 열 암호화 키입니다.
  • SSN 열은 일반 텍스트이며 현재 Latin1, non-BIN2 데이터 정렬 등 기본 데이터베이스 데이터 정렬(예: Latin1_General_CI_AI_KS_WS)을 사용하고 있습니다.

이 문은 결정적 암호화와 Enclave 사용 열 암호화 키를 바로 사용하여 SSN 열을 암호화합니다. 또한 기본 데이터베이스 데이터 정렬을 해당(동일한 코드 페이지) BIN2 데이터 정렬로 덮어씁니다.

이 작업은 온라인(ONLINE = ON)으로 됩니다. 또한 쿼리 계획을 다시 만드는 ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE에 대한 호출은 테이블 스키마 변경의 영향을 받습니다.

ALTER TABLE [dbo].[Employees]
ALTER COLUMN [SSN] [char](11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL
WITH
(ONLINE = ON);
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

암호화 유형 변경을 위해 전체 열 다시 암호화

아래 예제에서는 다음을 가정합니다.

  • SSN 열은 결정적 암호화와 Enclave 사용 열 암호화 키 CEK1를 사용하여 암호화됩니다.
  • 열 수준에서 설정된 현재 데이터 정렬은 Latin1_General_BIN2입니다.

아래 문은 임의 암호화 및 동일한 키(CEK1)를 사용하여 열을 다시 암호화합니다.

ALTER TABLE [dbo].[Employees]
ALTER COLUMN [SSN] [char](11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1]
, ENCRYPTION_TYPE = Randomized
, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL;
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

열을 현재 위치에서 다시 암호화하여 열 암호화 키 회전

아래 예제에서는 다음을 가정합니다.

  • SSN 열은 임의 암호화와 enclave 사용 열 암호화 키 CEK1를 사용하여 암호화됩니다.
  • CEK2는 Enclave 사용 열 암호화 키(CEK1와는 다름)입니다.
  • 열 수준에서 설정된 현재 데이터 정렬은 Latin1_General_BIN2입니다.

아래 문은 열을 CEK2로 다시 암호화합니다.

ALTER TABLE [dbo].[Employees]
ALTER COLUMN [SSN] [char](11) COLLATE Latin1_General_BIN2
ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK2]
, ENCRYPTION_TYPE = Randomized
, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL;
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

바로 열 암호 해독

아래 예제에서는 다음을 가정합니다.

  • SSN은 enclave 사용 열 암호화 키를 사용하여 암호화됩니다.
  • 열 수준에서 설정된 현재 데이터 정렬은 Latin1_General_BIN2입니다.

아래 문은 열의 비밀번호를 해독하고 데이터 정렬을 변경하지 않고 유지합니다. 또는 데이터 정렬을 변경하도록 선택할 수도 있습니다. 예를 들면 데이터 정렬을 동일한 문의 비 BIN2 데이터 정렬로 변경합니다.

ALTER TABLE [dbo].[Employees]
ALTER COLUMN [SSN] [char](11) COLLATE Latin1_General_BIN2
WITH (ONLINE = ON);
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

다음 단계

참고 항목