데이터를 암/복호화 하기위해 AES암호화를 사용하고있다.
헌데 java, C# 코드는 찾기 쉬운데 .. MSSQL에서 직접 사용할 수 있는 sp가 없더라..
그래서 만들어봤다.

.net 코드로 만든 클래스 라이브러리를 MSSQL에서 바로 호출이 가능하다.
방법은 이렇다.

1. 코드를 작성한다. (C#으로 되어있는 AES 암/복호화 코드를 copy해왔다.)  컴파일하여 .dll 파일을 얻는다.

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

namespace AESCrypto
{
    public class CAesCrypto
    {
        public static void Encrypt(SqlString textToEncrypt, SqlString key, out SqlString strValue)
        {
            try
            {
                RijndaelManaged rijndaelCipher = new RijndaelManaged();
                rijndaelCipher.Mode = CipherMode.CBC;
                rijndaelCipher.Padding = PaddingMode.PKCS7;

                rijndaelCipher.KeySize = 0x80;
                rijndaelCipher.BlockSize = 0x80;
                string strKey = key.ToString();
                byte[] pwdBytes = Encoding.UTF8.GetBytes(strKey);
                byte[] keyBytes = new byte[0x10];
                int len = pwdBytes.Length;
                if (len > keyBytes.Length)
                {
                    len = keyBytes.Length;
                }
                Array.Copy(pwdBytes, keyBytes, len);
                rijndaelCipher.Key = keyBytes;
                rijndaelCipher.IV = keyBytes;
                ICryptoTransform transform = rijndaelCipher.CreateEncryptor();
                string strTextToEncrypt = textToEncrypt.ToString();
                byte[] plainText = Encoding.UTF8.GetBytes(strTextToEncrypt);
                strValue = Convert.ToBase64String(transform.TransformFinalBlock(plainText, 0, plainText.Length));

            }
            catch (Exception ex)
            {
                strValue = ex.ToString();
                throw new Exception(ex.Message);
            }
        }

        public static void Decrypt(SqlString textToDecrypt, SqlString key, out SqlString strValue)
        {
            try
            {
                RijndaelManaged rijndaelCipher = new RijndaelManaged();
                rijndaelCipher.Mode = CipherMode.CBC;
                rijndaelCipher.Padding = PaddingMode.PKCS7;

                rijndaelCipher.KeySize = 0x80;
                rijndaelCipher.BlockSize = 0x80;
                string strTextToDecrypt = textToDecrypt.ToString();
                byte[] encryptedData = Convert.FromBase64String(strTextToDecrypt);

                string strKey = key.ToString();
                byte[] pwdBytes = Encoding.UTF8.GetBytes(strKey);
                byte[] keyBytes = new byte[0x10];
                int len = pwdBytes.Length;
                if (len > keyBytes.Length)
                {
                    len = keyBytes.Length;
                }
                Array.Copy(pwdBytes, keyBytes, len);
                rijndaelCipher.Key = keyBytes;
                rijndaelCipher.IV = keyBytes;
                byte[] plainText = rijndaelCipher.CreateDecryptor().TransformFinalBlock(encryptedData, 0, encryptedData.Length);

                strValue = Encoding.UTF8.GetString(plainText);
            }
            catch (Exception ex)
            {
                strValue = ex.ToString();
                throw new Exception(ex.Message);
            }
        }
    }
}

 

2. MSSQL에서 CLR을 사용할수 있게 변경한다.

-- CLR을 사용할수 있도록 변경
exec sp_configure 'clr enabled', 1
reconfigure with override 


3. 해당 DB에 권한을 부여한다.

 ALTER DATABASE [DATABASE_NAME] SET
 TRUSTWORTHY ON


4. 1에서 만든 DLL로 ASSEMBLY를 생성한다.

CREATE ASSEMBLY AESCrypto
FROM 'D:\AESCrypto.dll'     <- dll의 물리적 경로예제
WITH PERMISSION_SET = UNSAFE 


4.1. 소유자가 달라서 안되면...소유자를 변경한다.
 - 해당메시지는 DB를 만든 사용자와 복원한 사용자가 다를때 나타난다.

 /*
master 데이터베이스에 기록된 데이터베이스 소유자 SID가 데이터베이스 '[DATABASE_NAME]'에 기록된 데이터베이스 소유자 SID와 다릅니다.
ALTER AUTHORIZATION 문을 사용하여 데이터베이스 '[DATABASE_NAME]'의 소유자를 다시 설정하여 이 문제를 해결해야 합니다.
*/
SELECT owner_sid FROM sys.databases WHERE database_id=DB_ID()
SELECT sid FROM sys.database_principals WHERE name=N'dbo'
EXEC sp_changedbowner '[USER_ID]'
ALTER AUTHORIZATION ON [DATABASE_NAME] TO [domain\user]


5. 프로시져를 생성한다.

CREATE PROCEDURE usp_AESCryptoEncrypt
    @strText        NVARCHAR(100),
    @strKey         NVARCHAR(50),
    @strEnc        NVARCHAR(200) OUTPUT
    AS     EXTERNAL     NAME AESCrypto.[AESCrypto.CAesCrypto].Encrypt

--
CREATE PROCEDURE usp_AESCryptoDecrypt
    @strText          NVARCHAR(100),
    @strKey        NVARCHAR(50),
    @strDec        NVARCHAR(200) OUTPUT
    AS     EXTERNAL     NAME AESCrypto.[AESCrypto.CAesCrypto].Decrypt

6. 사용해본다.

DECLARE @strEnc NVARCHAR(200), @return_value int
EXEC @return_value = [dbo].[usp_AESCryptoEncrypt]
@strText
, @strKey
, @strEnc = @strEnc OUTPUT

 

7. 참고 사이트 및 호환성 문제.

* 참고 :  http://zmeun.tistory.com/46

* 호환성 문제.

출처 : http://livelock.tistory.com/entry/DB-%ED%98%B8%ED%99%98%EC%84%B1-%EB%AC%B8%EC%A0%9C

SQL Server 2000->2005 마이그레이션 등을 수행한 후,
 2005부터 지원되는 SQL문을 사용하려고 하는 경우, 다음과 같은 메시지가 나타날 때가 있다.
 
Msg 325, Level 15, State 1, Line 6
'PIVOT' 근처의 구문이 잘못되었습니다. 이 기능을 사용하려면 현재 데이터베이스의 호환성 수준 값을 더 높게 설정해야 합니다. 저장 프로시저 sp_dbcmptlevel에 대해서는 도움말을 참조하십시오.

에러 메시지에서 볼 수 있듯이, 호환성과 관련된 문제이다.
따라서 다음과 같은 구문을 실행하여 호환성 문제를 해결한다. 

EXEC sp_dbcmptlevel '[Database 명]', [호환성 번호]

 호환성 번호는 80: SQL Server 2000, 90: SQL Server 2005 등이다.

 

 

 

 

반응형

+ Recent posts