Using Always Encrypted of SQL Server from .NET Cor

问题:

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.

回答1:

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!