SQL Server对称密钥加密后在C#中解密的实现问题

嘿，我来帮你搞定这个解密的问题！你已经在SQL Server里用对称密钥完成了数据加密，现在用LINQ to SQL做内联查询，要在C#里展示解密后的数据，这里有两种可行的方案，我给你详细拆解：

这种方式最安全高效，因为解密操作直接在数据库端完成，不需要把敏感的密钥信息暴露到应用层。具体步骤如下：

1. 在DataContext中映射SQL的解密函数
   LINQ to SQL默认没有内置DECRYPTBYKEY方法，所以我们需要在自己的DataContext类里添加一个自定义方法，让LINQ能识别并转换成对应的SQL函数调用：

   using System.Data.Linq.Mapping;
   
   public class YourDataContext : DataContext
   {
       // 其他DataContext代码...
   
       [Function(Name = "DECRYPTBYKEY", IsComposable = true)]
       public string DecryptByKey(byte[] cipherText)
       {
           // 这个方法只是用来生成SQL查询，不会在C#中执行，所以抛出异常没关系
           throw new NotImplementedException("仅用于LINQ to SQL查询组合");
       }
   }
   

2. 打开对称密钥并执行查询
   在查询解密数据之前，需要先在数据库连接中打开对应的对称密钥，然后再查询时调用我们定义的DecryptByKey方法：

   using (var context = new YourDataContext())
   {
       // 先执行SQL命令打开对称密钥
       context.ExecuteCommand("OPEN SYMMETRIC KEY TestTableKey DECRYPTION BY CERTIFICATE EncryptTestCert;");
   
       // 编写LINQ查询，解密EncryptedPhone列
       var userList = from u in context.Users
                      select new
                      {
                          u.Id,
                          u.UserName,
                          DecryptedPhone = context.DecryptByKey(u.EncryptedPhone)
                      };
   
       // 遍历结果，展示解密后的手机号
       foreach (var user in userList)
       {
           Console.WriteLine($"用户ID: {user.Id}, 手机号: {user.DecryptedPhone}");
       }
   
       // 可选：显式关闭对称密钥（连接关闭后会自动关闭，但显式操作更规范）
       context.ExecuteCommand("CLOSE SYMMETRIC KEY TestTableKey;");
   }
   

这种方式需要将数据库的对称密钥、证书信息导出到应用端，存在密钥泄露的安全风险，仅在特殊场景下使用。步骤如下：

1. 导出SQL Server的证书和对称密钥
   先在SQL Server中执行以下命令，将证书和密钥导出到文件（注意文件路径要数据库服务有权限访问）：

   -- 打开密钥
   OPEN SYMMETRIC KEY TestTableKey DECRYPTION BY CERTIFICATE EncryptTestCert;
   -- 导出证书
   BACKUP CERTIFICATE EncryptTestCert TO FILE = 'C:\SQLCerts\EncryptTestCert.cer' 
   WITH PRIVATE KEY (FILE = 'C:\SQLCerts\EncryptTestCert.pvk', ENCRYPTION BY PASSWORD = 'YourCertPassword123');
   -- 导出对称密钥
   BACKUP SYMMETRIC KEY TestTableKey TO FILE = 'C:\SQLCerts\TestTableKey.key' 
   ENCRYPTION BY PASSWORD = 'YourKeyPassword123';
   

2. 在C#中导入密钥并解密
   使用.NET的加密库读取导出的文件，然后解密数据：

   using System.Security.Cryptography;
   using System.Security.Cryptography.X509Certificates;
   
   // 导入证书
   var cert = new X509Certificate2(@"C:\SQLCerts\EncryptTestCert.cer", "YourCertPassword123");
   // 读取对称密钥文件（需要处理备份的密钥格式，这里简化示例）
   byte[] keyBytes = File.ReadAllBytes(@"C:\SQLCerts\TestTableKey.key");
   
   // 使用TripleDES算法解密（和你加密时用的算法一致）
   using (var tdes = new TripleDESCryptoServiceProvider())
   {
       // 从证书和密钥文件中获取密钥和IV（实际需要解析备份的密钥，这里仅示例）
       tdes.Key = /* 从密钥文件解析出的密钥字节 */;
       tdes.IV = /* 对应的初始化向量 */;
       tdes.Mode = CipherMode.CBC; // SQL Server的TRIPLE_DES默认使用CBC模式
       tdes.Padding = PaddingMode.Zeros; // SQL Server默认的填充方式
   
       // 假设从数据库拿到的加密数据是encryptedPhone（byte[]类型）
       byte[] encryptedPhone = /* 从LINQ查询获取的EncryptedPhone值 */;
       using (var decryptor = tdes.CreateDecryptor())
       {
           byte[] decryptedBytes = decryptor.TransformFinalBlock(encryptedPhone, 0, encryptedPhone.Length);
           // 去掉填充的空字符，得到原始手机号
           string decryptedPhone = Encoding.UTF8.GetString(decryptedBytes).TrimEnd('\0');
           Console.WriteLine(decryptedPhone);
       }
   }
   

• 权限问题：确保你的数据库登录账号拥有OPEN SYMMETRIC KEY、VIEW DEFINITION等相关权限，否则会抛出权限错误。
• 密钥生命周期：尽量避免在应用端存储密钥或证书，方案一的数据库端解密是更安全的选择。
• 数据类型匹配：确保EncryptedPhone在LINQ to SQL的实体类中是byte[]类型，对应数据库的varbinary类型。

内容的提问来源于stack exchange，提问作者azhar rahi