Hi Viv

if you ever need to I found some good instructions on how to set this up as a CLR and use it in SQL Server here

http://www.codeproject.com/Articles/388312/Create-CLR-Stored-Procedure-with-Visual-Studio-Exp


(possibly teaching you how to suck eggs)

Only difference is I've created it as a SQL function rather than a Stored procedure so I can return strings easily from the CLR.

Nick



>I'd have suggested a SQL/CLR thingy but don't know if it's an option ?
>>SQL Server integration package.
>>
>>And I'll need the luck :-)
>>
>>
>>>I haven't a clue what a dtsx package is (and happy to remain ignorant :-} ) - but good luck anyway...
>>>
>>>>I'm eventually handling this in a dtsx package so hopefully I can add a script component that will handle that bit.
>>>>>The TripleDes key needs a byte[] which is known at both ends. They are using MD5 to create the byte[] value from a shared string.....
>>>>>
>>>>>>Is that some sort of double layer of encryption ?
>>>>>>
>>>>>>They are MD5 encrypting the key then using that value to encrypt the actual data ?
>>>>>>
>>>>>>>>>>Hi
>>>>>>>>>>
>>>>>>>>>>I'm being passed some data encrypted with MD5.
>>>>>>>>>>
>>>>>>>>>>I can find examples of how I encrypt in TSql
>>>>>>>>>>
>>>>>>>>>>SELECT CONVERT(NVARCHAR(32),HashBytes('MD5', 'email@dot.com'),2)
>>>>>>>>>>
>>>>>>>>>>but I can't find an example of how I would de encrypt the result to get the original content.
>>>>>>>>>
>>>>>>>>>You can't - it's just a hash value....
>>>>>>>>
>>>>>>>>Hi Viv
>>>>>>>>
>>>>>>>>I'm being passed some data which I've been told is MD5 encrpted
>>>>>>>>
>>>>>>>>they've also passed me some c# code which has encryption and decryption functions.
>>>>>>>>
>>>>>>>>Is that not the same thing ?
>>>>>>>>
>>>>>>>>Nick
>>>>>>>>
>>>>>>>>
>>>>>>>>using System;
>>>>>>>>using System.Collections.Generic;
>>>>>>>>using System.Linq;
>>>>>>>>using System.Text;
>>>>>>>>using System.Security.Cryptography;
>>>>>>>>
>>>>>>>>namespace Unified.ram.Common.Util
>>>>>>>>{
>>>>>>>> public class EncryptDecrypt
>>>>>>>> {
>>>>>>>> public static string Encrypt(string strToEncrypt)
>>>>>>>> {
>>>>>>>> string key = Sitecore.Configuration.Settings.GetSetting("EncryptDecryptKey", "");
>>>>>>>> return Encrypt(strToEncrypt, key);
>>>>>>>> }
>>>>>>>>
>>>>>>>> public static string Encrypt(string strToEncrypt, string strKey)
>>>>>>>> {
>>>>>>>> try
>>>>>>>> {
>>>>>>>> TripleDESCryptoServiceProvider objDESCrypto =
>>>>>>>> new TripleDESCryptoServiceProvider();
>>>>>>>> MD5CryptoServiceProvider objHashMD5 = new MD5CryptoServiceProvider();
>>>>>>>> byte[] byteHash, byteBuff;
>>>>>>>> string strTempKey = strKey;
>>>>>>>> byteHash = objHashMD5.ComputeHash(ASCIIEncoding.ASCII.GetBytes(strTempKey));
>>>>>>>> objHashMD5 = null;
>>>>>>>> objDESCrypto.Key = byteHash;
>>>>>>>> objDESCrypto.Mode = CipherMode.ECB; //CBC, CFB
>>>>>>>> byteBuff = ASCIIEncoding.ASCII.GetBytes(strToEncrypt);
>>>>>>>> return Convert.ToBase64String(objDESCrypto.CreateEncryptor().
>>>>>>>> TransformFinalBlock(byteBuff, 0, byteBuff.Length));
>>>>>>>> }
>>>>>>>> catch (Exception ex)
>>>>>>>> {
>>>>>>>> return "Wrong Input. " + ex.Message;
>>>>>>>> }
>>>>>>>> }
>>>>>>>>
>>>>>>>> public static string Decrypt(string strEncrypted)
>>>>>>>> {
>>>>>>>> string key = Sitecore.Configuration.Settings.GetSetting("EncryptDecryptKey", "");
>>>>>>>> return Decrypt(strEncrypted, key);
>>>>>>>> }
>>>>>>>>
>>>>>>>> public static string Decrypt(string strEncrypted, string strKey)
>>>>>>>> {
>>>>>>>> try
>>>>>>>> {
>>>>>>>> TripleDESCryptoServiceProvider objDESCrypto =
>>>>>>>> new TripleDESCryptoServiceProvider();
>>>>>>>> MD5CryptoServiceProvider objHashMD5 = new MD5CryptoServiceProvider();
>>>>>>>> byte[] byteHash, byteBuff;
>>>>>>>> string strTempKey = strKey;
>>>>>>>> byteHash = objHashMD5.ComputeHash(ASCIIEncoding.ASCII.GetBytes(strTempKey));
>>>>>>>> objHashMD5 = null;
>>>>>>>> objDESCrypto.Key = byteHash;
>>>>>>>> objDESCrypto.Mode = CipherMode.ECB; //CBC, CFB
>>>>>>>> byteBuff = Convert.FromBase64String(strEncrypted);
>>>>>>>> string strDecrypted = ASCIIEncoding.ASCII.GetString
>>>>>>>> (objDESCrypto.CreateDecryptor().TransformFinalBlock
>>>>>>>> (byteBuff, 0, byteBuff.Length));
>>>>>>>> objDESCrypto = null;
>>>>>>>> return strDecrypted;
>>>>>>>> }
>>>>>>>> catch (Exception ex)
>>>>>>>> {
>>>>>>>> return "Wrong Input. " + ex.Message;
>>>>>>>> }
>>>>>>>> }
>>>>>>>> }
>>>>>>>>}
>>>>>>>
>>>>>>>Looks like that is using TripleDES for the encryption with a MD5 hash as the key ? No idea how you can go about decrypting in SQL tho :-{