SQL Always Encrypted in Azure

Question

I need to build a web app that accesses some encrypted columns on a DB. All must be hosted in the client's azure account. I have searched for a couple of days and read a lot of tutorials but I can't find an answer to my problem.

I have mainly followed these:

https://learn.microsoft.com/en-us/azure/sql-database/sql-database-always-encrypted http://www.bradleyschacht.com/always-encrypted-with-azure-key-vault/

I was able to run a web app on my machine with the certificate generated by SSMS encryption wizard and a SQL DB hosted on azure. I couldn't do it with an azure vault key.

Now I need to publish my web app on azure but I'm unable to access/modify the DB data. I need to either use the certificate from my machine or use the azure vault. Can anyone explain to me how it's done?

• I tried to export the certificate to the azure vault, but I don't know how to "reference" it

• I tried to create a new table on the db and encrypting it with a vault key, but I get:

Failed to decrypt a column encryption key. Invalid key store provider name: 'AZURE_KEY_VAULT'. A key store provider name must denote either a system key store provider or a registered custom key store provider. Valid system key store provider names are: 'MSSQL_CERTIFICATE_STORE', 'MSSQL_CNG_STORE', 'MSSQL_CSP_PROVIDER'. Valid (currently registered) custom key store provider names are: . Please verify key store provider information in column master key definitions in the database, and verify all custom key store providers used in your application are registered properly."

• I read somewhere that I need to give permission in the AD to my application, but I don't have permissions from my client (the owner of the Azure subscription) to do that.
• I read also that a stored procedure must be used to read and write to the DB. Is this true?

Thanks in advance for any help.

Answer 1

I need to either use the certificate from my machine or use the azure vault. Can anyone explain to me how it's done?

It depends on your use-case. Actually Selecting Keystore Provider for your Column Master key is depends on which driver and version you are using. There are two high-level categories of key stores : Read here

• Local
• Centralized Key Store

Local

If you planning to deploy your App in On-Prem/VM, then you can generate our own Certificate and keep the certificate within your Local VM.

Centralized Key Store

If you planning to deploy your App in azure web APP/Cloud then you should keep your Key Store in a centralized Secure Vault which may be here as Azure Key Vault

As a best practice, you should not store the provider in the Local machine, Which would be a problem if you VM is compromised then your DB certificate also be compromised.

I tried to export the certificate to the azure vault, but I don't know how to "reference" it

CREATE COLUMN MASTER KEY [TESTMASTERKEY]
WITH
(
    KEY_STORE_PROVIDER_NAME = N'AZURE_KEY_VAULT',
    KEY_PATH = N'' --Paste your Key Identifier 
)
GO

I tried to create a new table on the DB and encrypting it with a vault key, but I get:

• Always try to download the latest SSMS version.
  
• Assume you are using Azure SQLDB. Always encryption will work only on SQL Server 2016 and above in on-prem and all versions of Azure SQLDB
  
• Set the connection string to Column Encryption Setting=enabled

The behavior you describe is a bug in CTP 3.0 and SSMS October update. The issue, as you surmised, is that the Azure Key Vault provider is not registered if you open the Query Editor window opening the Always Encrypted wizard first. We’ve already fixed this for the next update of SSMS! In the meantime, the workaround is to open the Always Encrypted wizard (you can close it/cancel immediately after opening) which will cause the Azure Key Vault provider to get registered. This bug manifests itself only through this specific case (using the Query Editor before the wizard), and won’t at all impact your ability to use the Always Encrypted wizard or use the Azure Key Vault provider with any of your client applications.

So try to download the latest SSMS version.

I read somewhere that I need to give permission in the AD to my application, but I don't have permissions from my client (the owner of the Azure subscription) to do that.

This is mainly for the Client side. You need to register your app in order to get the client id and client secret for your client-side application to talk with encrypted data in DB. Read here for how to register your client app. Unless you register your app, you couldn't able to connect from any client-side(Except SSMS). You need to contact the subscription owner to register the app.

I read also that a stored procedure must be used to read and write to the DB. Is this true?

Depends on your Encryption Type. There are two types of Encryption Read here about it

• Deterministic
• Randomized

Each having its own pro and cons.

Deterministic encryption always generates the same encrypted value for any given plaintext value. Using deterministic encryption allows point lookups, equality joins, grouping and indexing on encrypted columns. However, but may also allow unauthorized users to guess information about encrypted values by examining patterns in the encrypted column, especially if there is a small set of possible encrypted values, such as True/False, or North/South/East/West region. Deterministic encryption must use a column collation with a binary2 sort order for character columns.

Randomized encryption uses a method that encrypts data in a less predictable manner. Randomized encryption is more secure, but prevents searching, grouping, indexing, and joining on encrypted columns.