Single Store加密数据在SQL Server解密的替代方案咨询

方案1：跨库兼容AES模式加密+SQL Server自定义解密逻辑 #

因为SQL Server的DECRYPTBYKEY仅兼容自身加密格式，可改用双方都支持的AES-128-CBC模式，在SQL Server中通过自定义逻辑解密：

• Single Store加密端：使用带固定/随机初始化向量（IV）的AES-128-CBC加密，将IV与加密结果拼接存储，方便SQL Server拆分：

  -- 示例：将16字节IV转十六进制后，与加密结果拼接
  SELECT 
    CONCAT(HEX('0123456789abcdef'), HEX(AES_ENCRYPT(plain_column, 'your_aes_key', 'aes-128-cbc', '0123456789abcdef'))) AS encrypted_data
  FROM your_single_store_table;
  

• SQL Server解密端：开启CLR支持后，创建自定义CLR函数实现AES-128-CBC解密。核心逻辑示例（C#）：

  [SqlFunction(DataAccess = DataAccessKind.None)]
  public static SqlString DecryptAesCbc(SqlString encryptedHex, SqlString key, SqlString ivHex)
  {
      byte[] encryptedBytes = StringToByteArray(encryptedHex.Value);
      byte[] keyBytes = Encoding.UTF8.GetBytes(key.Value);
      byte[] ivBytes = StringToByteArray(ivHex.Value);
      
      using (var aes = Aes.Create())
      {
          aes.Key = keyBytes;
          aes.IV = ivBytes;
          aes.Mode = CipherMode.CBC;
          aes.Padding = PaddingMode.PKCS7;
          
          using (var decryptor = aes.CreateDecryptor())
          using (var ms = new MemoryStream(encryptedBytes))
          using (var cs = new CryptoStream(ms, decryptor, CryptoStreamMode.Read))
          using (var sr = new StreamReader(cs))
          {
              return new SqlString(sr.ReadToEnd());
          }
      }
  }
  
  private static byte[] StringToByteArray(string hex)
  {
      int length = hex.Length;
      byte[] bytes = new byte[length / 2];
      for (int i = 0; i < length; i += 2)
          bytes[i / 2] = Convert.ToByte(hex.Substring(i, 2), 16);
      return bytes;
  }
  

  注册CLR函数后，即可在SQL Server中调用解密。

方案2：迁移前解密，迁移后用SQL Server原生加密 #

绕过跨库加密兼容问题，先在Single Store解密数据，再导入SQL Server后用其原生加密：

1. Single Store导出明文：

   SELECT 
      id, 
      AES_DECRYPT(encrypted_column, 'your_key', 'aes-128-ecb') AS plain_column
   INTO OUTFILE '/path/to/plain_data.csv'
   FROM your_single_store_table;
   

2. SQL Server导入并加密：

   -- 创建加密依赖的证书与对称密钥
   CREATE CERTIFICATE DataEncryptCert WITH SUBJECT = 'Data Encryption Cert';
   CREATE SYMMETRIC_KEY DataEncryptKey WITH ALGORITHM = AES_128 ENCRYPTION BY CERTIFICATE DataEncryptCert;
   
   -- 导入明文到临时表
   BULK INSERT #TempPlainData
   FROM '/path/to/plain_data.csv'
   WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n');
   
   -- 加密并写入目标表
   OPEN SYMMETRIC KEY DataEncryptKey DECRYPTION BY CERTIFICATE DataEncryptCert;
   INSERT INTO your_sqlserver_table (id, encrypted_column)
   SELECT id, ENCRYPTBYKEY(KEY_GUID('DataEncryptKey'), plain_column)
   FROM #TempPlainData;
   CLOSE SYMMETRIC KEY DataEncryptKey;
   
   -- 清理临时表
   DROP TABLE #TempPlainData;
   

方案3：应用层中转加密/解密 #

将加密解密逻辑移至应用层，规避数据库端格式差异：

• 应用从Single Store读取加密列，用AES-128-ECB算法解密得到明文；
• 应用通过SSL连接将明文传递至SQL Server，调用ENCRYPTBYKEY完成加密存储，或先写入临时表后由SQL Server触发器自动加密；
• 注意：传输过程需确保明文数据的安全性。

方案4：模拟SQL Server ENCRYPTBYKEY加密逻辑（调试成本较高） #

SQL Server的ENCRYPTBYKEY默认用AES-256-CBC算法，会自动生成16字节IV并附加在加密结果前。若能在Single Store中完全模拟该格式：

• 生成16字节随机IV；
• 用AES-256-CBC加密明文；
• 将IV与加密结果拼接后存储；
• 在SQL Server中使用相同密钥、证书配置的DECRYPTBYKEY解密。

此方案需精准对齐双方的填充方式、密钥格式等参数，调试难度较大。

内容的提问来源于stack exchange，提问作者shaikh shaheed