SQL Server 2022 CLE (Column-Level Encryption) 완전 가이드, ENCRYPTBYKEY / DECRYPTBYKEY — 설치부터 작동까지

 

---

SQL Server 2022 CLE (Column-Level Encryption) 완전 가이드

ENCRYPTBYKEY / DECRYPTBYKEY — 설치부터 작동까지

환경: SQL Server 2022 (RTM-CU20) 16.0.4205.1 · Standard Edition · Windows Server 2022

---

🔑 암호화 계층 구조 (반드시 이 순서)

Service Master Key (SMK)       ← SQL Server 설치 시 자동 생성 (건드리지 않음)
    └── Database Master Key (DMK)   ← 우리가 생성
            └── Certificate          ← 우리가 생성
                    └── Symmetric Key ← 우리가 생성 (실제 데이터 암/복호화)
                            └── 암호화된 데이터 (VARBINARY)

 

 

SQL Server 암호화 계층의 최상위는 Windows DPAPI로 보호되는 Service Master Key이며, 그 아래로 Database Master Key, 인증서/비대칭키, 대칭키 순으로 구성됩니다.

---

STEP 0 — 사전 확인

-- 현재 버전 확인
SELECT @@VERSION;
-- 결과 예: Microsoft SQL Server 2022 (RTM-CU20) 16.0.4205.1

-- 현재 사용자 권한 확인 (db_owner 또는 sysadmin 필요)
SELECT IS_SRVROLEMEMBER('sysadmin') AS IsSysAdmin,
       IS_MEMBER('db_owner')        AS IsDbOwner;

-- 기존 DMK 존재 여부 확인
SELECT name, key_length, algorithm_desc, create_date
FROM   sys.symmetric_keys
WHERE  name = '##MS_DatabaseMasterKey##';

-- 기존 인증서 확인
SELECT name, subject, expiry_date, pvt_key_encryption_type_desc
FROM   sys.certificates;

-- 기존 대칭키 확인
SELECT name, key_algorithm, key_length, create_date
FROM   sys.symmetric_keys
WHERE  name != '##MS_DatabaseMasterKey##';

위 조회 결과가 모두 비어있으면 STEP 1부터 진행합니다. 이미 존재하면 해당 STEP을 건너뜁니다.

---

STEP 1 — 대상 데이터베이스 생성 및 테이블 준비

-- ① 작업 DB 생성 (이미 있으면 생략)
USE master;
GO

IF DB_ID('EncryptDemo') IS NULL
    CREATE DATABASE EncryptDemo;
GO

USE EncryptDemo;
GO

-- ② 암호화 대상 테이블 생성
--    ENCRYPTBYKEY() 반환 타입은 varbinary(8000) 최대
--    계좌번호 정도면 varbinary(256)으로 충분
IF OBJECT_ID('dbo.CustBankAcc', 'U') IS NOT NULL
    DROP TABLE dbo.CustBankAcc;
GO

CREATE TABLE dbo.CustBankAcc
(
    CustSeq    INT           IDENTITY(1,1) NOT NULL PRIMARY KEY,
    CustName   NVARCHAR(200) NOT NULL,
    -- 암호화 저장 컬럼: AES-256 + 헤더 오버헤드 포함해도 256바이트 충분
    BackAccNo  VARBINARY(256) NULL
);
GO

 

주의: DECRYPTBYKEY의 반환 타입은 varbinary이며 최대 크기는 8,000바이트입니다. 컬럼을 VARBINARY(MAX)로 선언해도 함수 자체 한계는 8,000바이트입니다. 계좌번호처럼 짧은 데이터는 VARBINARY(256)이 적절합니다.

---

STEP 2 — Database Master Key (DMK) 생성

DMK는 인증서 및 비대칭키의 개인 키를 보호하는 대칭키입니다. 생성 시 AES_256 알고리즘과 사용자 지정 비밀번호로 암호화되며, 서비스 마스터 키(SMK)에 의해 자동 복호화될 수 있도록 복사본이 master 데이터베이스에도 저장됩니다.

USE EncryptDemo;
GO

-- DMK 생성 (한 DB에 하나만 존재 가능, 이미 있으면 이 단계 건너뜀)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd!DMK_2025#Str0ng';
GO

-- 생성 확인
SELECT name, algorithm_desc, key_length, create_date, modify_date
FROM   sys.symmetric_keys
WHERE  name = '##MS_DatabaseMasterKey##';

 

비밀번호 규칙: Windows 암호 정책 준수 필수 (대/소문자 + 숫자 + 특수문자, 8자 이상)

---

STEP 3 — 인증서 생성

비밀번호를 별도로 지정하지 않으면 인증서의 개인 키는 Database Master Key로 자동 암호화됩니다. DMK가 없는 상태에서 ENCRYPTION BY PASSWORD도 생략하면 오류가 발생합니다.

USE EncryptDemo;
GO

-- 인증서 생성 (개인 키는 DMK로 자동 보호됨)
CREATE CERTIFICATE CertForSymKey
    WITH SUBJECT     = 'Certificate for CustBankAcc Column Encryption',
         EXPIRY_DATE = '2030-12-31';  -- 만료일 명시 권장
GO

-- 생성 확인
SELECT name, subject, expiry_date, pvt_key_encryption_type_desc, thumbprint
FROM   sys.certificates
WHERE  name = 'CertForSymKey';

 

---

STEP 4 — 대칭키 생성

RC4, RC4_128, DESX 알고리즘은 향후 버전에서 제거될 예정입니다. 신규 개발에서는 반드시 AES 계열 알고리즘을 사용하십시오.

USE EncryptDemo;
GO

CREATE SYMMETRIC KEY SymKey_CustBankAcc
    WITH ALGORITHM = AES_256             -- 권장 알고리즘
    ENCRYPTION BY CERTIFICATE CertForSymKey;
GO

-- 생성 확인
SELECT name, key_algorithm, key_length, create_date, modify_date
FROM   sys.symmetric_keys
WHERE  name = 'SymKey_CustBankAcc';

 

---

STEP 5 — 키 오픈 헬퍼 Stored Procedure 생성

USE EncryptDemo;
GO

IF OBJECT_ID('dbo.usp_SymKeyOpen', 'P') IS NOT NULL
    DROP PROCEDURE dbo.usp_SymKeyOpen;
GO

CREATE PROCEDURE dbo.usp_SymKeyOpen
AS
BEGIN
    SET NOCOUNT ON;

    -- 이미 열린 세션이면 OPEN을 재실행해도 무해하지만,
    -- 불필요한 중복 호출을 막기 위해 sys.openkeys 체크
    IF NOT EXISTS (
        SELECT 1
        FROM   sys.openkeys
        WHERE  key_name = 'SymKey_CustBankAcc'
    )
    BEGIN
        OPEN SYMMETRIC KEY SymKey_CustBankAcc
            DECRYPTION BY CERTIFICATE CertForSymKey;
    END
END;
GO

 

---

STEP 6 — 암호화 데이터 INSERT

ENCRYPTBYKEY는 authenticator 파라미터를 지원합니다. Authenticator는 암호화된 필드의 값 치환(whole-value substitution) 공격을 방어합니다. 암호화 시 사용한 authenticator와 동일한 값을 복호화 시에도 전달해야 하며, 불일치하면 복호화 결과가 NULL을 반환합니다.

USE EncryptDemo;
GO

-- ① 키 오픈
EXEC dbo.usp_SymKeyOpen;

-- ② 암호화 INSERT
--    add_authenticator = 1, authenticator = CustSeq (행 고유값 바인딩)
--    → 다른 행의 암호문으로 치환 공격 방어
INSERT INTO dbo.CustBankAcc (CustName, BackAccNo)
VALUES
(
    N'홍길동',
    ENCRYPTBYKEY(
        KEY_GUID('SymKey_CustBankAcc'),   -- 대칭키 GUID
        CONVERT(VARBINARY(256), N'123-456-7890'),  -- 암호화할 평문
        1,                                -- add_authenticator ON
        CONVERT(VARBINARY, NEWID())       -- 임시: 아래 주석 참고
    )
);
GO

 

실무 패턴: INSERT 시에는 CustSeq가 아직 확정되지 않으므로, authenticator 없이 먼저 INSERT 후 UPDATE로 재암호화하거나, authenticator로 CustName 같은 불변 컬럼을 사용하는 것이 일반적입니다. 아래는 단순 예제이므로 authenticator 없이 진행합니다.

-- ① 키 오픈
EXEC dbo.usp_SymKeyOpen;

-- ② authenticator 없이 INSERT (실무에서 가장 많이 쓰는 단순 패턴)
INSERT INTO dbo.CustBankAcc (CustName, BackAccNo)
VALUES
(
    N'홍길동',
    ENCRYPTBYKEY(
        KEY_GUID('SymKey_CustBankAcc'),
        CONVERT(VARBINARY(256), N'123-456-7890')
    )
),
(
    N'김철수',
    ENCRYPTBYKEY(
        KEY_GUID('SymKey_CustBankAcc'),
        CONVERT(VARBINARY(256), N'987-654-3210')
    )
);

-- ③ 키 닫기
CLOSE SYMMETRIC KEY SymKey_CustBankAcc;
GO

-- ④ 암호화 확인 (평문 아닌 바이너리로 저장됨)
SELECT CustSeq, CustName, BackAccNo FROM dbo.CustBankAcc;

 

---

STEP 7 — 복호화 조회 Stored Procedure 생성

USE EncryptDemo;
GO

IF OBJECT_ID('dbo.usp_GetCustBankAcc', 'P') IS NOT NULL
    DROP PROCEDURE dbo.usp_GetCustBankAcc;
GO

CREATE PROCEDURE dbo.usp_GetCustBankAcc
AS
BEGIN
    SET NOCOUNT ON;

    -- 1. 키 오픈 (같은 세션 내)
    EXEC dbo.usp_SymKeyOpen;

    -- 2. 복호화 조회
    --    DECRYPTBYKEY → varbinary 반환, CONVERT로 문자열 변환
    SELECT
        CustSeq,
        CustName,
        CONVERT(NVARCHAR(200), DECRYPTBYKEY(BackAccNo)) AS BackAccNo_Plain
    FROM  dbo.CustBankAcc;

    -- 3. 키 닫기 (반드시)
    IF EXISTS (SELECT 1 FROM sys.openkeys WHERE key_name = 'SymKey_CustBankAcc')
        CLOSE SYMMETRIC KEY SymKey_CustBankAcc;
END;
GO

-- 실행 테스트
EXEC dbo.usp_GetCustBankAcc;

 

DECRYPTBYKEY는 대칭키가 열려 있지 않거나 ciphertext가 NULL이면 NULL을 반환합니다. 결과가 NULL이면 키가 닫혀있거나 다른 세션 결과입니다.

---

STEP 8 — UPDATE (암호화 값 수정)

USE EncryptDemo;
GO

-- 키 오픈
EXEC dbo.usp_SymKeyOpen;

-- 계좌번호 업데이트 (재암호화)
UPDATE dbo.CustBankAcc
SET    BackAccNo = ENCRYPTBYKEY(
                      KEY_GUID('SymKey_CustBankAcc'),
                      CONVERT(VARBINARY(256), N'111-222-3333')
                  )
WHERE  CustSeq = 1;

-- 키 닫기
CLOSE SYMMETRIC KEY SymKey_CustBankAcc;
GO

 

---

STEP 9 — 백업 (SQL Server 2022 신기능 포함)

SQL Server 2022에서는 대칭키 직접 백업(BACKUP SYMMETRIC KEY)이 새로 추가되었습니다.

USE EncryptDemo;
GO

-- [1] DMK 백업 (SMK로 자동 복호화되는 경우 OPEN 생략 가능)
BACKUP MASTER KEY
    TO FILE = 'D:\Backup\EncryptDemo_DMK.bak'
    ENCRYPTION BY PASSWORD = 'P@ssw0rd!DMK_Backup2025#';
GO

-- [2] 인증서 백업 (SQL Server 2022: PFX 포맷 지원)
--     기존 방식 (PVK)
BACKUP CERTIFICATE CertForSymKey
    TO FILE = 'D:\Backup\CertForSymKey.cer'
    WITH PRIVATE KEY (
        FILE       = 'D:\Backup\CertForSymKey.pvk',
        ENCRYPTION BY PASSWORD = 'P@ssw0rd!Cert_Backup2025#'
    );
GO

 

 

SQL Server 2022에서는 인증서와 개인 키를 PFX 포맷(.pfx)으로 단일 파일에 백업하는 기능이 추가되었습니다.

-- SQL Server 2022 신기능: PFX 형식 백업 (인증서 + 개인키 한 파일)
BACKUP CERTIFICATE CertForSymKey
    TO FILE = 'D:\Backup\CertForSymKey.pfx'
    WITH FORMAT = 'PFX',
         PRIVATE KEY (
             FILE       = 'D:\Backup\CertForSymKey.pfx',
             ENCRYPTION BY PASSWORD = 'P@ssw0rd!Cert_PFX2025#'
         );
GO

 

 

SQL Server 2022는 대칭키를 파일 또는 Azure Blob Storage로 직접 내보내고 가져오는 기능을 새로 지원합니다.

-- [3] 대칭키 직접 백업 (SQL Server 2022 신기능)
BACKUP SYMMETRIC KEY SymKey_CustBankAcc
    TO FILE = 'D:\Backup\SymKey_CustBankAcc.bak'
    ENCRYPTION BY PASSWORD = 'P@ssw0rd!SymKey_Backup2025#';
GO

 

 

 

---

STEP 10 — C# / Dapper 연동 (.NET 8)

// ✅ .NET 8 기준: Microsoft.Data.SqlClient 사용
// NuGet: Install-Package Microsoft.Data.SqlClient
// NuGet: Install-Package Dapper

using Dapper;
using Microsoft.Data.SqlClient;  // ← .NET 8에서는 반드시 이것
using System.Collections.Generic;
using System.Threading.Tasks;

public class CustBankAccDto
{
    public int    CustSeq         { get; set; }
    public string CustName        { get; set; } = string.Empty;
    public string BackAccNo_Plain { get; set; } = string.Empty;  // 복호화된 값
}

public class CustBankAccRepository
{
    private readonly string _connectionString;

    public CustBankAccRepository(string connectionString)
    {
        _connectionString = connectionString;
    }

    /// <summary>
    /// 암호화된 계좌번호 전체 조회 (Stored Procedure 방식 — 권장)
    /// </summary>
    public async Task<IEnumerable<CustBankAccDto>> GetAllAsync()
    {
        // using: 연결 종료 시 자동으로 커넥션 풀에 반환
        await using var conn = new SqlConnection(_connectionString);
        await conn.OpenAsync();

        // SP 내부에서 OPEN → SELECT → CLOSE 처리
        return await conn.QueryAsync<CustBankAccDto>(
            "dbo.usp_GetCustBankAcc",
            commandType: System.Data.CommandType.StoredProcedure
        );
    }

    /// <summary>
    /// 인라인 SQL 방식 (같은 연결에서 키 오픈 → 쿼리 → 키 닫기)
    /// 커넥션 풀 재사용 시 키가 열린 채로 남지 않도록 finally에서 반드시 CLOSE
    /// </summary>
    public async Task<IEnumerable<CustBankAccDto>> GetAllInlineAsync()
    {
        await using var conn = new SqlConnection(_connectionString);
        await conn.OpenAsync();

        try
        {
            // ① 같은 연결(세션)에서 키 오픈
            await conn.ExecuteAsync("EXEC dbo.usp_SymKeyOpen");

            // ② 복호화 조회
            const string sql = @"
                SELECT
                    CustSeq,
                    CustName,
                    CONVERT(NVARCHAR(200), DECRYPTBYKEY(BackAccNo)) AS BackAccNo_Plain
                FROM dbo.CustBankAcc;
            ";

            return await conn.QueryAsync<CustBankAccDto>(sql);
        }
        finally
        {
            // ③ 반드시 키 닫기 — 예외 발생 시에도 보장
            //    sys.openkeys로 현재 세션 열린 키 체크 후 닫기
            try
            {
                await conn.ExecuteAsync(@"
                    IF EXISTS (
                        SELECT 1 FROM sys.openkeys
                        WHERE key_name = 'SymKey_CustBankAcc'
                    )
                        CLOSE SYMMETRIC KEY SymKey_CustBankAcc;
                ");
            }
            catch
            {
                // 닫기 실패는 로그만 남기고 무시
                // (연결 자체가 끊기면 세션 종료로 키도 자동 닫힘)
            }
        }
    }

    /// <summary>
    /// 신규 계좌번호 INSERT
    /// </summary>
    public async Task InsertAsync(string custName, string backAccNo)
    {
        await using var conn = new SqlConnection(_connectionString);
        await conn.OpenAsync();

        try
        {
            await conn.ExecuteAsync("EXEC dbo.usp_SymKeyOpen");

            const string sql = @"
                INSERT INTO dbo.CustBankAcc (CustName, BackAccNo)
                VALUES (
                    @CustName,
                    ENCRYPTBYKEY(
                        KEY_GUID('SymKey_CustBankAcc'),
                        CONVERT(VARBINARY(256), @BackAccNo)
                    )
                );
            ";

            await conn.ExecuteAsync(sql, new { CustName = custName, BackAccNo = backAccNo });
        }
        finally
        {
            try
            {
                await conn.ExecuteAsync(@"
                    IF EXISTS (SELECT 1 FROM sys.openkeys WHERE key_name = 'SymKey_CustBankAcc')
                        CLOSE SYMMETRIC KEY SymKey_CustBankAcc;
                ");
            }
            catch { }
        }
    }
}

 

 

 

DI 등록 (Program.cs):

// appsettings.json의 연결 문자열
builder.Services.AddScoped<CustBankAccRepository>(sp =>
{
    var connStr = builder.Configuration.GetConnectionString("DefaultConnection")!;
    return new CustBankAccRepository(connStr);
});

 

 

appsettings.json:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=YOUR_SERVER;Database=EncryptDemo;User Id=YOUR_ID;Password=YOUR_PW;TrustServerCertificate=True;"
  }
}

 

 

 

---

STEP 11 — 검증 쿼리 모음

USE EncryptDemo;
GO

-- [1] 암호화 계층 전체 확인
SELECT name, key_algorithm, key_length, create_date
FROM   sys.symmetric_keys;

SELECT name, subject, expiry_date, pvt_key_encryption_type_desc
FROM   sys.certificates;

-- [2] 현재 세션에서 열린 키 확인
SELECT key_name, algorithm, key_length
FROM   sys.openkeys;

-- [3] 복호화 동작 단위 테스트
EXEC dbo.usp_SymKeyOpen;

SELECT
    CustSeq,
    CustName,
    BackAccNo                                                   AS 암호화값,
    CONVERT(NVARCHAR(200), DECRYPTBYKEY(BackAccNo))             AS 복호화값,
    DATALENGTH(BackAccNo)                                       AS 암호화바이트수
FROM dbo.CustBankAcc;

CLOSE SYMMETRIC KEY SymKey_CustBankAcc;

-- [4] 키 닫힌 상태에서 복호화 시도 → NULL 반환 확인
SELECT
    CustSeq,
    CONVERT(NVARCHAR(200), DECRYPTBYKEY(BackAccNo)) AS 반드시NULL이어야함
FROM dbo.CustBankAcc;

 

---

STEP 12 — 전체 정리 및 실무 주의사항

항목	내용
컬럼 타입	VARBINARY(256) 권장 (함수 최대 반환값 8,000바이트)
알고리즘	AES_256 사용 (RC4/RC4_128/DESX 사용 금지)
키 오픈 범위	같은 세션(연결) 내에서만 유효
커넥션 풀	finally에서 반드시 CLOSE 보장 (sys.openkeys 체크 후)
NULL 반환	키가 닫힌 상태에서 DECRYPTBYKEY는 NULL 반환 (에러 아님)
VIEW에서	OPEN SYMMETRIC KEY 불가, SP 또는 호출자에서 처리
백업 순서	DMK → 인증서 → 대칭키 순으로 백업, 별도 안전한 위치에 보관
SQL 2022 신기능	BACKUP SYMMETRIC KEY 직접 지원, 인증서 PFX 포맷 백업
.NET 8 패키지	Microsoft.Data.SqlClient (구 System.Data.SqlClient 사용 금지)
인증서 만료	EXPIRY_DATE 명시 권장, 만료돼도 암/복호화 동작은 유지됨