Always Encrypted not working in a linux docker container

[hkruijss]

Hi,

We created a small test project using always encrypted using .net core 3.0 and the Microsoft.Data.SqlClient package.

Everything works ok when running the application in a windows environment but when running in a linux docker container we get a "platform not supported" error message when accessing an encrypted column.

[yukiwongky]

when accessing an encrypted column

@hkruijss how are you accessing the column? Which API were you using? Does it work if you try to do the same thing with an non-encrypted column?

[hkruijss]

These are the steps we used to reproduce the issue:

Create a new test database in a developer installation of SQL Server 2017 and execute below script: /** Object: Table [dbo].[Loan] Script Date: 10/25/2019 8:56:53 AM **/ SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

CREATE TABLE [dbo].[Loan]( [Id] [int] IDENTITY(1,1) NOT NULL, [Price] [money] NULL, [Fico] [int] NULL, [PropertyStreetAddress] nvarchar COLLATE Latin1_General_BIN2 ENCRYPTED WITH ( COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL, CONSTRAINT [PK_Loan] PRIMARY KEY CLUSTERED ( [Id] ASC ) WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO

Create a new asp.net core webapi project and include docker (to linux) support Add a reference to the latest stable versions of the following nuget packages: Microsoft.Data.SqlClient (System.Data.SqlClient doesn’t support Always Encrypted) Dapper (Without dapper the code is a little bit more complicated but we run into the exact same issue)

In our case the connection string was injected in the startup class since we wanted to try deploying to multiple environments, but can also be hardcoded.

From the controller call the following code:

public class Loan { public int Id { get; set; } public decimal? Price { get; set; } public int? Fico { get; set; } public string PropertyStreetAddress { get; set; } }

public Loan GetLoanById(int id) { using (var connection = new SqlConnection(configuration.ConnectionString)) { return connection.Query("SELECT * FROM LOAN WHERE Id=@id", new { id }).SingleOrDefault(); } }

Make sure the appsettings.json has correct connection string including the “Column Encryption Setting=enabled” part of the connection string.

We verified that this quick prototype works correctly in the following cases: • Deployed to a windows box (all situations) • Deployed to a docker container inside a linux box if we use no encrypted columns (We used the one provided by Microsoft out of the box with visual studio)

This doesn’t work if we deploy to a docker linux container, which is where we have our issue. The error message we get is: “Platform not supported”

[cheenamalhotra]

Hi @hkruijss

If you're working with "MSSQL_CERTIFICATE_STORE", that's only supported on Windows, not on Linux.

For Linux client applications, Azure Key Vault Provider can be used, currently available in preview: NuGet: AzureKeyVaultProvider

A sample application on how to work with AKV provider is available here: AzureKeyVaultProviderExample.cs

[cheenamalhotra]

Hi @hkruijss

Closing the issue as stable version of AKV provider has been released that can be used on Linux. Other Providers are Windows specific and will not work on Linux.

[zorrme]

Does this work with SQL database not deployed to Azure ?

[cheenamalhotra]

@zorrme

Could you clarify what exactly are you trying to implement? A "SQL Database not deployed to Azure", is it different than on-premise SQL Server?

[zorrme]

Yes, deployed to AWS RDS

[cheenamalhotra]

Please provide us a repro application to understand what are you asking about, maybe open a new issue if you face any problems.

[zorrme]

Just want to know whether we can use AKV with on premise encrypted SQL server ?

[cheenamalhotra]

Yes it's supported by the driver for all SQL Servers that support the option.

[zorrme]

Cool, do we have an example similar to https://github.com/dotnet/SqlClient/blob/master/doc/samples/AzureKeyVaultProviderExample.cs ?

[cheenamalhotra]

You should be able to provide you instance's connection string and AKV properties in this example and the same example should work. If you face any issues let us know!

[zorrme]

How do I specify this: ? What is the format ?

static readonly string s_akvUrl = "https://{KeyVaultName}.vault.azure.net/keys/{Key}/{KeyIdentifier}";

[alirashidi1993]

we are facing the same issue . the problem with us is that we dont have access to AKV. is there any other storage we can store a COLUMN MASTER KEY? We are using Docker Linux and facing the same platform_not_supported error and we also have no access to AKV. is there any other way we can use Always Encrypted in Linux? We also dont have any HSM to use.