EncryptByPassPhrase in SQL Server and Triple DES descrypt in C#

Question

Context:

• data is stored in SQL Server. Some fields are encrypted using EncryptWithPassPhrase

update my_table set my_field = EncryptByPassPhrase('012345678901234567890123','some value')

• data is fetch using NHibernate. Encrypted data are decoded using Interceptor

  public static string Decrypt(byte[] toDecryptArray, string key)
  {
      byte[] keyArray;
  
      keyArray = UTF8Encoding.UTF8.GetBytes(key);
  
      TripleDESCryptoServiceProvider tdes = new TripleDESCryptoServiceProvider();
      tdes.Key = keyArray;
      tdes.Mode = CipherMode.ECB;
      tdes.Padding = PaddingMode.PKCS7;
  
      ICryptoTransform cTransform = tdes.CreateDecryptor();
      byte[] resultArray = cTransform.TransformFinalBlock(toDecryptArray, 0, toDecryptArray.Length);
  
      return UTF8Encoding.UTF8.GetString(resultArray);
  }
  

It fails on cTransform.TransformFinalBlock saying "Length of data to decrypt is invalid".

What is wrong with my code? Data is encrypted/decrypted with same key (012345678901234567980123).

Answer 1

The documentation says of the passphrase:

"A variable of type nvarchar, char, varchar, binary, varbinary, or nchar containing a passphrase from which to generate a symmetric key."

http://msdn.microsoft.com/en-us/library/ms190357.aspx

So the reason why you can't decrypt it in C# is that you need to use the derived key, not the passphrase itself. I don't know how you'd derive the key, though.

Answer 2

I did not found out how to decrypt by C# code what was encrypted with T-SQL function EncryptByPassPhrase.

But SQL Server 2005 allow you to create C# assembly and load it into SQL Server, then build stored procedures or functions or your C# methods. Thus, I created "my" EncryptByPassPhrase function with shared code between app and SQL Server, so that I can encrypt/decrypt in both sides.