MSSQL - CLR

 

-- master 테이블로 변경합니다.
use [master]
go

--clr을 사용할수 있도록 변경을 해야 하는데 sysadmin 권한이 있어야 한다.
exec sp_configure 'clr enabled', 1;
reconfigure;
go

-- 그다음 함수를 하용하고자 하는 디비에 대해 trustworthy 기본 속성이 off이므로 높은 권한적용을 위해 on 시킴
alter database  [사용하고자 하는 디비명]
set trustworthy on

/* 에셈블리를 등록하다 보면 
master 데이터베이스에 기록된 데이터베이스 소유자 sid가 데이터베이스 '~~'에 기록된 
데이터베이스 소유자 sid와 다릅니다. 
alter authorization 문을 사용하여 데이터베이스 '~~~'의 소유자를 다시 설정하여 
이 문제를 해결해야 합니다.
라는 에러가 나면 
master 에 등록된 디비의 소유자를 sa 로 변경합니다.
alter authorization on database::사용하고자 하는 디비명 to sa ;
*/

이제 어셈블리를 등록합니다 . 
--assembly 생성 - 대소문자 구분함. [] 없으면 안됩니다.
create assembly [SqlFunction]
from 'd:\sqldb\SqlFunction.dll'  --파일 지정 경로
with permission_set = unsafe
go

에러가 나올수 있습니다. 
어셈블리 'sqlfunction'이(가) 트러스트되어 있지 않으므로 어셈블리 'sqlfunction'에 대한 create assembly가 실패했습니다. 어셈블리는 unsafe assembly 권한과 함께 해당하는 로그인이 있는 인증서 또는 비대칭 키로 서명되어 있는 경우나 sp_add_trusted_assembly를 사용하여 트러스트되어 있는 경우에 트러스터됩니다.
​위에서 꼭 set trustworthy on 해주어야 합니다 . 
했는데 이번엔 다른 에러가 나옵니다. 
master 데이터베이스에 기록된 데이터베이스 소유자 sid가 데이터베이스 '사용하고자 하는 디비명'에 기록된 데이터베이스 소유자 sid와 다릅니다. alter authorization 문을 사용하여 데이터베이스 '사용하고자 하는 디비명'의 소유자를 다시 설정하여 이 문제를 해결해야 합니다.
​
그러면 위에 있던 디비 소유자를 변경해 줍니다. 
대부분은 sa 일거니가... sa 로 해줍니다. 
​
d:\sqldb\SqlFunction.dll 에 복사해두었습니다 . 
SqlFunction.dll 이라는 CLR을  SqlFunction 라는 어셈블리로 등록한다는 의미..
​
이제 dll에 있는 함수를 sql 에 사용할수 있도록 sql 함수를 만듭니다. 
create function dbo.fn_UsrFun1(
@p_original_string nvarchar(4000)
)
returns nvarchar(max) with execute as caller
as
-- 어셈블리명.dll 내 class 명.함수명
external name [SqlFunction].UsrFunction.UsrFun1
go

sql 서버내 함수명 : dbo.fn_UsrFun1
external name [SqlFunction].UsrFunction.UsrFun1
이 부분이 제일 중요하겠죠 ... 
SqlFunction 어셈블리 > UsrFunction Class > UsrFun1 method 라는 의미입니다 .
​
UsrFun2 라는 함수도 만들었으니  아래처럼 될겁니다.
create function dbo.fn_UsrFun2(
@p_original_string nvarchar(4000)
)
returns nvarchar(max) 
with execute as caller
as
external name [SqlFunction].UsrFunction.UsrFun2
go
​
디비에서 확장하면  다음처럼 등록된 것을 볼수 있습니다.


참조 : https://m.blog.naver.com/jskimmail/222083779381

C# - MSSQL CLR (AES256 암호화)

using System;
using System.Text;
using System.Security.Cryptography;
using System.IO;

public partial class CryptoHelper
{
    /// <summary>
    /// AesManaged 생성
    /// </summary>
    /// <returns></returns>
    private static AesManaged GetAesManaged()
    {
        // 암호화에 사용할 32bytes 의 키 값.
        const string DEFAULT_KEY_ASE256 = "32자리 KEY 입력";

        return new AesManaged()
        {
            KeySize = 256,
            BlockSize = 128,
            Mode = CipherMode.CBC,
            Padding = PaddingMode.PKCS7,
            Key = System.Text.Encoding.UTF8.GetBytes(DEFAULT_KEY_ASE256),
            IV = new byte[] {0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}
        };
    }

    /// <summary>
    /// 암호화 AES256
    /// </summary>
    /// <param name="plainText"></param>
    /// <returns></returns>
    [Microsoft.SqlServer.Server.SqlFunction]
    public static string EncryptAES256(string inputText)
    {
        if (string.IsNullOrWhiteSpace(inputText))
            return null;

        byte[] encrypted;
        AesManaged aesAlg = GetAesManaged();

        ICryptoTransform encryptor = aesAlg.CreateEncryptor(aesAlg.Key, aesAlg.IV);

        using (MemoryStream msEncrypt = new MemoryStream())
        {
            using (CryptoStream csEncrypt = new CryptoStream(msEncrypt, encryptor, CryptoStreamMode.Write))
            {
                using (StreamWriter swEncrypt = new StreamWriter(csEncrypt))
                {
                    swEncrypt.Write(inputText);
                }
                encrypted = msEncrypt.ToArray();
            }
        }

        return Convert.ToBase64String(encrypted);
    }

    /// <summary>
    /// 복호화 AES256
    /// </summary>
    /// <param name="plainText"></param>
    /// <returns></returns>
    [Microsoft.SqlServer.Server.SqlFunction]
    public static string DecryptAES256(string inputText)
    {
        if (string.IsNullOrWhiteSpace(inputText))
            return null;

        string plaintext = null;
        byte[] cipherText = Convert.FromBase64String(inputText);
        AesManaged aesAlg = GetAesManaged();

        ICryptoTransform decryptor = aesAlg.CreateDecryptor(aesAlg.Key, aesAlg.IV);

        using (MemoryStream msDecrypt = new MemoryStream(cipherText))
        {
            using (CryptoStream csDecrypt = new CryptoStream(msDecrypt, decryptor, CryptoStreamMode.Read))
            {
                using (StreamReader srDecrypt = new StreamReader(csDecrypt))
                {
                    plaintext = srDecrypt.ReadToEnd();
                }
            }
        }

        return plaintext;
    }

    /// <summary>
    /// 단방향 암호화 (SHA256)
    /// </summary>
    /// <param name="Data"></param>
    /// <returns></returns>
    [Microsoft.SqlServer.Server.SqlFunction]
    public static string SHA256Hash(string inputText)
    {
        StringBuilder sb = new StringBuilder();
        SHA256 sha = new SHA256Managed();
        byte[] hash = sha.ComputeHash(Encoding.ASCII.GetBytes(inputText));

        foreach (byte b in hash)
            sb.AppendFormat("{0:x2}", b);

        return sb.ToString();
    }

}





/*
EXEC sp_configure 'show advanced options', 1
GO

RECONFIGURE
GO

EXEC sp_configure 'clr enabled', 1
GO

RECONFIGURE
GO
*/

/*
-- DROP ASSEMBLY myEncrypt
CREATE ASSEMBLY myEncrypt FROM 'D:\CryptoProject\CryptoSQLProject.dll'
WITH PERMISSION_SET = SAFE
*/




/*
-- DROP FUNCTION dbo.fn_Encrypt
-- DROP FUNCTION dbo.fn_Decrypt

CREATE FUNCTION fn_Encrypt(@value NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
EXTERNAL NAME myEncrypt.CryptoHelper.EncryptAES256
GO

CREATE FUNCTION fn_Decrypt(@value NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
EXTERNAL NAME myEncrypt.CryptoHelper.DecryptAES256
GO
*/



/*
SELECT *
FROM sys.assemblies

SELECT dbo.fn_Encrypt('123-12-123456')
SELECT dbo.fn_Decrypt('Jsh3QXSLqiv2U5q1wF+gEw==')
*/