从.NET Core 2.1使用SQL Server的始终加密 [英] Using Always Encrypted of SQL Server from .NET Core 2.1

问题描述

我知道Microsoft尚未指定任何计划从Core内部支持Always Encrypted。
但是，经典.NET Framework（4.5及更高版本）对此提供了支持。
我们的Core 2.1项目对Always Always加密的使用仅限于两​​个简单的查询，除了从Core 2.1降级之外，我们愿意采用其他途径来使用它。

有很多解决这些问题的远程方法，但是它们涉及到远程处理（对于另一个经典的.NET，它是WCF或REST）或通过Service Fabric Actor（我们之前使用过）来解决。

是否有任何干净的流程方式？例如，通过ODBC连接这两个查询还是类似的东西？

N.B。我们正在Windows上运行。

解决方案

根据

如果我从连接字符串中删除了 ColumnEncryption = Enabled，则输出将变为 SSN：System.Byte []，即未解密SSN。

我希望这会有所帮助！

I am aware that Microsoft has not specified any plan to support Always Encrypted from within Core, yet. However, this is supported by classical .NET Framework (4.5 onward). Our Core 2.1 project's usage of Always Encrypted is limited to two simple queries and we are willing to take alternative routes to use it other than downgrading from Core 2.1.

There are many remote ways to solve these problems but they involve remoting (WCF or REST for another classical .NET) or through a Service Fabric Actor (we were using this before).

Is there any clean in-process way to do it? Connecting through ODBC, for example, for these two queries or something like that?

N.B. We are running on Windows.

解决方案

According Microsoft Docs latest ODBC drivers supports Always Encrypted. The following code works.

using System;
using System.Data.Odbc;

namespace ConsoleApp2
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var connection = new OdbcConnection(
                "Driver={ODBC Driver 17 for SQL Server};DSN=SQL2016;Server={SQL2016};Trusted_Connection=yes;ColumnEncryption=Enabled;Database=AndreyTest;"))
            {
                using (var cmd = new OdbcCommand("select SSN from TestTable", connection))
                {
                    connection.Open();
                    Console.WriteLine("Connected");
                    Console.WriteLine("SSN: " + Convert.ToString(cmd.ExecuteScalar()));
                    Console.ReadLine();
                    connection.Close();
                }
            }
        }
    }
}

If I remove "ColumnEncryption=Enabled" from the connection string, the output becomes "SSN: System.Byte[]", i.e. the SSN isn't decrypted.

I hope this will help!

这篇关于从.NET Core 2.1使用SQL Server的始终加密的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！