How to set up AzureSQL Database with AlwaysEncrypted and fill it with data?

Question

at the moment I am working with the Azure Cloud. I want to set up an AzureSQL database and use AlwaysEncrypted to ensure that the data is 'always encrypted' ;-). Furthermore I would like to set up AzureFunctions which are able to connect to the Database as well as write records in.

I already set up an AzureSQL Database but I do not know how to work with it. I started two attempts:

1. Set up table directly in SSMS, fill data in table, create keys and encrypt it with the wizard. This works totally fine and I am able to see the plain data only if I set the 'AlwaysEncrypted' Checkbox while Connecting to the database.
2. My second attempt was to include the 'always encrypt directly in the queries. I tried the following:

CREATE COLUMN MASTER KEY CMK_test_1   
    WITH (  
        KEY_STORE_PROVIDER_NAME = 'AZURE_KEY_VAULT',  
        KEY_PATH = '<PATH_TO_AZURE_KEY_VAULT>'   
         )   

CREATE COLUMN ENCRYPTION KEY CEK_test_1   
WITH VALUES  
  (  
    COLUMN_MASTER_KEY = CMK_test_1,   
    ALGORITHM = 'RSA_OAEP',  
    ENCRYPTED_VALUE = <VALUE>
  )

Create Table dbo.AlwaysEncryptedTest
(
    ID int identity(1,1) PRIMARY KEY
    , FirstName varchar(25) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (  
        ENCRYPTION_TYPE = RANDOMIZED,  
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
        COLUMN_ENCRYPTION_KEY = CEK_test_1) not NULL    
    , LastName varchar(25) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (  
        ENCRYPTION_TYPE = RANDOMIZED,  
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
        COLUMN_ENCRYPTION_KEY = CEK_test_1) not NULL
    , City varchar(25) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (  
        ENCRYPTION_TYPE = RANDOMIZED,  
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
        COLUMN_ENCRYPTION_KEY = CEK_test_1) not NULL
    , StreetName varchar(25) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (  
        ENCRYPTION_TYPE = RANDOMIZED,  
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
        COLUMN_ENCRYPTION_KEY = CEK_test_1) not NULL
)

I know that I have to use an application to put records in the database but I could not find a tutorial or something else that helps me to do so. I found some C# explenation on the Microsoft website but this did not help me to do the job. In best case I would write the connection in python.

Any help is appreciated.

Best P

Answer 1

If you want to connect Azure SQL server which enables always encrypt with Azure key vault in python application, we can use ODBC driver to implement it.

Regarding how to implement it, we need to add ColumnEncryption=Enabled into connection string to tell odbc application always encrypt has been enabled. Besides, since we use Azure key vault store, we also need to add KeyStoreAuthentication KeyStorePrincipalId and KeyStoreSecret to make ODBC application connect Azure key vault, get encryption key. For more details, please refer to here and here

For example

1. Create a service principal to connect Azure key vault

az login
az ad sp create-for-rbac --skip-assignment --sdk-auth

az keyvault set-policy --name $vaultName --key-permissions get, list, sign, unwrapKey, verify, wrapKey --resource-group $resourceGroupName --spn <clientId-of-your-service-principal>

2. Code

server = '<>.database.windows.net'
database = ''
username = ''
password = ''   
driver= '{ODBC Driver 17 for SQL Server}'
KeyStoreAuthentication='KeyVaultClientSecret'
KeyStorePrincipalId='<clientId-of-your-service-principal>'
KeyStoreSecret='<clientSecret-of-your-service-principal>'
conn_str=f'DRIVER={driver};SERVER={server};PORT=1443;DATABASE={database};UID={username};PWD={password};ColumnEncryption=Enabled;KeyStoreAuthentication={KeyStoreAuthentication};KeyStorePrincipalId={KeyStorePrincipalId};KeyStoreSecret={KeyStoreSecret}'
with pyodbc.connect(conn_str) as conn:
    with conn.cursor() as cursor:
        cursor.execute("SELECT * FROM [dbo].[Patients]")
        row = cursor.fetchone()
        while row:
            print (row)
            row = cursor.fetchone()