SQL Server Management Studio로 Always Encrypted를 사용하는 열 쿼리

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

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

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

참고 항목

Azure Key Vault의 관리형 HSM에 저장된 열 마스터 키를 사용하려면 SSMS 18.9 이상 버전이 필요합니다.

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

암호화된 열에 저장된 데이터의 암호 텍스트를 검색하는 SELECT 쿼리를 실행해도(데이터를 암호 해독하지 않고) 데이터를 보호하는 열 마스터 키에 액세스할 필요가 없습니다. SSMS에서 암호화된 열의 값을 암호 텍스트로 검색하려면 다음을 수행합니다.

1. 쿼리를 실행하고 있는 열을 보호하는 키에 대한 메타데이터에 액세스할 수 있는지 확인합니다. 실제 열 마스터 키에 액세스할 필요는 없지만 데이터베이스에서 열 마스터 키 및 열 암호화 키 메타데이터 개체를 보려면 데이터베이스 수준 권한이 필요합니다. 자세한 내용은 아래의 암호화된 열을 쿼리하기 위한 사용 권한을 참조 하세요 .
2. 암호 텍스트 값을 검색하는 쿼리를 실행할 쿼리 편집기 창의 데이터베이스 연결에 대해 Always Encrypted를 사용하지 않도록 설정 SELECT 했는지 확인합니다. 아래 데이터베이스 연결에 대해 Always Encrypted 사용 및 비활성화를 참조하세요.
3. SELECT 쿼리를 실행합니다. 암호화된 열에서 검색된 모든 데이터는 이진(암호화된) 값으로 반환됩니다.

암호 텍스트 검색 예제

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

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

암호화된 열에서 일반 텍스트 값을 검색하여 값의 암호를 해독하려면

1. 쿼리를 실행 중인 열을 보호하는 키에 대한 열 마스터 키 및 메타데이터에 액세스할 수 있는지 확인합니다. 자세한 내용은 아래의 암호화된 열을 쿼리하기 위한 사용 권한을 참조 하세요 .
2. 쿼리 편집기 창에 대해 데이터베이스 연결에 대해 Always Encrypted를 사용하도록 설정했는지 확인합니다. 이 창에서는 데이터를 검색하고 암호를 해독하는 쿼리를 실행 SELECT 합니다. 이는 SSMS에서 사용하는 .NET Framework Data Provider for SQL Server에 쿼리 결과 집합의 암호화된 데이터의 암호를 해독하도록 지시합니다. 아래 데이터베이스 연결에 대해 Always Encrypted 사용 및 비활성화를 참조하세요.
3. SELECT 쿼리를 실행합니다. 암호화된 열에서 검색된 모든 데이터는 원래 데이터 형식의 일반 텍스트 값으로 반환됩니다.

일반 텍스트 검색 예제

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

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

암호화된 열을 대상으로 하는 값(예: 암호화된 열에 저장된 값으로 삽입, 업데이트 또는 필터링하는 쿼리)을 보내는 쿼리를 실행하려면 다음을 수행합니다.

1. 쿼리가 실행되는 열을 보호하는 키에 대한 열 마스터 키 및 메타데이터에 액세스할 수 있는지 확인합니다. 자세한 내용은 아래의 암호화된 열을 쿼리하기 위한 권한을 참조하세요.

2. 쿼리 편집기 창에 대해 데이터베이스 연결에 대해 Always Encrypted를 사용하도록 설정했는지 확인합니다. 이 창에서는 데이터를 검색하고 암호를 해독하는 쿼리를 실행 SELECT 합니다. 이는 SSMS에서 사용하는 .NET Framework Data Provider for SQL Server에 쿼리 결과 집합의 암호화된 데이터의 암호를 해독하도록 지시합니다. 아래 데이터베이스 연결에 대해 Always Encrypted 사용 및 비활성화를 참조하세요.

3. 쿼리 편집기 창에서 Always Encrypted에 대한 매개 변수화가 사용하도록 설정되어 있는지 확인합니다. (SSMS 버전 17.0 이상이 필요합니다.) Transact-SQL 변수를 선언하고 값으로 초기화합니다. 데이터베이스에 보내거나 업데이트하거나 필터링하려고 합니다. 자세한 내용은 아래의 Always Encrypted에 대한 매개 변수화 를 참조하세요.

4. 데이터베이스에 TRANSACT-SQL 변수 값을 전송하는 쿼리를 실행합니다. SSMS는 변수를 쿼리 매개 변수로 변환하고 데이터베이스로 보내기 전에 해당 값을 암호화합니다.

예

테이블의 암호화된 char(11) 열이라고 가정 SSN 하면 아래 스크립트는 SSN 열에 포함된 '795-73-9838' 행을 찾아 열 값을 LastName 반환합니다. 이 경우 데이터베이스 연결에 Always Encrypted가 활성화되고, 쿼리 편집기 창에서 Always Encrypted에 대한 매개 변수화가 사용되며, 열에 대해 구성된 열 마스터 키에 SSNPatients 액세스할 수 있습니다.

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

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

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

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

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

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

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

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

새 연결을 만들거나 서버에 연결 대화 상자를 사용하여 기존 연결을 변경할 때 Always Encrypted를 사용하거나 사용하지 않도록 설정할 수 있습니다.

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

1. 서버에 연결 대화 상자 열기(자세한 내용은 SQL Server 인스턴스에 연결 참조).
2. 옵션을 선택합니다.
3. 상시 암호화 탭을 선택합니다. Always Encrypted를 사용하도록 설정하려면 Always Encrypted 사용(열 암호화)을 선택합니다. Always Encrypted를 사용하지 않도록 설정하려면 Always Encrypted 사용(열 암호화)이 선택되지 않았는지 확인합니다.
4. 연결을 선택합니다.

팁

기존 쿼리 편집기 창에서 Always Encrypted를 사용하도록 설정하고 사용하지 않도록 설정하려면 다음을 수행합니다.

1. 쿼리 편집기 창에서 아무 곳이나 마우스 오른쪽 단추로 클릭합니다.
2. 연결 변경 ...을>선택합니다. 그러면 쿼리 편집기 창에 대한 현재 연결에 대한 서버에 연결 대화 상자가 열립니다.
3. 위의 단계에 따라 Always Encrypted를 사용하거나 사용하지 않도록 설정하고 연결을 클릭합니다.

참고 항목

보안 Enclave와 함께 Always Encrypted를 사용할 때 서버 쪽 보안 Enclave를 활용하는 문을 실행하려면 보안 Enclave를 사용하여 Transact-SQL 문 실행을 참조 하세요.

Always Encrypted에 대한 매개 변수화

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

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

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

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

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

현재 쿼리 편집기 창에서 Always Encrypted에 대해 매개 변수화를 사용하거나 사용하지 않도록 설정하려면 다음을 수행합니다.

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

향후 쿼리 편집기 창에 Always Encrypted에 대한 매개 변수화를 사용하도록/사용하지 않도록 설정하려면 다음을 수행합니다.

1. 주 메뉴에서 도구를 선택합니다.
2. 옵션... 을 선택합니다.
3. 쿼리 실행>SQL Server>고급으로 이동합니다.
4. Always Encrypted에 대해 매개 변수화 사용을 선택하거나 선택 취소합니다.
5. 확인을 선택합니다.

Always Encrypted를 사용하도록 설정된 데이터베이스 연결을 사용하는 쿼리 편집기 창에서 쿼리를 실행하지만 쿼리 편집기 창에 매개 변수화를 사용하도록 설정하지 않은 경우 사용하도록 설정하라는 메시지가 표시됩니다.

참고 항목

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

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

쿼리 편집기 창에서 Always Encrypted에 대한 매개 변수화와 데이터베이스 연결의 Always Encrypted 동작을 모두 사용하도록 설정한 경우 SQL Server Management Studio는 다음 필수 조건을 충족하는 Transact-SQL 변수를 매개 변수화하려고 시도합니다.

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

다음은 변수의 예입니다. SQL Server Management Studio는 매개 변수화합니다.

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

SQL Server Management 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   

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

성공적으로 매개 변수화할 수 있는 변수의 선언은 쿼리 편집기에 경고 밑줄로 표시됩니다. 경고 밑줄로 표시된 선언 문을 마우스로 가리키면 결과 SqlParameter 개체의 키 속성 값(변수가 매핑됨): SqlDbType, Size, Precision, Scale, SqlValue 등 매개 변수화 프로세스의 결과가 표시됩니다. 오류 목록 보기의 경고 탭에서 성공적으로 매개 변수화된 모든 변수의 전체 목록을 볼 수도 있습니다. 오류 목록 보기를 열려면 주 메뉴에서 보기를 선택한 다음 오류 목록을 선택합니다.

SQL Server Management Studio에서 변수를 매개 변수화하려고 했지만 매개 변수화에 실패한 경우 변수 선언에 오류 밑줄이 표시됩니다. 오류 밑줄로 표시된 선언 문을 마우스로 가리키면 오류에 대한 결과가 표시됩니다. 오류 목록 보기의 오류 탭에서 모든 변수에 대한 전체 매개 변수화 오류 목록을 볼 수도 있습니다는. 오류 목록 보기를 열려면 주 메뉴에서 보기를 선택한 다음 오류 목록을 선택합니다.

아래 스크린샷은 6개의 변수 선언 예제를 보여 줍니다. SQL Server Management Studio는 처음 세 개의 변수를 매개 변수화했습니다. 마지막 세 개의 변수가 매개 변수화에 대한 필수 조건을 충족하지 않았기 때문에 SQL Server Management Studio는 매개 변수화를 시도하지 않았습니다(선언은 어떤 방식으로도 표시되지 않음).

아래의 또 다른 예에서는 매개 변수화에 대한 필수 조건을 충족하는 두 개의 변수를 보여 주지만 변수가 잘못 초기화되어 매개 변수화 시도가 실패했습니다.

참고 항목

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

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 내에서 처리됩니다. 매개 변수화를 사용하면 일부 형식 변환이 SQL Server Management Studio 내의 .NET Framework에서 수행됩니다. .NET Framework 형식 시스템과 SQL Server 형식 시스템 간의 차이(예: float와 같은 일부 형식의 정밀도 차이)로 인해 매개 변수화를 사용하도록 설정된 상태로 실행된 쿼리는 매개 변수화를 사용하지 않고 실행된 쿼리와 다른 결과를 생성할 수 있습니다.

다음 단계

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

참고 항목

• Always Encrypted