데이터를 암/복호화 하기위해 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 등이다.
