C＃从SQL Server EncryptByPassPhrase解密字节？ [英] C# Decrypt bytes from SQL Server EncryptByPassPhrase?

问题描述

在在C＃中复制T-SQL DecryptByPassPhrase ，我是无法使用MSSQL获得简单的加密，以便在C＃中进行描述。某些列中的加密值是必需的，因为表会定期导出到Excel和Access中，因此简单加密足以阻止值，而不必让开发人员（重新）执行视图等。

Following Replicate T-SQL DecryptByPassPhrase in C#, I am unable to get a simple encryption with MSSQL to descrypt in C#. The encrypted values in certain columns is necessary because the table is exported into Excel and Access on a regular basis so simple encryption is more than enough to "block" values without having to involve developers to (re)do views, etc.

在SQL Server 2012中：

In SQL Server 2012:

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

在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();

应该使用C＃代码看起来如何？

What should working C# code look like?

谢谢。

推荐答案

我相信你正在关注的链接是建议一种新的加密和解密模式， SQL EncryptByPassPhrase被创建。因此，您只能在C＃中使用解密，如果您在C＃中加密也是如此。

I believe the link you are following is suggesting a new way to encrypt and decrypt mimicking that of how the SQL EncryptByPassPhrase is made. So, you could only use the decrypt in C#, if you encrypted in C# as well.

由于您已经在SQL中使用了EncryptByPassPhrase，所以我建议只使用DecryptByPassPhrase SQL传递给C＃代码之前。

Since, you already used EncryptByPassPhrase in SQL then I would suggest just using DecryptByPassPhrase in SQL before passing to C# code.

您的hello world加密和解密示例：

Example of your hello world encrypt and decrypt:

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

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

这篇关于C＃从SQL Server EncryptByPassPhrase解密字节？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！