C # Decrypt bytes from SQL Server EncryptByPassPhrase?

Question

After Replicating T-SQL DecryptByPassPhrase in C # , I cannot get simple encryption with MSSQL to decrypt in C #. Encrypted values ​​in certain columns are necessary because the table is exported to Excel and Access on a regular basis, so simple encryption is more than enough to “block” the values ​​without having the developers re-execute the views, etc.

In SQL Server 2012:

    select EncryptByPassPhrase( N'hello' , N'world'  ) 
-- returns 0x01000000AA959FFB3A8E4B06B734051437E198C8B72000A058ACE91D617123DA102287EB

In C #:

byte[] buf = System.Text.Encoding.UTF8.GetBytes( "0x010000003A95FA870ED699A5F90D33C2BF01491D9132F61BA162998E96F37117AF5DA0905D51EB6FB298EC88" );
// bytes emitted from the database
var cp = new TripleDESCryptoServiceProvider();
var m = new MemoryStream(buf);
cp.Key = System.Text.Encoding.UTF8.GetBytes( "hello" ); // throws
cp.IV = System.Text.Encoding.UTF8.GetBytes( "hello" ); // throws
CryptoStream cs = new CryptoStream( m , cp.CreateDecryptor( cp.Key , cp.IV ) , CryptoStreamMode.Read );
StreamReader reader = new StreamReader( cs );
string plainText = reader.ReadToEnd();

What should C # code look like?

Thank.

Answer 1

, , , , SQL EncryptByPassPhrase. , #, #.

EncryptByPassPhrase SQL, DecryptByPassPhrase SQL, # -.

:

Declare @lEncryptedText VARBINARY(256) = (select ENCRYPTBYPASSPHRASE('hello','world'))
SELECT @lEncryptedText --Encrypted VALUE for world

SELECT CONVERT(VARCHAR(100),DECRYPTBYPASSPHRASE('hello',@lEncryptedText)) --Decrypted Value