问题描述
在，我是无法使用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解密字节？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！