SQL Server 데이터의 암호화와 복호화

SQL Server에서 데이터의 암호화와 복호화에 대해서 검증한 내용을 정리해 보았습니다.

0. 전제조건

• 대상의 SQL Server은 최신 버젼은 아님.
• DB단위의 인증이라거나 키는 가급적 사용하지 않고 어플 단위로 암호화와 복호화를 하고 싶음.
• 개발엔 Entity Framework를 사용예정. 하지만 SQL 직접 실행도 OK.
• 암호화 대상은 영어, 일본어를 기본으로 어느 나라 언어가 올지는 아무도 모름.
• DB기반이 아닌 암호화와 복호화도 조사해 보았으나 일본어의 한자 내용에 따라서 복호화시에 문자가 깨지는 상황이 자주 발생했기 때문에 DB에서 지원하는 암호화/복호화 기능을 사용하기로 함.

1. 검증용 DB작성(localDB)

2. 검증용 테이블 작성

USE [TESTDB01]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Table_1](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](500) NULL,
	[NameEnc] [varbinary](2000) NULL
) ON [PRIMARY]

GO

※암호화 한 데이터의 datatype은 varbinary。

3. 각종 언어를 사용해서 테스트용 데이터를 작성

INSERT문은 반드시 N을 붙여서 nvarchar로 등록함.

INSERT INTO [dbo].[Table_1]([Name]) VALUES (N'Name');

※datatype을 nvarchar로 등록하지 않으면 Char로 등록되어서 “???”같이 내용이 깨진다.

4. 암호화

※’ENCRYPT’ <- 여기에 임의의 암호키를 설정

UPDATE [dbo].[Table_1] SET [NameEnc] = ENCRYPTBYPASSPHRASE('ENCRYPT',Name);

※Name의 datatype이 nvarchar이기 때문에 ENCRYPTBYPASSPHRASE에 의해 nvarchar의 속성으로 암호화 된다.

문자열을 직접 입력해서 암호화 하고 싶은 경우엔 N’…’ <- 와 같이 설정한다.

UPDATE [dbo].[Table_1] SET [NameEnc] = ENCRYPTBYPASSPHRASE('ENCRYPT',N'암호화 하고 싶은 내용');

5. 복호화

※’ENCRYPT’ <- 여기에 4에서 설정한 암호키를 설정

SELECT [ID], [Name], [NameEnc]
  , CAST(DECRYPTBYPASSPHRASE('ENCRYPT',NameEnc) AS nvarchar(2000)) AS NameDec
FROM [TESTDB01].[dbo].[Table_1]

※복호화 한 다음 nvarchar 변환처리를 넣는다.

이상

보충 ： MySQL의 경우

암호화

CAST(HEX(AES_ENCRYPT('암호화 할 데이터', '암호key')) AS CHAR)

복호화

CAST(AES_DECRYPT(UNHEX('복호화 할 데이터'), '암호Key') AS CHAR)

Pie's Tech Note

생계형 개발자의 메모장