C#] MSSQL AES128/256, SHA256 암복호화 어셈블리 DLL 만들기

1. SQL Server 데이터베이스 프로젝트 생성

2. 프로젝트 선택 후 추가 -> 새항목 -> SQL CLR C# -> SQL CLR C# 사용자 정의 함수 추가 

3. Crypto.cs 파일 추가 

총 9개 함수 추가 

AES128/256 암복호화에 base64 인코딩을 사용했다. (HEX option도 있음)

1. encryptAES128 : class내 KEY를 이용하여 AES128 암호
2. decryptAES128 : class내 KEY를 이용하여 AES128 복호
3. encryptAES128_pw : KEY를 parameter로 전달받아 AES128 암호
4. decryptAES128_pw : KEY를 parameter로 전달받아 AES128 복호
5. encryptAES256 : class내 KEY를 이용하여 AES256 암호
6. decryptAES256 : class내 KEY를 이용하여 AES256 복호
7. encryptAES256_pw : KEY를 parameter로 전달받아 AES256 암호
8. decryptAES256_pw : KEY를 parameter로 전달받아 AES256 복호
9. encryptSHA256 : SHA256 암호

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using System.Security.Cryptography;
using System.Text;
using Microsoft.SqlServer.Server;

public partial class Crypto
{
    private static readonly string KEY = "12345123451234512345123451234512";

    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString encryptAES128(SqlString plain)
    {
        try
        {
            string plainString = plain.ToString();
            return Crypto.encryptAES128_pw(plainString, KEY);
        }
        catch (Exception) {
            return null;
        }
    }

    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString decryptAES128(SqlString plain)
    {
        try
        {
            string plainString = plain.ToString();
            return Crypto.decryptAES128_pw(plainString, KEY);
        }
        catch (Exception)
        {
            return null;
        }
    }

    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString encryptAES128_pw(SqlString plain, SqlString password)
    {
        try
        {
            string plainString = plain.ToString();
            string passwordString = password.ToString();

            UTF8Encoding ue = new UTF8Encoding();
            byte[] plainBytes = ue.GetBytes(plainString);

            RijndaelManaged rijndael = new RijndaelManaged();
            rijndael.KeySize = 128;
            rijndael.Mode = CipherMode.CBC;
            rijndael.Padding = PaddingMode.PKCS7;

            // key 및 iv 설정
            byte[] pwdBytes = ue.GetBytes(passwordString);
            byte[] keyBytes = new byte[16];
            int lenK = pwdBytes.Length;
            if (lenK > keyBytes.Length) { lenK = keyBytes.Length; }
            Array.Copy(pwdBytes, keyBytes, lenK);
            rijndael.Key = keyBytes;
            rijndael.IV = new byte[] { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };

            ICryptoTransform transform = rijndael.CreateEncryptor();
            // 암호화 수행 
            byte[] encryptBytes = transform.TransformFinalBlock(plainBytes, 0, plainBytes.Length);
            rijndael.Clear();

            string encryptString = Convert.ToBase64String(encryptBytes);

            return new SqlString(encryptString);

        }
        catch (Exception)
        {
            return null;
        }
    }


    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString decryptAES128_pw(SqlString encrypt, SqlString password)
    {

        try
        {
            string encryptString = encrypt.ToString();
            string passwordString = password.ToString();

            UTF8Encoding ue = new UTF8Encoding();
            RijndaelManaged rijndael = new RijndaelManaged();
            rijndael.KeySize = 128;
            rijndael.Mode = CipherMode.CBC;
            rijndael.Padding = PaddingMode.PKCS7;

            byte[] encryptBytes = Convert.FromBase64String(encryptString);

            // key 및 iv 설정
            byte[] pwdBytes = ue.GetBytes(passwordString);
            byte[] keyBytes = new byte[16];
            int lenK = pwdBytes.Length;
            if (lenK > keyBytes.Length) { lenK = keyBytes.Length; }
            Array.Copy(pwdBytes, keyBytes, lenK);
            rijndael.Key = keyBytes;
            rijndael.IV = new byte[] { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };

            ICryptoTransform transform = rijndael.CreateDecryptor();
            // 복호화 수행
            byte[] plainBytes = transform.TransformFinalBlock(encryptBytes, 0, encryptBytes.Length);
            rijndael.Clear();

            string plainString = Encoding.UTF8.GetString(plainBytes);

            return new SqlString(plainString);
        }
        catch (Exception)
        {
            return null;
        }
    }

    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString encryptAES256(SqlString plain)
    {
        try
        {
            string plainString = plain.ToString();
            return Crypto.encryptAES256_pw(plainString, KEY);
        }
        catch (Exception)
        {
            return null;
        }
    }

    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString decryptAES256(SqlString plain)
    {
        try
        {
            string plainString = plain.ToString();
            return Crypto.decryptAES256_pw(plainString, KEY);
        }
        catch (Exception)
        {
            return null;
        }
    }

    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString encryptAES256_pw(SqlString plain, SqlString password)
    {
        try
        {
            string plainString = plain.ToString();
            string passwordString = password.ToString();

            UTF8Encoding ue = new UTF8Encoding();
            byte[] plainBytes = ue.GetBytes(plainString);

            RijndaelManaged rijndael = new RijndaelManaged();
            rijndael.KeySize = 256;
            rijndael.Mode = CipherMode.CBC;
            rijndael.Padding = PaddingMode.PKCS7;

            // key 및 iv 설정
            byte[] pwdBytes = ue.GetBytes(passwordString);
            byte[] keyBytes = new byte[32];
            int lenK = pwdBytes.Length;
            if (lenK > keyBytes.Length) { lenK = keyBytes.Length; }
            Array.Copy(pwdBytes, keyBytes, lenK);
            rijndael.Key = keyBytes;
            rijndael.IV = new byte[] { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };

            ICryptoTransform transform = rijndael.CreateEncryptor();
            // 암호화 수행 
            byte[] encryptBytes = transform.TransformFinalBlock(plainBytes, 0, plainBytes.Length);
            rijndael.Clear();

            string encryptString = Convert.ToBase64String(encryptBytes);

            return new SqlString(encryptString);
        }
        catch (Exception)
        {
            return null;
        }
    }


    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString decryptAES256_pw(SqlString encrypt, SqlString password)
    {

        try
        {
            string encryptString = encrypt.ToString();
            string passwordString = password.ToString();

            UTF8Encoding ue = new UTF8Encoding();
            RijndaelManaged rijndael = new RijndaelManaged();
            rijndael.KeySize = 256;
            rijndael.Mode = CipherMode.CBC;
            rijndael.Padding = PaddingMode.PKCS7;

            byte[] encryptBytes = Convert.FromBase64String(encryptString);

            // key 및 iv 설정
            byte[] pwdBytes = ue.GetBytes(passwordString);
            byte[] keyBytes = new byte[32];
            int lenK = pwdBytes.Length;
            if (lenK > keyBytes.Length) { lenK = keyBytes.Length; }
            Array.Copy(pwdBytes, keyBytes, lenK);
            rijndael.Key = keyBytes;
            rijndael.IV = new byte[] { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };

            ICryptoTransform transform = rijndael.CreateDecryptor();
            // 복호화 수행
            byte[] plainBytes = transform.TransformFinalBlock(encryptBytes, 0, encryptBytes.Length);
            rijndael.Clear();

            string plainString = Encoding.UTF8.GetString(plainBytes);

            return new SqlString(plainString);
        }
        catch (Exception) {
            return null;
        }
    }

    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString encryptSHA256(SqlString plain)
    {
        try
        {
            string plainString = plain.ToString();

            SHA256Managed smd = new SHA256Managed();
            UTF8Encoding ue = new UTF8Encoding();

            byte[] plainBytes = ue.GetBytes(plainString);
            byte[] encrptBytes = smd.ComputeHash(plainBytes);

            StringBuilder encryptString = new StringBuilder();
            foreach (byte b in encrptBytes)
            {
                encryptString.Append(b.ToString("x2"));
            }

            return new SqlString(encryptString.ToString());
        }
        catch (Exception)
        {
            return null;
        }
    }
}

 

4. 프로젝트 빌드 -> bin\Debug 경로의 DLL 파일 MSSQL 어셈블리 등록

5. 어셈블리 내 class 함수를 호출하는 스칼라 반환 함수 생성

--
CREATE FUNCTION [dbo].[FN_DE_AES128](@plain [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [SqlFunction].[Crypto].[decryptAES128]

--
CREATE FUNCTION [dbo].[FN_DE_AES128_PW](@plain [nvarchar](max), @password [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [SqlFunction].[Crypto].[decryptAES128_pw]

--
CREATE FUNCTION [dbo].[FN_DE_AES256](@plain [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [SqlFunction].[Crypto].[decryptAES256]

--
CREATE FUNCTION [dbo].[FN_DE_AES256_PW](@plain [nvarchar](max), @password [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [SqlFunction].[Crypto].[decryptAES256_pw]

--
CREATE FUNCTION [dbo].[FN_EN_AES128](@plain [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [SqlFunction].[Crypto].[encryptAES128]

--
CREATE FUNCTION [dbo].[FN_EN_AES128_PW](@plain [nvarchar](max), @password [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [SqlFunction].[Crypto].[encryptAES128_pw]

--
CREATE FUNCTION [dbo].[FN_EN_AES256](@plain [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [SqlFunction].[Crypto].[encryptAES256]

--
CREATE FUNCTION [dbo].[FN_EN_AES256_PW](@plain [nvarchar](max), @password [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [SqlFunction].[Crypto].[encryptAES256_pw]

--
CREATE FUNCTION [dbo].[FN_EN_SHA256](@plain [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [SqlFunction].[Crypto].[encryptSHA256]