Azure Data Studio를 사용하여 Always Encrypted를 이용하는 열 쿼리

적용 대상:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

이 문서에서는 Azure Data Studio를 사용하여 Always Encrypted로 암호화된 열을 쿼리하는 방법을 설명합니다. Azure Data Studio를 사용하면 다음을 수행할 수 있습니다.

• 암호화된 열에 저장된 암호 텍스트 값을 검색합니다.
• 암호화된 열에 저장된 일반 텍스트 값을 검색합니다.
• 암호화된 열을 대상으로 하는 일반 텍스트 값을 보냅니다(예: 문 또는 UPDATE 문에서 INSERT 절 SELECT 의 WHERE 조회 매개 변수로).

암호화된 열에 저장된 암호 텍스트 값 검색

이 섹션에서는 암호화된 열에 암호 텍스트로 저장된 데이터를 검색하는 방법을 설명합니다.

단계

1. 암호 텍스트 값을 검색하는 SELECT 쿼리를 실행할 쿼리 창에 대한 데이터베이스 연결에 Always Encrypted가 사용 안 함으로 설정되어야 합니다. 아래 데이터베이스 연결에 대해 Always Encrypted 사용 및 비활성화를 참조하세요.
2. SELECT 쿼리를 실행합니다. 암호화된 열에서 검색된 모든 데이터는 이진(암호화된) 값으로 반환됩니다.

예

SSN 이 Patients 테이블의 암호화된 열이라고 가정할 경우, 아래 표시된 쿼리는 데이터베이스 연결에 Always Encrypted가 사용되지 않는 경우 이진 암호 텍스트 값을 검색합니다.

암호화된 열에 저장된 일반 텍스트 값 검색

이 섹션에서는 암호화된 열에 암호 텍스트로 저장된 데이터를 검색하는 방법을 설명합니다.

전제 조건

• Azure Data Studio 버전 17.1 이상.
• 쿼리를 실행하는 열을 보호하는 키에 대한 열 마스터 키 및 메타데이터에 액세스할 수 있어야 합니다. 자세한 내용은 아래의 암호화된 열을 쿼리하기 위한 사용 권한을 참조 하세요 .
• 열 마스터 키는 Azure Key Vault 또는 Windows 인증서 저장소의 키 자격 증명 모음에 저장되어야 합니다. Azure Data Studio는 다른 키 저장소를 지원하지 않으며 Azure Key Vault의 관리형 HSM에 저장된 열 마스터 키를 지원하지 않습니다.

단계

1. 데이터를 검색하고 암호를 해독하는 SELECT 쿼리를 실행할 쿼리 창에 대한 데이터베이스 연결에 Always Encrypted를 사용 설정합니다. 그러면 Microsoft .NET Data Provider for SQL Server(Azure Data Studio에서 사용됨)가 쿼리 결과 집합에서 암호화된 열의 암호를 해독하도록 지시합니다. 아래 데이터베이스 연결에 대해 Always Encrypted 사용 및 비활성화를 참조하세요.
2. SELECT 쿼리를 실행합니다. 암호화된 열에서 검색된 모든 데이터는 원래 데이터 형식의 일반 텍스트 값으로 반환됩니다.

예

SSN이 테이블의 Patients 암호화된 열이라고 가정하면 데이터베이스 연결에 Always Encrypted를 사용하도록 설정하고 열에 대해 구성된 SSN 열 마스터 키에 대한 액세스 권한이 있는 경우 아래 표시된 쿼리는 일반 텍스트 값을 반환합니다.

암호화된 열을 대상으로 하는 일반 텍스트 값 보내기

이 섹션에서는 암호화된 열을 대상으로 하는 값을 보내는 쿼리를 실행하는 방법을 설명합니다. 예를 들어 암호화된 열에 저장된 값으로 삽입, 업데이트 또는 필터링하는 쿼리는 다음과 같습니다.

전제 조건

• Azure Data Studio 버전 18.1 이상.
• 쿼리를 실행 중인 열을 보호하는 키에 대한 열 마스터 키 및 메타데이터에 액세스할 수 있어야 합니다. 자세한 내용은 아래의 암호화된 열을 쿼리하기 위한 사용 권한을 참조 하세요 .
• 열 마스터 키는 Azure Key Vault 또는 Windows 인증서 저장소의 키 자격 증명 모음에 저장되어야 합니다. Azure Data Studio는 다른 키 저장소를 지원하지 않으며 Azure Key Vault의 관리형 HSM에 저장된 열 마스터 키를 지원하지 않습니다.

단계

1. 데이터를 검색하고 암호를 해독하는 SELECT 쿼리를 실행할 쿼리 창에 대한 데이터베이스 연결에 Always Encrypted를 사용 설정합니다. 그러면 암호화된 열을 대상으로 하는 쿼리 매개 변수를 암호화하고 암호화된 열에서 검색된 결과의 암호를 해독하도록 Microsoft .NET Data Provider for SQL Server(Azure Data Studio에서 사용)에 지시합니다. 아래 데이터베이스 연결에 대해 Always Encrypted 사용 및 비활성화를 참조하세요.
2. 쿼리 창에 Always Encrypted에 대해 매개 변수화를 사용하도록 설정합니다. 자세한 내용은 아래의 Always Encrypted에 대한 매개 변수화 를 참조하세요.
3. Transact-SQL 변수를 선언하고 데이터베이스에 보내려는 값(삽입, 업데이트 또는 필터링 기준)으로 초기화합니다.
4. 데이터베이스에 TRANSACT-SQL 변수 값을 전송하는 쿼리를 실행합니다. Azure Data Studio는 변수를 쿼리 매개 변수로 변환하고 데이터베이스로 보내기 전에 해당 값을 암호화합니다.

예

테이블의 암호화된 char(11) 열 Patients 이라고 가정 SSN 하면 아래 스크립트는 SSN 열에 포함된 '795-73-9838' 행을 찾으려고 시도합니다. 데이터베이스 연결에 대한 Always Encrypted를 사용 설정하고, 쿼리 창에서 Always Encrypted에 대한 매개 변수화를 사용 설정했으며, SSN 열에 대해 구성한 열 마스터 키에 액세스할 수 있다면 결과가 반환됩니다.

@SSN char(11) = '795-73-9838' SELECT * FROM [dbo].[ Patients] WHERE [SSN] = @SSN query and the results of the query." />

암호화된 열을 쿼리하기 위한 권한

암호화된 열에서 데이터를 검색하는 쿼리를 포함하여 암호화된 열에 대해 쿼리를 실행하려면 데이터베이스에서 VIEW ANY COLUMN MASTER KEY DEFINITION 및 VIEW ANY COLUMN ENCRYPTION KEY DEFINITION 권한이 필요합니다.

위의 권한 외에도 쿼리 결과의 암호를 해독하거나 Transact-SQL 변수를 매개 변수화하여 생성된 쿼리 매개 변수를 암호화하려면 대상 열을 보호하는 열 마스터 키에 액세스하고 사용할 키 저장소 권한이 필요합니다. 키 저장소 권한에 대한 자세한 내용은 Always Encrypted에 대한 열 마스터 키 만들기 및 저장으로 이동하여 키 저장소와 관련된 섹션을 찾습니다.

데이터베이스 연결에 Always Encrypted 사용 및 사용 안 함

Azure Data Studio에서 데이터베이스에 연결하는 경우 데이터베이스 연결에 대해 Always Encrypted를 사용하거나 사용하지 않도록 설정할 수 있습니다. 기본적으로 Always Encrypted는 사용하지 않도록 설정됩니다.

데이터베이스 연결에 Always Encrypted를 사용하도록 설정하면 Azure Data Studio에서 사용하는 Microsoft .NET Data Provider for SQL Server가 다음 작업을 투명하게 시도합니다.

• 암호화된 열에서 검색된 값을 해독하고 쿼리 결과에서 반환합니다.
• 암호화된 데이터베이스 열을 대상으로 하는 매개 변수가 있는 Transact-SQL 변수의 값을 암호화합니다.

연결에 Always Encrypted를 사용하도록 설정하지 않으면 Microsoft .NET Data Provider for SQL Server는 쿼리 매개 변수를 암호화하거나 결과를 해독하지 않습니다.

데이터베이스에 연결할 때 Always Encrypted를 사용하거나 사용하지 않도록 설정할 수 있습니다. 데이터베이스에 연결하는 방법에 대한 일반적인 내용은 다음을 참조하세요.

• 빠른 시작: Azure Data Studio를 사용하여 SQL Server 연결 및 쿼리
• 빠른 시작: Azure Data Studio를 사용하여 Azure SQL 데이터베이스 연결 및 쿼리

Always Encrypted를 사용(사용하지 않도록 설정)하려면:

1. 연결 대화 상자에서 고급...을 클릭합니다.
2. 연결에 Always Encrypted를 사용하도록 설정하려면 Always Encrypted 필드를 사용으로 설정합니다. Always Encrypted를 사용하지 않도록 설정하려면 Always Encrypted 필드의 값을 비워 두거나 사용 안 함으로 설정합니다.
3. 확인을 클릭하여 고급 속성을 닫습니다.

보안 enclave를 사용한 Always Encrypted를 사용하는 경우 서버 쪽 보안 enclave를 이용하는 문을 실행하려면 연결에서 Always Encrypted를 사용하도록 설정하는 것 외에도 enclave 증명 프로토콜 및 enclave 증명 URL을 지정해야 합니다. 자세한 내용은 Azure Data Studio에서 enclave를 사용하여 T-SQL 문을 실행하기 위한 필수 구성 요소를 참조 하세요.

팁

기존 쿼리 창에 대해 Always Encrypted를 사용하도록 설정하고 사용하지 않도록 설정하려면 연결 끊기를 클릭한 다음 연결 단추를 클릭하고 위의 단계를 완료하여 Always Encrypted 필드의 원하는 값으로 데이터베이스에 다시 연결합니다.

참고 항목

쿼리 창의 연결 변경 단추는 현재 Always Encrypted 사용 및 사용 안 함 간 토글을 지원하지 않습니다.

Always Encrypted에 대한 매개 변수화

Always Encrypted에 대한 매개 변수화는 Transact-SQL 변수를 쿼리 매개 변수( SqlParameter 클래스의 인스턴스)로 자동으로 변환하는 Azure Data Studio 18.1 이상에서 제공하는 기능입니다. 이렇게 하면 기본 Microsoft .NET Data Provider for SQL Server 가 암호화된 열을 대상으로 하는 데이터를 검색하고 해당 데이터를 데이터베이스로 보내기 전에 암호화할 수 있습니다.

매개 변수화가 없으면 Microsoft .NET Data Provider for SQL Server는 쿼리 창에서 작성한 각 문을 매개 변수가 없는 쿼리로 전달합니다. 쿼리에 암호화된 열을 대상으로 하는 리터럴 또는 Transact-SQL 변수가 포함된 경우 .NET Framework Data Provider for SQL Server는 쿼리를 데이터베이스로 보내기 전에 검색하고 암호화할 수 없습니다. 따라서 일반 텍스트 리터럴 Transact-SQL 변수와 암호화된 열 간의 형식 불일치로 인해 쿼리에 실패합니다. 예를 들어 SSN 열이 암호화된 것으로 가정할 경우 매개 변수화하지 않으면 다음 쿼리가 실패합니다.

DECLARE @SSN CHAR(11) = '795-73-9838'
SELECT * FROM [dbo].[Patients]
WHERE [SSN] = @SSN

Always Encrypted에 대한 매개 변수화 사용 및 사용 안 함

Always Encrypted에 대한 매개 변수화는 기본적으로 사용하지 않도록 설정됩니다.

Always Encrypted에 대해 매개 변수화를 사용하거나 사용하지 않도록 설정하려면 다음을 수행합니다.

1. 파일>기본 설정>설정(Mac에서는 코드>기본 설정>설정)을 선택합니다.
2. 데이터>Microsoft SQL Server로 이동합니다.
3. Always Encrypted에 대해 매개 변수화 사용을 선택하거나 선택 취소합니다.
4. 설정 창을 닫습니다.

참고 항목

Always Encrypted에 대한 매개 변수화는 Always Encrypted를 사용하도록 설정된 데이터베이스 연결을 사용하는 쿼리에서만 작동합니다(데이터베이스 연결에 대해 Always Encrypted 사용 및 사용 안 함 참조). 쿼리 창에서 Always Encrypted를 사용하지 않고 데이터베이스 연결을 사용하는 경우 Transact-SQL 변수는 매개 변수화되지 않습니다.

Always Encrypted의 매개 변수화 작동 방식

쿼리 창에서 Always Encrypted 및 Always Encrypted에 대한 매개 변수화가 모두 사용하도록 설정된 경우 Azure Data Studio는 다음 필수 조건을 충족하는 Transact-SQL 변수를 매개 변수화하려고 시도합니다.

• 동일한 문(인라인 초기화)에서 선언되고 초기화됩니다. 별도의 SET 문을 사용하여 선언된 변수는 매개 변수화되지 않습니다.
• 단일 리터럴을 사용하여 초기화됩니다. 연산자 또는 함수를 포함한 식을 사용하여 초기화된 변수는 매개 변수화되지 않습니다.

다음은 Azure Data Studio가 매개 변수화할 변수의 예입니다.

DECLARE @SSN char(11) = '795-73-9838';
   
DECLARE @BirthDate date = '19990104';
DECLARE @Salary money = $30000;

다음은 Azure Data Studio에서 매개 변수화를 시도하지 않는 변수의 몇 가지 예입니다.

DECLARE @Name nvarchar(50); --Initialization separate from declaration
SET @Name = 'Abel';

DECLARE @StartDate date = GETDATE(); -- a function used instead of a literal

DECLARE @NewSalary money = @Salary * 1.1; -- an expression used instead of a literal

매개 변수화가 성공하려면 다음을 수행합니다.

• 매개 변수를 매개 변수로 초기화하는 데 사용되는 리터럴의 형식은 변수 선언의 형식과 일치해야 합니다.
• 변수의 선언된 형식이 날짜 형식이거나 시간 형식인 경우 변수는 다음 ISO 8601 규격 형식 중 하나를 사용하는 문자열을 사용하여 초기화되어야 합니다.

매개 변수화 오류가 발생하는 Transact-SQL 변수 선언의 예는 다음과 같습니다.

DECLARE @BirthDate date = '01/04/1999' -- unsupported date format   
   
DECLARE @Number int = 1.1 -- the type of the literal does not match the type of the variable   

Azure Data Studio는 Intellisense를 사용하여 성공적으로 매개 변수화할 수 있는 변수와 실패한 매개 변수화 시도(및 이유)를 알려줍니다.

성공적으로 매개 변수화할 수 있는 변수 선언은 쿼리 창에 정보 메시지 밑줄로 표시됩니다. 정보 메시지 밑줄로 표시된 선언 문을 마우스로 가리키면 결과 SqlParameter 클래스 개체의 키 속성 값을 포함하여 매개 변수화 프로세스의 결과가 포함된 메시지가 표시됩니다(변수는 SqlDbType, Size, Precision, Scale, SqlValue에 매핑됨). 문제 보기에서 성공적으로 매개 변수화된 모든 변수의 전체 목록을 볼 수도 있습니다 . 문제 보기를 열려면 문제 보기를>선택합니다.

Azure Data Studio에서 변수를 매개 변수화하려고 했지만 매개 변수화에 실패한 경우 변수 선언에 오류 밑줄이 표시됩니다. 오류 밑줄로 표시된 선언 문을 마우스로 가리키면 오류에 대한 결과가 표시됩니다. 문제 보기에서 모든 변수 에 대한 매개 변수화 오류의 전체 목록을 볼 수도 있습니다 .

참고 항목

Always Encrypted는 제한된 형식 변환 하위 집합을 지원하므로 대부분의 경우 Transact-SQL 변수의 데이터 형식이 대상 데이터베이스 열의 형식과 동일해야 합니다. 예를 들어 SSN 테이블의 Patients 열 형식이 char(11)이라고 가정할 경우 아래 쿼리는 @SSN 변수의 형식(nchar(11))이 열 형식과 일치하지 않으므로 실패합니다.

DECLARE @SSN nchar(11) = '795-73-9838'
SELECT * FROM [dbo].[Patients]
WHERE [SSN] = @SSN;

Msg 402, Level 16, State 2, Line 5   
The data types char(11) encrypted with (encryption_type = 'DETERMINISTIC', 
encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', 
column_encryption_key_database_name = 'Clinic') collation_name = 'Latin1_General_BIN2' 
and nchar(11) encrypted with (encryption_type = 'DETERMINISTIC', 
encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', 
column_encryption_key_database_name = 'Clinic') are incompatible in the equal to operator.

참고 항목

매개 변수화를 사용하지 않으면 형식 변환을 포함하여 전체 쿼리가 SQL Server/Azure SQL Database 내에서 처리됩니다. 매개 변수화를 사용하도록 설정하면 Azure Data Studio 내에서 Microsoft .NET Data Provider for SQL Server에서 일부 형식 변환을 수행합니다. Microsoft .NET 형식 시스템과 SQL Server 형식 시스템 간의 차이(예: float와 같은 일부 형식의 정밀도 차이)로 인해 매개 변수화를 사용하여 실행된 쿼리는 매개 변수화를 사용하지 않고 실행된 쿼리와 다른 결과를 생성할 수 있습니다.

다음 단계

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

참고 항목

• Always Encrypted