（原 ：http://topic.csdn.net/u/20100929/17/21bfc421-4ace-435f-baea-4d352d1015e3.html）
因业务升级，将原用户DES密文转换为MD5，需要在SQL中进行DES解密操作，故使用CLR函数实现，特此记录，以备后用。--by RYHAN
C#程序集 dll 文件
1 using System; 2 using System.IO; 3 using System.Text; 4 using System.Data; 5 using System.Data.SqlTypes; 6 using System.Security.Cryptography; 7 using Microsoft.SqlServer.Server; 8 9 public class DES 10 { 11 [SqlFunction(IsDeterministic=true, IsPrecise=true)] 12 public static SqlString DESEncrypt(SqlString text, SqlString key) 13 { 14 if (text.IsNull || key.IsNull || key.Value.Length < 8) 15 return null; 16 17 return (SqlString)_DESEncrypt(Encoding.Default.GetBytes((string)text), 18 Encoding.Default.GetBytes((string)key), 19 new byte[] { 0x12, 0x34, 0x56, 0x78, 0x90, 0xAB, 0xCD, 0xEF }); 20 } 21 // Encrypt with DES 22 private static string _DESEncrypt(byte[] text, byte[] key, byte[] iv) 23 { 24 string entext; 25 26 using (MemoryStream mstream = new MemoryStream()) 27 { 28 DESCryptoServiceProvider des = new DESCryptoServiceProvider(); 29 CryptoStream estream = new CryptoStream(mstream, des.CreateEncryptor(key, iv), CryptoStreamMode.Write); 30 try 31 { 32 estream.Write(text, 0, text.Length); 33 estream.FlushFinalBlock(); 34 entext = Convert.ToBase64String(mstream.ToArray()); 35 } 36 finally 37 { 38 estream.Close(); 39 des.Clear(); 40 } 41 } 42 43 return entext; 44 } 45 46 [SqlFunction(IsDeterministic=true, IsPrecise=true)] 47 public static SqlString DESDecrypt(SqlString text, SqlString key) 48 { 49 if (text.IsNull || key.IsNull || key.Value.Length < 8) 50 return null; 51 52 return (SqlString)_DESDecrypt(Convert.FromBase64String((string)text), 53 Encoding.Default.GetBytes((string)key), 54 new byte[] { 0x12, 0x34, 0x56, 0x78, 0x90, 0xAB, 0xCD, 0xEF }); 55 } 56 // Decrypt with DES 57 private static string _DESDecrypt(byte[] text, byte[] key, byte[] iv) 58 { 59 string detext; 60 61 using (MemoryStream mstream = new MemoryStream()) 62 { 63 DESCryptoServiceProvider des = new DESCryptoServiceProvider(); 64 CryptoStream estream = new CryptoStream(mstream, des.CreateDecryptor(key, iv), CryptoStreamMode.Write); 65 try 66 { 67 estream.Write(text, 0, text.Length); 68 estream.FlushFinalBlock(); 69 detext = Encoding.Default.GetString(mstream.ToArray()); 70 } 71 finally 72 { 73 estream.Close(); 74 des.Clear(); 75 } 76 } 77 78 return detext; 79 } 80 }
SQL CLR 函数
1 use master 2 go 3 -- 启用 SQL Server 的 CLR 功能 4 exec sp_configure 'show advanced options',1; 5 go 6 reconfigure 7 go 8 exec sp_configure 'clr enabled',1; 9 go 10 reconfigure 11 go 12 13 if object_id('dbo.des_encrypt','FS') is not null 14 drop function dbo.des_encrypt; 15 go 16 17 if object_id('dbo.des_decrypt','FS') is not null 18 drop function dbo.des_decrypt; 19 go 20 21 -- 创建程序集 22 if exists (select * from sys.assemblies where name='DES') 23 drop assembly DES; 24 go 25 create assembly DES authorization dbo 26 from 'C:\clrDES.dll' -- dll 文件路径 27 with permission_set=safe; 28 go 29 30 create function dbo.des_encrypt (@text nvarchar(max), @key nvarchar(128)) 31 returns nvarchar(max) 32 as external name DES.DES.DESEncrypt; 33 go 34 35 -- select dbo.des_encrypt(N'hello world', N'88888888'); 36 37 create function dbo.des_decrypt (@text nvarchar(max), @key nvarchar(128)) 38 returns nvarchar(max) 39 as external name DES.DES.DESDecrypt; 40 go 41 42 -- select dbo.des_decrypt(N'+GeLDT6kAxZlm2pnFX8X4w==',N'88888888');
因业务升级，将原用户DES密文转换为MD5，需要在SQL中进行DES解密操作，故使用CLR函数实现，特此记录，以备后用。--by RYHAN
