SQL Server 2022 SQL 샘플과 암호화/복호화(ENCRYPTBYKEY / DECRYPTBYKEY)

 

 

 

SQL 샘플과 암호화/복호화(ENCRYPTBYKEY / DECRYPTBYKEY) 예제, 그리고 실무에서 흔히 쓰는 Stored Procedure + Dapper 예제 까지 한 번에 알아보죠.. 실무 관점에서 권한·커넥션풀링·보안 주의사항도 같이 알아봅시다.

---

STEP 0 — 전제 / 권한

• 작업은 DB 소유자(DBO) 또는 충분한 권한(데이터베이스 마스터키 생성, 인증서/대칭키 생성 권한)이 있는 계정에서 수행하세요.
• SQL Server 2022 환경에서 동작합니다.
• 커넥션 풀 사용 시 OPEN SYMMETRIC KEY는 세션(연결) 기준임을 유의하세요. (같은 연결에서 open → select → close 순으로 처리해야 합니다.)

---

STEP 1 — 샘플 테이블 준비

먼저 예시 테이블을 만듭니다. 암호화 대상 컬럼은 varbinary로 저장합니다.

USE YourDatabase;
GO

IF OBJECT_ID('dbo.Tables','U') IS NOT NULL DROP TABLE dbo.Tables;
CREATE TABLE dbo.Tables
(
    CustSeq INT IDENTITY(1,1) PRIMARY KEY,
    CustName NVARCHAR(200),
    BackAccNo VARBINARY(MAX) -- 암호화된 값 저장
);
GO

 

 

---

STEP 2 — Database Master Key (DBMK) 생성 (없으면)

데이터베이스 마스터 키가 없으면 만듭니다. 이미 있으면 이 스텝은 건너뜁니다.

-- DB Master Key 생성 (한 번만)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongDBMasterKey!2025';
GO

 

 

실제 환경에서는 강력한 비밀번호를 사용하고, 키 관리를 정책에 따라 하세요.

---

STEP 3 — 인증서 또는 비대칭키 생성

대칭키를 보호(암호화)하기 위한 인증서를 만듭니다.

-- 인증서 생성 (대칭키를 보호하는데 사용)
CREATE CERTIFICATE MyCertificate
    WITH SUBJECT = 'Certificate for symmetric key encryption';
GO

 

 

---

STEP 4 — Symmetric Key 생성

데이터 암호화/복호화에 사용할 대칭키 생성:

CREATE SYMMETRIC KEY MySymmetricKey
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE MyCertificate;
GO

 

 

---

STEP 5 — _SYMMETRICKeyOpen 같은 프로시저(래퍼) 만들기

실무에서는 OPEN SYMMETRIC KEY ...를 래핑한 저장 프로시저를 만듭니다. 내부에서 OPEN SYMMETRIC KEY만 수행하도록 하세요(그리고 호출자는 닫기까지 책임지는 패턴).

 

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

CREATE PROCEDURE dbo._SYMMETRICKeyOpen
AS
BEGIN
    SET NOCOUNT ON;
    -- 인증서(또는 비밀번호)로 대칭키 열기
    OPEN SYMMETRIC KEY MySymmetricKey
    DECRYPTION BY CERTIFICATE MyCertificate;
END
GO

 

 

필요하면 프로시저에 파라미터(예: certificate name)로 유연성을 둘 수 있습니다.

---

STEP 6 — 암호화(데이터 저장) 예제

대칭키를 열고 ENCRYPTBYKEY로 값을 암호화해서 저장합니다.

-- 대칭키 오픈
EXEC dbo._SYMMETRICKeyOpen;

-- 암호화하여 삽입
INSERT INTO dbo.Tables (CustName, BackAccNo)
VALUES
('회사A', ENCRYPTBYKEY(KEY_GUID('MySymmetricKey'), CONVERT(VARBINARY(MAX), '123-456-7890'))),
('회사B', ENCRYPTBYKEY(KEY_GUID('MySymmetricKey'), CONVERT(VARBINARY(MAX), '987-654-3210')));

-- 키 닫기
CLOSE SYMMETRIC KEY MySymmetricKey;
GO

 

 

ENCRYPTBYKEY 내부에서 두 번째 인자에 문자열을 바로 넣을 수 있지만, 안전하게 CONVERT(VARBINARY, '...')를 사용하는 것이 일반적입니다.

---

STEP 7 — 복호화(조회) — 순서 중요

복호화는 같은 세션에서 OPEN SYMMETRIC KEY 후 DECRYPTBYKEY()를 호출해야 합니다.

 

방법 A: Stored Procedure로 묶기 (권장 — 키 오픈/조회/닫기 일괄)

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

CREATE PROCEDURE dbo.usp_GetTables
AS
BEGIN
    SET NOCOUNT ON;

    -- 1. 키 오픈 (다른 DB의 프로시저를 호출하는 경우 EXEC이 가능하도록 스키마/DB 지정)
    EXEC dbo._SYMMETRICKeyOpen;

    -- 2. 복호화 조회
    SELECT 
        CustSeq,
        CustName,
        CONVERT(NVARCHAR(1000), DECRYPTBYKEY(BackAccNo)) AS BackAccNo
    FROM dbo.Tables;

    -- 3. 키 닫기
    CLOSE SYMMETRIC KEY MySymmetricKey;
END
GO

 

사용:

EXEC dbo.usp_GetCustBankAcc;

 

 

방법 B: 애플리케이션 레이어에서 제어 (Dapper)

앱에서 한 연결(same DB connection)을 열고 EXEC _SYMMETRICKeyOpen, SELECT, CLOSE SYMMETRIC KEY 순으로 실행합니다. (아래에 C# 샘플 포함)

---

STEP 8 — TVF/VIEW 관련 주의

• VIEW 내부에는 EXEC나 OPEN SYMMETRIC KEY 등을 넣을 수 없으므로, 뷰 안에서 자동으로 복호화할 수 없습니다.
• 인라인 TVF는 SELECT 문을 캡슐화하지만, 키 오픈은 TVF 밖(호출자)에서 처리해야 합니다.

---

STEP 9 — 실무 유의사항 (요약)

• 권한: 인증서 생성, 대칭키 생성은 권한이 필요합니다. 운영 DB에서는 DBA 정책에 따르세요.
• 커넥션풀링: OPEN SYMMETRIC KEY는 연결 세션에 종속됩니다. 커넥션 풀에서 재사용될 때 의도치 않게 키가 열린 채로 남아있을 수 있으니, 반드시 CLOSE SYMMETRIC KEY를 호출하세요.
• 예외 처리: 애플리케이션 코드에서 예외 발생 시 finally 블록에서 키 닫기 호출을 보장하세요.
• 로그/감사: 민감데이터 접근 로그를 남기도록 정책을 마련하세요.
• 성능: DECRYPT 연산은 비용이 있어 대량 조회 시 성능 영향이 있음. 가능한 필터/페이징 적용.

---

STEP 10 — Dapper(C#) 실전 예제

아래는 ASP.NET Core(혹은 콘솔)에서 Dapper로 동일 연결 안에서 키 열기 → 쿼리 → 키 닫기 패턴입니다.

using System.Data.SqlClient;
using Dapper;
using System.Threading.Tasks;
using System.Collections.Generic;

public class CustBankAccDto
{
    public int CustSeq { get; set; }
    public string CustName { get; set; }
    public string BackAccNo { get; set; }
}

public async Task<IEnumerable<CustBankAccDto>> GetCustBankAccsAsync(string connectionString)
{
    using var conn = new SqlConnection(connectionString);
    await conn.OpenAsync();

    try
    {
        // 1) 키 오픈 (동일 연결)
        await conn.ExecuteAsync("EXEC dbo._SYMMETRICKeyOpen");

        // 2) 복호화 조회
        var sql = @"
            SELECT 
                CustSeq,
                CustName,
                CONVERT(NVARCHAR(1000), DECRYPTBYKEY(BackAccNo)) AS BackAccNo
            FROM dbo.Tables;
        ";

        var result = await conn.QueryAsync<CustBankAccDto>(sql);
        return result;
    }
    finally
    {
        // 3) 키 닫기 (반드시)
        try
        {
            await conn.ExecuteAsync("IF EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = 'MySymmetricKey') CLOSE SYMMETRIC KEY MySymmetricKey;");
        }
        catch
        {
            // 닫기 실패는 로그만 남기고 무시하거나 에러 처리
        }
    }
}

 

• 주의: 위 예제는 키 이름이 고정 MySymmetricKey인 경우입니다. 보안 정책에 따라 키 이름/관리 방법을 조정하세요.

---

STEP 11 — 키 재생성/백업/복원 팁

• 인증서/대칭키 삭제 전에 백업하세요. 인증서를 삭제하면 대칭키를 암호 해독할 수 없게 됩니다.
• 인증서 백업:

BACKUP CERTIFICATE MyCertificate TO FILE = 'C:\backup\MyCert.cer' 
WITH PRIVATE KEY (
    FILE = 'C:\backup\MyCert.pvk',
    ENCRYPTION BY PASSWORD = 'StrongBackupPassword!2025'
);

 

---

STEP 12 — 전체 흐름 요약 (한눈에)

1. DBMK(없으면) 생성 → 2. 인증서 생성 → 3. 대칭키 생성(인증서로 보호) → 4. _SYMMETRICKeyOpen 같은 프로시저 만들기 → 5. 암호화: ENCRYPTBYKEY → 6. 복호화: OPEN SYMMETRIC KEY → DECRYPTBYKEY() → CLOSE SYMMETRIC KEY → 7. 앱(Dapper) 또는 Stored Proc에서 일괄 처리

---

 

---

 

개발자 입장에서는 “동작 원리 + 데이터 흐름”만 다시 한번 이해해 보죠.

---

🔍 1. 기본 개념

✅ 대칭키 암호화 (Symmetric Encryption)

“대칭”이라는 말 그대로 암호화와 복호화에 같은 키를 사용합니다.

• ENCRYPTBYKEY() → 데이터를 암호화할 때 사용
• DECRYPTBYKEY() → 암호화된 데이터를 복호화할 때 사용
• 둘 다 “같은 대칭키(Symmetric Key)” 가 열려 있어야 작동합니다.

즉,

암호화: 평문 → ENCRYPTBYKEY → 암호문
복호화: 암호문 → DECRYPTBYKEY → 평문

 

---

🔐 2. 구조적 이해 (단계별 흐름)

① 대칭키 생성

먼저 대칭키(Symmetric Key) 는 실제 데이터를 암호화/복호화하는 실질적인 키입니다.

CREATE SYMMETRIC KEY MySymmetricKey
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE MyCertificate;

 

• AES_256 : 매우 강력한 암호화 알고리즘 (Advanced Encryption Standard 256-bit)
• ENCRYPTION BY CERTIFICATE : 키를 보호하기 위해 인증서로 한 번 더 암호화
  (즉, 키 자체도 평문으로 저장되지 않음)

---

② 키 열기 (OPEN SYMMETRIC KEY)

SQL Server는 “세션 기반 암호화”를 사용합니다.

OPEN SYMMETRIC KEY MySymmetricKey
DECRYPTION BY CERTIFICATE MyCertificate;

 

이 명령은 SQL Server 내부적으로:

1. 인증서를 이용해 DB에 저장된 암호화된 대칭키를 복호화
2. 현재 세션(Session)의 메모리 영역에 해당 키를 임시로 로드

💡 즉, 키는 실제로 DB 테이블에 저장되어 있지만, OPEN SYMMETRIC KEY 실행 시 현재 세션에만 “활성화된 상태”로 올라옵니다.

---

③ 데이터 암호화 (ENCRYPTBYKEY)

이제 키가 열린 상태에서 데이터를 암호화할 수 있습니다.

INSERT INTO CustBankAcc (CustName, BackAccNo)
VALUES ('홍길동', ENCRYPTBYKEY(KEY_GUID('MySymmetricKey'), '123-456-789012'));

 

내부 동작 원리:

1. KEY_GUID('MySymmetricKey') → 대칭키의 GUID를 찾음
2. SQL Server 내부 AES-256 엔진이 실행되어 평문 '123-456-789012'를 암호화
3. 결과는 varbinary 형태(이진 데이터)로 반환되어 DB에 저장됨

이 시점에서 DB에는 “읽어도 알아볼 수 없는 암호화된 바이트 값”이 들어갑니다.

예시 (실제 DB에 저장된 값 예):

0x00A7F9D4B6D23E117CF84A... (길고 복잡한 바이트)

 

 

---

④ 데이터 복호화 (DECRYPTBYKEY)

데이터를 조회할 때는 대칭키가 열린 상태에서 복호화 함수를 사용합니다.

OPEN SYMMETRIC KEY MySymmetricKey
DECRYPTION BY CERTIFICATE MyCertificate;

SELECT CONVERT(NVARCHAR(1000), DECRYPTBYKEY(BackAccNo))
FROM CustBankAcc;

 

 

내부 동작 원리:

1. 현재 세션에 열린 키를 사용해 BackAccNo 값을 복호화
2. 평문(예: '123-456-789012')을 반환
3. CONVERT(NVARCHAR)로 문자열로 변환하여 읽을 수 있도록 함

⚠️ 키가 닫혀 있으면 DECRYPTBYKEY() 결과는 NULL을 반환합니다.
이는 보안적 설계로, “키가 없으면 평문을 얻을 수 없다”는 원칙을 강제합니다.

---

⑤ 키 닫기 (CLOSE SYMMETRIC KEY)

복호화가 끝나면 키를 반드시 닫습니다.

CLOSE SYMMETRIC KEY MySymmetricKey;

 

 

이로써 세션의 메모리에서 키가 제거되고, 더 이상 복호화가 불가능해집니다.

---

🧠 3. 요약 그림 (개념적으로)

                 ┌──────────────────────────┐
                 │ Database (암호화된 데이터) │
                 └───────────┬──────────────┘
                             │
            ┌───────────────▼───────────────┐
            │  OPEN SYMMETRIC KEY 실행        │
            │  인증서로 대칭키 복호화 (세션에 로드) │
            └───────────────┬───────────────┘
                             │
        ┌────────────────────▼──────────────────┐
        │ ENCRYPTBYKEY() → 평문→암호문 저장       │
        │ DECRYPTBYKEY() → 암호문→평문 복원       │
        └────────────────────┬──────────────────┘
                             │
                     ┌───────▼────────┐
                     │ CLOSE KEY (메모리 제거) │
                     └────────────────┘

---

⚙️ 4. 실무에서 알아야 할 포인트

구분 설명

🔒 세션 기반	OPEN SYMMETRIC KEY는 “현재 세션(커넥션)”에만 유효
🧱 알고리즘	AES_256, AES_192, AES_128, Triple_DES 등 지원
💾 데이터형	varbinary 로 저장, 복호화 시 nvarchar로 변환 필요
⚠️ NULL 반환	키가 닫혀 있거나 없는 세션에서는 DECRYPTBYKEY() → NULL
🔐 중첩 보호	대칭키는 인증서로 보호, 인증서는 DB Master Key로 보호
🧰 성능 주의	대량 복호화 시 CPU 부하가 있음 (AES는 CPU 집약적)

---

🧩 5. 간단한 실습 예제 (요약)

-- 1. 대칭키 열기
OPEN SYMMETRIC KEY MySymmetricKey
DECRYPTION BY CERTIFICATE MyCertificate;

-- 2. 암호화 예제
DECLARE @Encrypted VARBINARY(MAX);
SET @Encrypted = ENCRYPTBYKEY(KEY_GUID('MySymmetricKey'), 'HelloWorld!');
SELECT @Encrypted AS EncryptedValue;

-- 3. 복호화 예제
SELECT CONVERT(NVARCHAR(100), DECRYPTBYKEY(@Encrypted)) AS DecryptedValue;

-- 4. 키 닫기
CLOSE SYMMETRIC KEY MySymmetricKey;

 

결과 예시:

EncryptedValue: 0x007000A3F9D45B16...
DecryptedValue: HelloWorld!

 

 

---

✅ 정리

단계 명령 설명

1	CREATE SYMMETRIC KEY	암호화 키 생성 (AES 등 알고리즘 지정)
2	OPEN SYMMETRIC KEY	세션에서 키 활성화
3	ENCRYPTBYKEY()	평문 → 암호문
4	DECRYPTBYKEY()	암호문 → 평문
5	CLOSE SYMMETRIC KEY	세션에서 키 닫기

---