AES, SHA 암호화 4, T-SQL

MS-SQL  내부에서 AES, SHA 암호화를 진행하겠습니다.

C# 코드로 DLL만들고 이걸 시퀄서버에 등록해서 펑션화 하겠습니다.

DLL을 만들기 위해 클래스 라이브러리 프로젝트를 생성하고.. 다음 코드로 빌드하여 DLL을 생성합니다.

using System;
using System.Data.SqlTypes;
using System.IO;
using System.Security.Cryptography;
using System.Text;
 
///<summary>
///Crypto, 암복호화 유틸 클래스, MS-SQL에 ASSEMBLY로 등록해서 사용
///</summary>
///<remarks>
///author Francis Lee
///since 2015. 8. 21.
///version 1.0
///</remarks>
public partial class Crypto
{
    // 키
    private static readonly string KEY = "01234567890123456789012345678901";
 
    //128bit (16자리)
    private static readonly string KEY_128 = KEY.Substring(0, 128 / 8);
 
    //256bit (32자리)
    private static readonly string KEY_256 = KEY.Substring(0, 256 / 8);
 
    //AES 128 암호화.., CBC, PKCS7, 예외발생하면 null
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString encryptAES128(SqlString plain)
    {
        try
        {
            string plainString = plain.ToString();
            //바이트로 변환 
            byte[] plainBytes = Encoding.UTF8.GetBytes(plainString);
 
            //레인달 알고리듬
            RijndaelManaged rm = new RijndaelManaged();
            //자바에서 사용한 운용모드와 패딩방법 일치시킴(AES/CBC/PKCS5Padding)
            rm.Mode = CipherMode.CBC;
            rm.Padding = PaddingMode.PKCS7;
            rm.KeySize = 128;
 
            //메모리스트림 생성
            MemoryStream memoryStream = new MemoryStream();
 
            //key, iv값 정의
            ICryptoTransform encryptor = rm.CreateEncryptor(Encoding.UTF8.GetBytes(KEY_128), Encoding.UTF8.GetBytes(KEY_128));
            //크립토스트림을 키와 IV값으로 메모리스트림을 이용하여 생성
            CryptoStream cryptoStream = new CryptoStream(memoryStream, encryptor, CryptoStreamMode.Write);
 
            //크립트스트림에 바이트배열을 쓰고 플러시..
            cryptoStream.Write(plainBytes, 0, plainBytes.Length);
            cryptoStream.FlushFinalBlock();
 
            //메모리스트림에 담겨있는 암호화된 바이트배열을 담음
            byte[] encryptBytes = memoryStream.ToArray();
 
            //베이스64로 변환
            string encryptString = Convert.ToBase64String(encryptBytes);
 
            //스트림 닫기.
            cryptoStream.Close();
            memoryStream.Close();
 
            return new SqlString(encryptString);
        }
        catch (Exception)
        {
            return null;
        }
    }
 
    //AES128 복호화.., CBC, PKCS7, 예외발생하면 null
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString decryptAES128(SqlString encrypt)
    {
        try
        {
            //SqlString을 string으로
            string encryptString = encrypt.ToString();
            //base64를 바이트로 변환 
            byte[] encryptBytes = Convert.FromBase64String(encryptString);
            //byte[] encryptBytes = Encoding.UTF8.GetBytes(encryptString);
 
            //레인달 알고리듬
            RijndaelManaged rm = new RijndaelManaged();
            //자바에서 사용한 운용모드와 패딩방법 일치시킴(AES/CBC/PKCS5Padding)
            rm.Mode = CipherMode.CBC;
            rm.Padding = PaddingMode.PKCS7;
            rm.KeySize = 128;
 
            //메모리스트림 생성
            MemoryStream memoryStream = new MemoryStream(encryptBytes);
 
            //key, iv값 정의
            ICryptoTransform decryptor = rm.CreateDecryptor(Encoding.UTF8.GetBytes(KEY_128), Encoding.UTF8.GetBytes(KEY_128));
            //크립토스트림을 키와 IV값으로 메모리스트림을 이용하여 생성
            CryptoStream cryptoStream = new CryptoStream(memoryStream, decryptor, CryptoStreamMode.Read);
 
            //복호화된 데이터를 담을 바이트 배열을 선언한다. 
            byte[] plainBytes = new byte[encryptBytes.Length];
 
            int plainCount = cryptoStream.Read(plainBytes, 0, plainBytes.Length);
 
            //복호화된 바이트 배열을 string으로 변환
            string plainString = Encoding.UTF8.GetString(plainBytes, 0, plainCount);
 
            //스트림 닫기.
            cryptoStream.Close();
            memoryStream.Close();
 
            return new SqlString(plainString);
        }
        catch (Exception)
        {
            return null;
        }
    }
 
    //AES 256 암호화.., CBC, PKCS7, 예외발생하면 null
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString encryptAES256(SqlString plain)
    {
        try
        {
            string plainString = plain.ToString();
            //바이트로 변환 
            byte[] plainBytes = Encoding.UTF8.GetBytes(plainString);
 
            //레인달 알고리듬
            RijndaelManaged rm = new RijndaelManaged();
            //자바에서 사용한 운용모드와 패딩방법 일치시킴(AES/CBC/PKCS5Padding)
            rm.Mode = CipherMode.CBC;
            rm.Padding = PaddingMode.PKCS7;
            rm.KeySize = 256;
 
            //메모리스트림 생성
            MemoryStream memoryStream = new MemoryStream();
 
            //key, iv값 정의
            ICryptoTransform encryptor = rm.CreateEncryptor(Encoding.UTF8.GetBytes(KEY_256), Encoding.UTF8.GetBytes(KEY_128));
            //크립토스트림을 키와 IV값으로 메모리스트림을 이용하여 생성
            CryptoStream cryptoStream = new CryptoStream(memoryStream, encryptor, CryptoStreamMode.Write);
 
            //크립트스트림에 바이트배열을 쓰고 플러시..
            cryptoStream.Write(plainBytes, 0, plainBytes.Length);
            cryptoStream.FlushFinalBlock();
 
            //메모리스트림에 담겨있는 암호화된 바이트배열을 담음
            byte[] encryptBytes = memoryStream.ToArray();
 
            //베이스64로 변환
            string encryptString = Convert.ToBase64String(encryptBytes);
 
            //스트림 닫기.
            cryptoStream.Close();
            memoryStream.Close();
 
            return new SqlString(encryptString);
        }
        catch (Exception)
        {
            return null;
        }
    }
 
    //AES256 복호화.., CBC, PKCS7, 예외발생하면 null
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString decryptAES256(SqlString encrypt)
    {
        try
        {
            //SqlString을 string으로
            string encryptString = encrypt.ToString();
            //base64를 바이트로 변환 
            byte[] encryptBytes = Convert.FromBase64String(encryptString);
            //byte[] encryptBytes = Encoding.UTF8.GetBytes(encryptString);
 
            //레인달 알고리듬
            RijndaelManaged rm = new RijndaelManaged();
            //자바에서 사용한 운용모드와 패딩방법 일치시킴(AES/CBC/PKCS5Padding)
            rm.Mode = CipherMode.CBC;
            rm.Padding = PaddingMode.PKCS7;
            rm.KeySize = 256;
 
            //메모리스트림 생성
            MemoryStream memoryStream = new MemoryStream(encryptBytes);
 
            //key, iv값 정의
            ICryptoTransform decryptor = rm.CreateDecryptor(Encoding.UTF8.GetBytes(KEY_256), Encoding.UTF8.GetBytes(KEY_128));
            //크립토스트림을 키와 IV값으로 메모리스트림을 이용하여 생성
            CryptoStream cryptoStream = new CryptoStream(memoryStream, decryptor, CryptoStreamMode.Read);
 
            //복호화된 데이터를 담을 바이트 배열을 선언한다. 
            byte[] plainBytes = new byte[encryptBytes.Length];
 
            int plainCount = cryptoStream.Read(plainBytes, 0, plainBytes.Length);
 
            //복호화된 바이트 배열을 string으로 변환
            string plainString = Encoding.UTF8.GetString(plainBytes, 0, plainCount);
 
            //스트림 닫기.
            cryptoStream.Close();
            memoryStream.Close();
 
            return new SqlString(plainString);
        }
        catch (Exception)
        {
            return null;
        }
    }
 
    //SHA256 해쉬 함수 암호화.., 예외발생하면 null
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString encryptSHA256(SqlString plain)
    {
        try
        {
            string plainString = plain.ToString();
            //바이트로 변환 
            byte[] plainBytes = Encoding.UTF8.GetBytes(plainString);
 
            SHA256Managed sm = new SHA256Managed();
 
            byte[] encryptBytes = sm.ComputeHash(plainBytes);
 
            //hex.. 16진수
            //string encryptString = BitConverter.ToString(encryptBytes).Replace("-", "").ToLower();
 
            //base64
            string encryptString = Convert.ToBase64String(encryptBytes);
 
            return new SqlString(encryptString);
        }
        catch (Exception)
        {
            return null;
        }
    }
}

프로젝트를 생성할때 다음 쿼리를 통해 .net framework의 버전을 확인후 해당 버전을 선택해야 됩니다.

SELECT * FROM sys.dm_clr_properties;

빌드 후 생성된 DLL파일(bin\Debug경로에 존재)을 MS-SQL이 설치되어 있는 서버에 복사하고.. 다음 쿼리를 실행합니다.

USE master;
EXEC sp_configure
     'clr enabled',
     1;
GO
RECONFIGURE;
 
GO
USE 해당데이터베이스;
 
--삭제, dll 갱신시 사용
DROP FUNCTION FN_ENCRYPT_AES128;
DROP FUNCTION FN_DECRYPT_AES128;
DROP FUNCTION FN_ENCRYPT_AES256;
DROP FUNCTION FN_DECRYPT_AES256;
DROP FUNCTION FN_ENCRYPT_SHA256;
DROP ASSEMBLY Crypto;
GO
 
--dll 등록
CREATE ASSEMBLY Crypto FROM 'D:\dll\Crypto.dll' WITH PERMISSION_SET = SAFE;
GO 
 
 
--함수 생성 [dll명].[클래스명].[함수명]
CREATE FUNCTION FN_ENCRYPT_AES128( @plain NVARCHAR(100))
RETURNS NVARCHAR(100)
AS
     EXTERNAL NAME
    Crypto.Crypto.encryptAES128;
GO
CREATE FUNCTION FN_DECRYPT_AES128( @encrypted NVARCHAR(100))
RETURNS NVARCHAR(100)
AS
     EXTERNAL NAME
    Crypto.Crypto.decryptAES128;
GO
CREATE FUNCTION FN_ENCRYPT_AES256( @plain NVARCHAR(100))
RETURNS NVARCHAR(100)
AS
     EXTERNAL NAME
    Crypto.Crypto.encryptAES256;
GO
CREATE FUNCTION FN_DECRYPT_AES256( @encrypted NVARCHAR(100))
RETURNS NVARCHAR(100)
AS
     EXTERNAL NAME
    Crypto.Crypto.decryptAES256;
GO
CREATE FUNCTION FN_ENCRYPT_SHA256( @plain NVARCHAR(100))
RETURNS NVARCHAR(100)
AS
     EXTERNAL NAME
    Crypto.Crypto.encryptSHA256;
GO

다음 쿼리로 결과를 확인합니다.

DECLARE @str1 VARCHAR(1000) = '암호화되지 않은 문자',
        @str2 VARCHAR(1000),
        @str3 VARCHAR(1000),
        @str4 VARCHAR(1000),
        @str5 VARCHAR(1000),
        @str6 VARCHAR(1000);
 
PRINT( 'plain : ' + @str1 );
 
SET @str2 = dbo.FN_ENCRYPT_AES128( @str1 );
PRINT( 'AES128 encrypted : ' + @str2 );
 
SET @str3 = dbo.FN_DECRYPT_AES128( @str2 );
PRINT( 'AES128 decrypted : ' + @str3 );
 
SET @str4 = dbo.FN_ENCRYPT_AES256( @str1 );
PRINT( 'AES256 encrypted : ' + @str4 );
 
SET @str5 = dbo.FN_deCRYPT_AES256( @str4 );
PRINT( 'AES256 decrypted : ' + @str5 );
 
SET @str6 = dbo.FN_ENCRYPT_SHA256( @str1 );
PRINT( 'SHA256 encrypted : ' + @str6 );

실행결과

 

시퀄서버 내부에서 다음과 같이 암호화를 할 수 있는데 알고리즘에 대한 운용방식, 패딩, iv값등을 어떻게 제어하는지는 모르겠습니다. 데이터베이스 내부에서만 암호화를 진행한다면 간단하게 사용할 수 있습니다. iv값이 바뀌는지 암호화의 결과는 항상 틀립니다. 또한 해쉬함수의 경우 HashBytes란 펑션을 제공하나 SHA256은 2012버전 이상에서만 사용가능합니다.

/*
1. 서버 마스터 키 확인
서버 마스터 키는 SQL Server 암호화 계층의 루트 이다. 이는 인스턴스 생성시 생성된다. 
아래 쿼리를 사용하여 마스터키 확인이되지 않는 다면 수동으로 생성 해야 한다.
*/
 
SELECT *
  FROM master.sys.symmetric_keys
  WHERE name = '##MS_ServiceMasterKey##';
 
/*
2. 마스터 키 생성
이 키는 마스터키는 데이터를암호화 할 때 사용되는 암호를 정의 한다.
*/
 
-- 키 확인
SELECT *
  FROM sys.symmetric_keys;
-- 생성
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '0123456789012345';
-- 키 확인
SELECT *
  FROM sys.symmetric_keys;
-- 마스터 키 삭제
--DROP MASTER KEY
 
/*
3. 인증서 생성 
인증서는 SQL Server의 공개키를 포함한 디지털 서명 보안 개체 이다.
*/
 
-- 인증서 확인
SELECT *
  FROM sys.certificates;
 
-- 인증서 생성
CREATE CERTIFICATE CerificateTest WITH SUBJECT = '테스트 인증서',EXPIRY_DATE = '12/31/2050';
 
-- 인증서 확인
SELECT *
  FROM sys.certificates;
 
-- 인증서 삭제
--DROP CERTIFICATE CerificateTest
 
/*
4. 대칭키를 생성
대칭키를사용하여 암호화 및 암호화 해독을 진행 한다.
*/
 
-- 키 확인
SELECT *
  FROM sys.symmetric_keys;
 
-- 키 생성
CREATE SYMMETRIC KEY SymmetricTest WITH ALGORITHM = AES_128 ENCRYPTION BY CERTIFICATE CerificateTest;
 
-- 키 확인
SELECT *
  FROM sys.symmetric_keys;
-- 키 삭제
--DROP SYMMETRIC KEY SymmetricTest
 
/*
5. 암호화
*/
 
OPEN SYMMETRIC KEY SymmetricTest DECRYPTION BY CERTIFICATE CerificateTest;
GO
DECLARE @decryptedChar VARCHAR( MAX ),
        @encryptedBinary VARBINARY( MAX ),
        @encryptedChar VARCHAR( MAX );
 
--평문
SET @decryptedChar = '암호화되지 않은 문자';
--암호화 바이너리
SET @encryptedBinary = ENCRYPTBYKEY( KEY_GUID( 'SymmetricTest' ),@decryptedChar );
--암호화 문자
SET @encryptedChar = CONVERT( VARCHAR( MAX ),@encryptedBinary,2 );
SELECT @decryptedChar AS '평문',
       @encryptedBinary AS '암호화 바이너리',
       @encryptedChar AS '암호화 문자',
       LEN( @encryptedChar ) AS '암호화 문자길이';
GO
CLOSE SYMMETRIC KEY SymmetricTest;
 
 
/*
6. 복호화
*/
 
OPEN SYMMETRIC KEY SymmetricTest DECRYPTION BY CERTIFICATE CerificateTest;
GO
DECLARE @encryptedChar VARCHAR( MAX ),
        @encryptedBinary VARBINARY( MAX ),
        @decryptedChar VARCHAR( MAX );
--암호화 문자
SET @encryptedChar = '003A89DBF6BDEE4A914F13D77806489B01000000DB4E2D7E18166FCE544AC3BD2CC0EB145DA6C9E1A9369773F23506B013814E5F0BF83AF57CFBEF80BB818C152E1525F5';
--암호화 바이너리
SET @encryptedBinary = CONVERT( VARBINARY( MAX ),@encryptedChar,2 );
--평문
SET @decryptedChar = CONVERT( VARCHAR( MAX ),DECRYPTBYKEY( @encryptedBinary ));
SELECT @encryptedChar AS '암호화 문자',
       @encryptedBinary AS '암호화 바이너리',
       @decryptedChar AS '평문';
GO
CLOSE SYMMETRIC KEY SymmetricTest;