SQL Server 암호화/복호화

이전 직장에서 근무하던 동료에게 전화가 왔다.

 

DB에 새로운 테이블을 만들고 데이터를 암호화시켜 저장해놨는데 복호화하면 결과값이 null이 나온다는 거다.

 

그쪽 DB에 접속해서 조회를 해봤는데 정말로 null이 나온다.

OPEN SYMMETRIC KEY keyValue
DECRYPTION BY CERTIFICATE certValue;

select passwd, CAST(DECRYPTBYKEY(passwd) AS VARCHAR)
from userTable;

null인 결과값

별짓을 해도 조회가 안되고 계속 null만 나오는 거다.

이것저것 해보다 처음부터 한번 점검해보자 하고 컬럼 정의를 봤는데 binary로 되어 있다.

이걸 varbinary로 바꾸고 데이터를 입력한 다음 조회하니 정상적으로 나온다.

정상적인 결과값

Microsoft Learn에 보면 아래와 같이 정의되어 있다. (ENCRYPTBYKEY(Transact-SQL) - SQL Server | Microsoft Learn)

반환 형식

최대 크기가 8,000바이트인 varbinary입니다.

키가 열리지 않거나, 존재하지 않거나, 더 이상 사용되지 않는 RC4 키이며 데이터베이스 호환성 수준이 110 이상이 아닐 경우 Null을 반환합니다.

cleartext 값이 NULL이면 NULL을 반환합니다.