Using Always Encrypted of SQL Server from .NET Core 2.1

Question

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.

Andrey Nikolov · Accepted Answer · 2018-11-02 16:18:41Z

2

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!

answered Nov 2, 2018 at 16:18

Andrey Nikolov

13.5k3 gold badges26 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Adam Over a year ago

Strange that nobody is aware of this! I modified the connection string to be standalone: Driver={ODBC Driver 13 for SQL Server};Trusted_Connection=yes;ColumnEncryption=Enabled;Server=localhost;Database=MyDbName;

Collectives™ on Stack Overflow

Using Always Encrypted of SQL Server from .NET Core 2.1

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related