求助：无法将Always Encrypted加密的varbinary列转换为XML等类型

这个问题我之前帮别人排查过，核心原因是Always Encrypted的设计限制：服务器端永远无法访问加密列的明文数据，所有解密操作都是在客户端驱动中完成的。你直接在T-SQL里写CONVERT(xml, datavalue2)，服务器端拿到的还是加密的二进制数据，自然没法完成转换，所以抛出那个错误。

下面给你两个可行的解决方案：

方案1：通过客户端应用程序处理（推荐） #

利用支持Always Encrypted的客户端驱动（比如.NET Framework 4.6.1+、ODBC 13.1+等），先把加密的varbinary数据拉到客户端，驱动会自动完成解密，然后在客户端把明文二进制转成XML或字符串。

举个C#的示例代码：

using System.Data.SqlClient;
using System.Xml;
using System.Text;

// 注意连接字符串必须加上Column Encryption Setting=Enabled
string connString = "Server=你的SQL服务器地址;Database=TestDB;Integrated Security=True;Column Encryption Setting=Enabled;";

using (SqlConnection conn = new SqlConnection(connString))
{
    conn.Open();
    string query = "SELECT TOP 10 datavalue2 FROM [TestDB].[dbo].[DataCalc]";
    
    using (SqlCommand cmd = new SqlCommand(query, conn))
    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            // 读取客户端解密后的明文字节数组
            byte[] plainBytes = reader.GetSqlBytes(0).Buffer;
            
            // 根据原XML的编码转换为字符串（如果是UTF-16就用Encoding.Unicode）
            string xmlContent = Encoding.UTF8.GetString(plainBytes);
            
            // 解析为XML对象
            XmlDocument xmlDoc = new XmlDocument();
            xmlDoc.LoadXml(xmlContent);
            
            // 这里可以处理XML内容，比如输出或业务逻辑
            Console.WriteLine(xmlDoc.InnerXml);
        }
    }
}

方案2：用PowerShell快速验证 #

如果只是临时验证数据，用PowerShell脚本也能搞定，原理和客户端程序一样：

$connString = "Server=你的SQL服务器地址;Database=TestDB;Integrated Security=True;Column Encryption Setting=Enabled;"
$conn = New-Object System.Data.SqlClient.SqlConnection($connString)
$conn.Open()

$query = "SELECT TOP 10 datavalue2 FROM [TestDB].[dbo].[DataCalc]"
$cmd = New-Object System.Data.SqlClient.SqlCommand($query, $conn)
$reader = $cmd.ExecuteReader()

while ($reader.Read()) {
    $plainBytes = $reader.GetSqlBytes(0).Buffer
    # 同样注意编码匹配原XML
    $xmlString = [System.Text.Encoding]::UTF8.GetString($plainBytes)
    $xmlObj = [xml]$xmlString
    # 输出XML内容
    Write-Host $xmlObj.InnerXml
}

$reader.Close()
$conn.Close()

关键注意点 #

• 连接字符串必须包含Column Encryption Setting=Enabled，否则客户端驱动不会自动解密。
• 转换时的编码要和你当初把XML转成varbinary时的编码一致（比如原XML是用CAST(xmlColumn AS varbinary(max))存的，那编码是UTF-16，要用Encoding.Unicode转换）。

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