Column Encryption (Always Encrypted) support in SqlClient for .NET Core

[ghost]

When using a DbContext with a ConnectionString like

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=.\\SQLEXPRESS;Database=EFGetStarted.AspNetCore.NewDb;Trusted_Connection=True;Column Encryption Setting=Enabled;MultipleActiveResultSets=true;"
  }
}

I get the following error
StackTrace

{System.ArgumentException: Keyword not supported: 'column encryption setting'.
   at System.Data.Common.DbConnectionOptions.ParseInternal(Dictionary`2 parsetable, String connectionString, Boolean buildChain, Dictionary`2 synonyms)
   at System.Data.Common.DbConnectionOptions..ctor(String connectionString, Dictionary`2 synonyms)
   at System.Data.SqlClient.SqlConnectionString..ctor(String connectionString)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(DbConnectionPoolKey key, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions)
   at System.Data.SqlClient.SqlConnection.ConnectionString_Set(DbConnectionPoolKey key)
   at System.Data.SqlClient.SqlConnection.set_ConnectionString(String value)
   at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerConnection.CreateDbConnection()
   at Microsoft.EntityFrameworkCore.Internal.LazyRef`1.get_Value()
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open()
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.BeginTransaction(IsolationLevel isolationLevel)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(Tuple`2 parameters)
   at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](Func`2 operation, Func`2 verifySucceeded, TState state)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IReadOnlyList`1 entriesToSave)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at xBN.Repository.Repositories.EntityBaseRepository`1.Commit() in C:\!Checkouts\git checkouts\xBN\xBN.Service\Repositories\EntityBaseRepository.cs:line 98
   at xBN.API.Controllers.CompanyController.Get(Int32 id) in C:\!Checkouts\git checkouts\xBN\xBN.API\Controllers\CompanyController.cs:line 37
   at lambda_method(Closure , Object , Object[] )
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeActionMethodAsync>d__27.MoveNext()}

The issue is about adding support for the Column Encryption Setting to the .NET Core ADO.NET SqlClient.

[jasondaicoder]

guys, has this been fixed or?

[corivera]

CoreFx SqlClient does not currently support column encryption

[jasondaicoder]

@corivera, do we have any plans on this feature?

[corivera]

We don't have any plans for it at the moment, we (SqlClient team) will have to discuss it.

[jasondaicoder]

Thanks for you info. I think it would be highly demanded feature as column encryption is widely used. Hope to see this feature being implemented soon.

[divega]

Related dotnet/efcore#9142.

[jdelrue]

Need this

[LarrySmith-1437]

The lack of Always Encrypted is a real drag on promoting .net Core where I am, because security is paramount.

[wizofaus]

Actually I had reported this separately, so technically my issue is a duplicate. But I've been looking at explicit server-based value encryption (using ENCRYPTBYKEY etc), and it seems there are no .net libraries out there that support this either?

[aspnerd]

What is going on with this? We really need this now!

[danmoseley]

@divega @corivera is this in plan? If not is it feasible for community to port?

[mattdkx]

Just starting a new enterprise project, we had decided on .Net Core but are switching to .Net Framework over this issue - I just removed the .Net Core projects from the solution and am retargeting ASP.Net Core at the .Net Framework. It is a shame Core isn't yet mature enough for us, but at the end of the day you've got the resources you've got.

[JohnKoz]

@corivera : Similar to @mattd73uk, we've started an enterprise project and just found out always encrypted is not supported in .Net Core. Basically we will have to switch back to the .Net Framework unless we hear otherwise. Is there any indication on when this feature would likely be added?

[se7vanj]

We had the same demand during mid of 2017 in one of our enterprise project that uses AE feature from a .NET Core API, reached through the Microsoft premier support, but could not find any timelines for this functionality, I don't think its even present in their future milestones.

Wondering when they will add support for column encryption in CoreFX sql client.

Finally, we had to target the API to .NET Framework and use EF 6.x instead of Core.

[danmoseley]

@divega @corivera could you please share thoughts? Clearly it's a blocking issue for a number of folks here. Is it something community could help port?

[louislewis2]

Ouch, this has burned me today horribly. Tomorrow we are deploying to production. Which means that today we start migrating users across from an old system across to a new one.

Then I get hit by this......

[ld0614]

While I was looking forward to trying .NET Core out for a new project the lack of support for column encryption and the lack of support for AccessToken means I will have to build the project on .Net Framework instead.

[David-Engel]

Always Encrypted is definitely still on our radar. We don't have a timeline for it at the moment, though. There are a few items that we believe are higher on the list. We are open to prioritization changes, though (vote up comments on this issue).

[rudiv]

I've commented on another thread regarding this already - but with your recent push on GDPR it shocks me that this isn't available. It's fairly critical to stop people from rolling their own methods to encrypt data in columns (which as we know from experience never ends well).

Being forced to use .NET Framework for such "basic" (from an outside perspective) functionality isn't really acceptable, when there's such a huge push to use .NET Core.

[aspnerd]

@David-Engel We cannot use the latest SQL and .net Core due to this. We had hours and hours of rework which had to be done because of this and we aren't the only ones. This should have already been done imo.

[LarrySmith-1437]

Here we are at the midpoint of 2018. To find out now that this critical piece of functionality is even at this late date not under development is deeply disappointing.

Like these others, I've had to push back on timelines to move to .net core.

Prospective dates are in order here. When?

[aspnerd]

Always Encrypted came out in 2016. We are a complete newer version of SQL Server ahead and at least 4 versions of .net framework ahead since it came out and we still don't have support for core. This makes no sense!

[SariDev]

I hope to see this feature in the next version of System.Data.SqlClient.
Support for Always Encrypted is required for enterprise applications that rely on SQL Server and it's security features.
Releasing this together with .NET Core 2.2 later this year should be high priority.

[filippostefanello]

I share your hope as I need a solution to comply the GDPR's rules.

[armordba]

With all the data security push from GDPR and others, we really need this fix ASAP

[RJoseSilva]

we really need this fix ASAP. This feature will be release with .Net Core 2.2 ?

[erikrenaud]

Any news on this ? would be nice to not have to use .net framework for bigger projects...

[David-Engel]

We pushed an update to the Microsoft.Data.SqlClient 1.0 preview. It should resolve the issues noted around Always Encrypted.

@sebader - I want to note, though, custom key store providers like the Azure Key Vault provider need to be updated to work with Microsoft.Data.SqlClient. Only the built-in providers will work until that happens (hopefully soon!). So the example you were following using Azure Key Vault still won't work. But this example, which uses the built-in Windows Certificate Store provider, should work as expected after changing from System.Data.SqlClient to Microsoft.Data.SqlClient:
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-always-encrypted

@divega

[sebader]

thanks for the update @David-Engel ! Too bad though about KeyVault missing yet. I'm using Azure Functions so Windows Cert store is no good for me ;-)
Where could we track the progress of their update?

[David-Engel]

@sebader I'm not sure. We've pinged the team who owns it. I don't think that project is public.

[divega]

As recently announced in the .NET Blog, focus on new SqlClient features an improvements is moving to the new Microsoft.Data.SqlClient package. For this reason, we are moving this issue to the new repo at https://github.com/dotnet/SqlClient. We will still use https://github.com/dotnet/corefx to track issues on other providers like System.Data.Odbc and System.Data.OleDB, and general ADO.NET and .NET data access issues.

[lsuarez5280]

@David-Engel could you recommend the best place to watch for updated Azure Key Vault support? We're using certificate store for the moment but trying to catch all the places that need cert updates is a pain and we'd love to migrate in the near term.

[zenunits]

@divega How can this change be incorporated into the EFCore.SqlServer? because System.Data.SqlClient is one of the dependencies of EFCore.SqlServer.

[lsuarez5280]

@kedarchinchvalkar There are two relatively small steps:

1. Register your DB context for DI using the overload provided for specifying the abstract DbConnection, constructing instead a Microsoft.Data.SqlClient.SqlConnection (probably optional).
2. Replace the ISqlServerConnection service with one targeting Microsoft.Data.SqlClient for both SqlConnection and SqlConnectionStringBuilder.

I lifted the code from GitHub for the last Microsoft.EntityFrameworkCore.SqlServer 2.x release for SqlServerConnection and updated it with proper targets as follows:

using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal;
using Microsoft.EntityFrameworkCore.Storage;
using System.Data.Common;

namespace My.Storage
{
    /// <summary>
    ///     This API supports the Entity Framework Core infrastructure.
    /// </summary>
    public class SqlServerConnection : RelationalConnection, ISqlServerConnection
    {
        private bool? _multipleActiveResultSetsEnabled;

        // Compensate for slow SQL Server database creation
        private const int DefaultMasterConnectionCommandTimeout = 60;

        /// <summary>
        ///     This API supports the Entity Framework Core infrastructure and is not intended to be used
        ///     directly from your code. This API may change or be removed in future releases.
        /// </summary>
        public SqlServerConnection(RelationalConnectionDependencies dependencies)
            : base(dependencies)
        {
        }

        /// <summary>
        ///     This API supports the Entity Framework Core infrastructure and is not intended to be used
        ///     directly from your code. This API may change or be removed in future releases.
        /// </summary>
        protected override DbConnection CreateDbConnection() => new SqlConnection(ConnectionString);

        /// <summary>
        ///     This API supports the Entity Framework Core infrastructure and is not intended to be used
        ///     directly from your code. This API may change or be removed in future releases.
        /// </summary>
        public virtual ISqlServerConnection CreateMasterConnection()
        {
            var connectionStringBuilder = new SqlConnectionStringBuilder(ConnectionString)
            {
                InitialCatalog = "master"
            };
            connectionStringBuilder.Remove("AttachDBFilename");

            var contextOptions = new DbContextOptionsBuilder()
                .UseSqlServer(
                    connectionStringBuilder.ConnectionString,
                    b => b.CommandTimeout(CommandTimeout ?? DefaultMasterConnectionCommandTimeout))
                .Options;

            return new SqlServerConnection(Dependencies.With(contextOptions));
        }

        /// <summary>
        ///     This API supports the Entity Framework Core infrastructure and is not intended to be used
        ///     directly from your code. This API may change or be removed in future releases.
        /// </summary>
        public override bool IsMultipleActiveResultSetsEnabled
            => (bool)(_multipleActiveResultSetsEnabled
                      ?? (_multipleActiveResultSetsEnabled
                          = new SqlConnectionStringBuilder(ConnectionString).MultipleActiveResultSets));

        /// <summary>
        ///     Indicates whether the store connection supports ambient transactions
        /// </summary>
        protected override bool SupportsAmbientTransactions => true;
    }
}

Then I updated my service registration for AddDbContext as follows, where I construct a Microsoft.Data.SqlClient.SqlConnection and SqlServerConnection is using SqlServerConnection = My.Storage.SqlServerConnection;:

public void ConfigureServices(IServiceCollection services)
{
    services
        .AddDbContext<MyContext>(opts =>
            opts
                .UseSqlServer(new SqlConnection(Configuration.GetConnectionString("DefaultConnection")))
                .ReplaceService<ISqlServerConnection, SqlServerConnection>()) // TODO: Remove after transition to Core 3
}

Works like a charm. I even think the step of constructing the DbConnection may be unnecessary and you can use the raw string value for UseSqlServer(), but haven't tried it. Feel free to experiment.

[David-Engel]

@David-Engel could you recommend the best place to watch for updated Azure Key Vault support? We're using certificate store for the moment but trying to catch all the places that need cert updates is a pain and we'd love to migrate in the near term.

@lsuarez5280 I'm not sure. I'll be sure to update this issue if I hear anything, though.

[divega]

@divega How can this change be incorporated into the EFCore.SqlServer? because System.Data.SqlClient is one of the dependencies of EFCore.SqlServer.

@kedarchinchvalkar, EF Core 3.0 will switch to depend directly on Microsoft.Data.SqlClient. This change is coming probably in the next preview.

[divega]

@David-Engel should we have a separate issue to track Azure Key Vault support? I am not very familiar with the feature, but it seems we need to follow up internally with the owners of https://www.nuget.org/packages/Microsoft.SqlServer.Management.AlwaysEncrypted.AzureKeyVaultProvider.

[David-Engel]

@divega I've already notified the team that owns the AKV library. Given the priority of AE support in SqlClient on netcore, I'm certainly keeping tabs on their progress. I've filed #111 here for tracking.

[zenunits]

@kedarchinchvalkar There are two relatively small steps:

1. Register your DB context for DI using the overload provided for specifying the abstract DbConnection, constructing instead a Microsoft.Data.SqlClient.SqlConnection (probably optional).
2. Replace the ISqlServerConnection service with one targeting Microsoft.Data.SqlClient for both SqlConnection and SqlConnectionStringBuilder.

I lifted the code from GitHub for the last Microsoft.EntityFrameworkCore.SqlServer 2.x release for SqlServerConnection and updated it with proper targets as follows:

using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal;
using Microsoft.EntityFrameworkCore.Storage;
using System.Data.Common;

namespace My.Storage
{
    /// <summary>
    ///     This API supports the Entity Framework Core infrastructure.
    /// </summary>
    public class SqlServerConnection : RelationalConnection, ISqlServerConnection
    {
        private bool? _multipleActiveResultSetsEnabled;

        // Compensate for slow SQL Server database creation
        private const int DefaultMasterConnectionCommandTimeout = 60;

        /// <summary>
        ///     This API supports the Entity Framework Core infrastructure and is not intended to be used
        ///     directly from your code. This API may change or be removed in future releases.
        /// </summary>
        public SqlServerConnection(RelationalConnectionDependencies dependencies)
            : base(dependencies)
        {
        }

        /// <summary>
        ///     This API supports the Entity Framework Core infrastructure and is not intended to be used
        ///     directly from your code. This API may change or be removed in future releases.
        /// </summary>
        protected override DbConnection CreateDbConnection() => new SqlConnection(ConnectionString);

        /// <summary>
        ///     This API supports the Entity Framework Core infrastructure and is not intended to be used
        ///     directly from your code. This API may change or be removed in future releases.
        /// </summary>
        public virtual ISqlServerConnection CreateMasterConnection()
        {
            var connectionStringBuilder = new SqlConnectionStringBuilder(ConnectionString)
            {
                InitialCatalog = "master"
            };
            connectionStringBuilder.Remove("AttachDBFilename");

            var contextOptions = new DbContextOptionsBuilder()
                .UseSqlServer(
                    connectionStringBuilder.ConnectionString,
                    b => b.CommandTimeout(CommandTimeout ?? DefaultMasterConnectionCommandTimeout))
                .Options;

            return new SqlServerConnection(Dependencies.With(contextOptions));
        }

        /// <summary>
        ///     This API supports the Entity Framework Core infrastructure and is not intended to be used
        ///     directly from your code. This API may change or be removed in future releases.
        /// </summary>
        public override bool IsMultipleActiveResultSetsEnabled
            => (bool)(_multipleActiveResultSetsEnabled
                      ?? (_multipleActiveResultSetsEnabled
                          = new SqlConnectionStringBuilder(ConnectionString).MultipleActiveResultSets));

        /// <summary>
        ///     Indicates whether the store connection supports ambient transactions
        /// </summary>
        protected override bool SupportsAmbientTransactions => true;
    }
}

Then I updated my service registration for AddDbContext as follows, where I construct a Microsoft.Data.SqlClient.SqlConnection and SqlServerConnection is using SqlServerConnection = My.Storage.SqlServerConnection;:

public void ConfigureServices(IServiceCollection services)
{
    services
        .AddDbContext<MyContext>(opts =>
            opts
                .UseSqlServer(new SqlConnection(Configuration.GetConnectionString("DefaultConnection")))
                .ReplaceService<ISqlServerConnection, SqlServerConnection>()) // TODO: Remove after transition to Core 3
}

Works like a charm. I even think the step of constructing the DbConnection may be unnecessary and you can use the raw string value for UseSqlServer(), but haven't tried it. Feel free to experiment.

Its giving an error on UserSqlServer() as,
The call is ambiguous between the following methods or properties:

'Microsoft.EntityFrameworkCore.SqlServerDbContextOptionsExtensions.UseSqlServer(Microsoft.EntityFrameworkCore.DbContextOptionsBuilder, System.Data.Common.DbConnection, System.Action<Microsoft.EntityFrameworkCore.Infrastructure.SqlServerDbContextOptionsBuilder>)'
and
'Microsoft.EntityFrameworkCore.SqlServerDbContextOptionsExtensions.UseSqlServer(Microsoft.EntityFrameworkCore.DbContextOptionsBuilder, System.Data.Common.DbConnection, System.Action<Microsoft.EntityFrameworkCore.Infrastructure.SqlServerDbContextOptionsBuilder>)'

[lsuarez5280]

Its giving an error on UserSqlServer() as,
The call is ambiguous between the following methods or properties:
'Microsoft.EntityFrameworkCore.SqlServerDbContextOptionsExtensions.UseSqlServer(Microsoft.EntityFrameworkCore.DbContextOptionsBuilder, System.Data.Common.DbConnection, System.Action<Microsoft.EntityFrameworkCore.Infrastructure.SqlServerDbContextOptionsBuilder>)'
and
'Microsoft.EntityFrameworkCore.SqlServerDbContextOptionsExtensions.UseSqlServer(Microsoft.EntityFrameworkCore.DbContextOptionsBuilder, System.Data.Common.DbConnection, System.Action<Microsoft.EntityFrameworkCore.Infrastructure.SqlServerDbContextOptionsBuilder>)'

@kedarchinchvalkar That sounds like you may have reference problems in your project with multiple SQL Server EF Relational packages imported somehow, but is not related to the code. Those method prototypes are exactly the same.

[zenunits]

Its giving an error on UserSqlServer() as,
The call is ambiguous between the following methods or properties:
'Microsoft.EntityFrameworkCore.SqlServerDbContextOptionsExtensions.UseSqlServer(Microsoft.EntityFrameworkCore.DbContextOptionsBuilder, System.Data.Common.DbConnection, System.Action<Microsoft.EntityFrameworkCore.Infrastructure.SqlServerDbContextOptionsBuilder>)'
and
'Microsoft.EntityFrameworkCore.SqlServerDbContextOptionsExtensions.UseSqlServer(Microsoft.EntityFrameworkCore.DbContextOptionsBuilder, System.Data.Common.DbConnection, System.Action<Microsoft.EntityFrameworkCore.Infrastructure.SqlServerDbContextOptionsBuilder>)'

@kedarchinchvalkar That sounds like you may have reference problems in your project with multiple SQL Server EF Relational packages imported somehow, but is not related to the code. Those method prototypes are exactly the same.

Yes, You're right. There was a duplicate reference due to "Microsoft.AspNetCore.All" package. Thank you so much.

Program now builds perfectly. I have applied column encryption on database table columns.
but while reading data I am getting following error,

InvalidCastException: Unable to cast object of type 'System.Byte[]' to type 'System.String'.
Microsoft.Data.SqlClient.SqlBuffer.get_String()
lambda_method(Closure , DbDataReader )
Microsoft.EntityFrameworkCore.Storage.Internal.TypedRelationalValueBufferFactory.Create(DbDataReader dataReader)
Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable+Enumerator.BufferlessMoveNext(DbContext _, bool buffer)
Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute<TState, TResult>(TState state, Func<DbContext, TState, TResult> operation, Func<DbContext, TState, ExecutionResult> verifySucceeded) in SqlServerExecutionStrategy.cs

        TState state,
        Func<DbContext, TState, TResult> operation,
        Func<DbContext, TState, ExecutionResult<TResult>> verifySucceeded)
    {
        try
        {
            return operation(Dependencies.CurrentDbContext.Context, state);
        }
        catch (Exception ex) when (ExecutionStrategy.CallOnWrappedException(ex, SqlServerTransientExceptionDetector.ShouldRetryOn))
        {
            throw new InvalidOperationException(SqlServerStrings.TransientExceptionDetected, ex);
        }
    }

Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable+Enumerator.MoveNext()
Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider._TrackEntities<TOut, TIn>(IEnumerable results, QueryContext queryContext, IList entityTrackingInfos, IList<Func<TIn, object>> entityAccessors)+MoveNext()
Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider+ExceptionInterceptor+EnumeratorExceptionInterceptor.MoveNext()
Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.Load(IQueryable source)
Microsoft.EntityFrameworkCore.Internal.EntityFinder.Load(INavigation navigation, InternalEntityEntry entry)
Microsoft.EntityFrameworkCore.Internal.LazyLoader.Load(object entity, string navigationName)
Microsoft.EntityFrameworkCore.Proxies.Internal.LazyLoadingInterceptor.Intercept(IInvocation invocation)
Castle.DynamicProxy.AbstractInvocation.Proceed()
Castle.Proxies.WorkflowProxy.get_FormData()
lambda_method(Closure , Workflow , SurveyViewModel , ResolutionContext )

[zenunits]

Any updates on above?

[lsuarez5280]

@kedarchinchvalkar I'm gonna guess that you haven't actually added Column Encryption Setting=Enabled in your connection string. At this point, I suggest you don't use this issue to continue the conversation. Either seek community support for utilizing the connector or open a new issue so your questions can be addressed there.

[zenunits]

@kedarchinchvalkar I'm gonna guess that you haven't actually added Column Encryption Setting=Enabled in your connection string. At this point, I suggest you don't use this issue to continue the conversation. Either seek community support for utilizing the connector or open a new issue so your questions can be addressed there.

@Isuarez5280 : Thank you so much for the suggestion. I will open it as a new issue.

[zenunits]

Thank you so much @lsuarez5280 and @divega
Your solution works like a charm.

[cheenamalhotra]

We believe the originally reported issue has been addressed in Microsoft.Data.SqlClient (NetCore), please open new issues for any specific usecase, closing the issue.