Search code examples
sql-serverdockerasp.net-coreencryptionalways-encrypted

Using SQL server Always Encrypted in asp.net core app that is deployed to docker/ linux

I have created an ASP.NET Core Web API app that connects to a SQL Server database. I have a table in which I need to use SQL Server always encryption.

I'm creating a column master key with the following T-SQL:

 CREATE COLUMN MASTER KEY [MyCMK]
 WITH
 (
 KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
 KEY_PATH = N'CurrentUser/my/2DB1E2F1BE5E2A640FB1626895DB174D1A3176DD'
 );

and then a column encryption key using this column master key as follows:

 CREATE COLUMN ENCRYPTION KEY [MyCEK]
 WITH VALUES
 (
 COLUMN_MASTER_KEY = [MyCMK],
 ALGORITHM = 'RSA_OAEP',
 ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F0032006400620031006500320066003100620065003500650032006100360034003000660062003100360032003600380039003500640062003100370034006400310061003300310037003600640064002E9E339743391E3829BF1A7B9DF9BAF6858F7D46928D9285A01C6833A049F0DE3A01192B274CD793AD49572F372F79D825B999A4ED2DE824D694A5FA0AC42D62CCA8CCC20D4182F31B52C919E343BF945E518C836F2444304A18307A03C33C1BFA6FB7938F4FC004B11FD4EBD8FC773292689936EAAC6A0B0CD16B5BA937F0169FBC75B3380E23A196DF905292CEDA6F4DEC327F29EBF8B65CD7B073B4BEB07D2B3CC3E6E24951B27B7E0B1ACE272DEA133C41932C72381262B74A7FABF4E84129A3F4D36639D662ECBD4C0D25CA360248559B4479B7076F9C8BD352CBCB4D6460201DBB5CC734139F5032AE241F8491779BBE10554568554AC9530EB76AE2AD560E8D4CB18FA6DDAA7763E873DCA23D582176E84D78BF2E59D2ED2D926932D05231F52D7E9E01AADC08A039DDC082F0F2B67115922E01772741CE19DE63C7AA4B6B2E3A120717FD04A6A31FB72713CA603E5ADF701497D5EAE1E3920DDF24EB9DD367CBAB8CEDFEDBEAEDC7CD8C2123066AC5BCE552AB3E3C211D38DEDDCB3300EFC652FB03443EE91429CFEE802484FD84E7FA1194CD2A753D0CCA29FAC8286F79390C3E24B5F0ACA479FD5F3BBB78F82A4C4F32FF33C878B1895A0C6CB57F05CBE69FC2D1A26236102F19F2256FE7352A5CB3B6700F373B6DBC7E022EC5DBFE405BCAA96B5B0A070FB704E251F804B9F5AC2EFE4E75C8D02B3DCEA21B90C4
 );

Then I create my table using TSQL which has 2 encrypted columns:

CREATE TABLE [Appraisal].[Answer]
(
     [Id] [uniqueidentifier] NOT NULL,
     [AppraisalId] [uniqueidentifier] NOT NULL,
     [QuestionId] [uniqueidentifier] NOT NULL,
     [AppraiserId] [uniqueidentifier] NOT NULL,
     [AppraisedId] [uniqueidentifier] NOT NULL,
     [InteractionGroupId] [uniqueidentifier] NOT NULL,
     [Point] [int]  ENCRYPTED WITH (
         COLUMN_ENCRYPTION_KEY = MyCEK,
         ENCRYPTION_TYPE = RANDOMIZED,
         ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'),
     [Comment] [nvarchar](1024) COLLATE Latin1_General_BIN2  ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK,
                ENCRYPTION_TYPE = DETERMINISTIC,
                ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'),
     [CreateDateTime] [datetime] NOT NULL
)

Then in order to install my certificate on any machine which will host my app I export SQL Server Always Encrypted certificate using windows certification manager to my application root folder and try to install it using this code:

X509Certificate2 cert = new X509Certificate2("MyExportedCertificate.pfx", "MyPassword", 
X509KeyStorageFlags.Exportable | X509KeyStorageFlags.PersistKeySet);
X509Store store = new X509Store(StoreName.My);

I run the app and everything works fine. the X509Certificate2 class installs my certificate and my app can encrypt/decrypt the data.

then I create a docker file using Visual Studio> Add Item> Docker Support and add this line of code to copy my "*.pfx" file to my app's root folder:

FROM mcr.microsoft.com/dotnet/aspnet:6.0-alpine3.16-amd64 AS base
WORKDIR /app
EXPOSE 80
EXPOSE 443

COPY ["Api/MyExportedCertificate.pfx", ""]
  • rest of the docker file contents are omitted for clarity.

then I edit my docker-compose file to add my sql server image:

services:
api:
 image: ali-api
 container_name: web_api_application
 ports:
   - "5000:80"
 environment:
   - ASPNETCORE_ENVIRONMENT=Production

sqldb:
 image: mcr.microsoft.com/mssql/server:2019-latest
 environment: 
     - SA_PASSWORD=Qwerty*2607548
     - ACCEPT_EULA=Y
 ports:
     - "1440:1433"
 container_name: sqldb

and finally I build my app:

docker build -t ali-api -f Api/Dockerfile .

docker-compose up

my app starts working and I can access my swagger page.

enter image description here

I can see that CMK and CEK are both generated too.

enter image description here

And also my always encryption is done as well:

enter image description here

But it seems that my license is not installed, because when I want to insert a data to my encrypted table columns I get the error:

An error occurred while saving the entity changes. See the inner exception for details. Failed to decrypt a column encryption key using key store provider: 'MSSQL_CERTIFICATE_STORE'. The last 10 bytes of the encrypted column encryption key are: '51-29-CD-17-1C-E2-6E-13-A4-45'. Operation is not supported on this platform.

what am I doing wrong?

how can I import this certificate in linux?(docker)

how can I install this certificate in linux?(docker)

.pfx file is located in my app's root folder. how can I install this .pfx file in linux?

enter image description here

Solution

  • Linux doesn't support MSSQL_CERTIFICATE_STORE. You have to use Azure Key Vault or implement your own custom key store provider. For more information you can see this link.