Access Azure SQL Always Encrypted Data in Power BI Reports or Azure Data Factory using Managed Identity and ODBC

Background

Published in

Microsoft Azure

8 min readMar 8, 2020

Always Encrypted is a feature available in Azure SQL Database which allows encrypting sensitive data inside client applications so that encryption keys are never revealed to the database. The main benefit of this feature is that business owners who own the data can maintain full control of data without revealing the actual decrypted data to teams who manage data like Cloud Operations, DBA, etc. This features is interesting especially for regulated industries. You can read in detail about this feature from the official Microsoft documentation page — https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?redirectedfrom=MSDN&view=sql-server-ver15

**Update Note October 2023: This article was published a while back, please do checkout the updated blog post Access Azure SQL Always Encrypted Data in Power BI Online by Matheus Martins Beuno **

Main Use Case addressed by the Solution

In reporting scenarios (using tools like Power BI) there are times when such Always Encrypted tables need to be decrypted for business use to see the data in plain text, Power BI native drivers don’t have the capability to decrypt Always Encrypted data at this time (March 2020) and the solution described here enables the requirement of seeing Always Encrypted Data in plain text in Power BI.

Other Relevant Use Cases enabled by the Solution

The solution shared here is not limited to just Power BI use case but applicable to many different scenarios like:

Azure Data Factory native drivers cannot read Always Encrypted Data so there might be needs to read the encrypted data from Azure SQL Database and save it decrypted format in Data Lake Sensitive Zone.
Tableau needs to be used for Reporting on Always Encrypted Azure SQL Database.
Need to access Azure SQL Database using Azure Active Directory Managed Identity of an Azure Virtual Machine over ODBC — This was a unique customer use case where there were users with assigned VMs in Azure but they were not part of Azure Active Directory, the goal was to not use SQL Authentication (requires maintaining username/password) but at the same time there were some reasons not to add those users to Azure Active Directory and this technique was useful although database was not Always Encrypted (in fact the database was Azure Synapse formerly Azure SQL Data Warehouse).

Solution Architecture

The solution requires use of ODBC for connecting and decrypted data from Azure SQL Database and following diagram shows overall architecture for the solution.

Solution Details

Basic knowledge of Azure Platform and access to Azure Subscription is assumed. Another pre-requisite is an Azure SQL Database which has a Table with at least few columns encrypted using Always Encrypted feature and the encryption keys saved in Azure Key Vault. The detailed instructions to setup such an Azure SQL Database with Key Vault are documented here — https://docs.microsoft.com/en-us/azure/sql-database/sql-database-always-encrypted-azure-key-vault?tabs=azure-powershell

Please note that Always Encrypted feature can make use of keys in Azure Key Vault or Windows Certificate Store but in this article post I will be using Azure Key Vault (it has not been validated but it is expected that similar principles would work with Windows Certificate Store as well).

Step by Step Instructions

Create Azure Windows VM (any of the Windows Server 2016 or later should work)—This VM is used for Power BI On-Premises Data Gateway, instructions to create the VM https://docs.microsoft.com/en-us/azure/virtual-machines/windows/quick-create-portal

2. Enable Managed Identity on the VM once the creation of the VM is complete as shown in the screenshot below. Managed Identity is a very useful feature available as part of the Azure Platform where the individual service like an Azure VM has an Azure AD Identity associated with it and code running within that VM can make use of the identity without any password. You can read more about Managed Identity here — https://docs.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/overview

Enable System Assigned Managed Identity for Azure Virtual Machine

3. Grant permissions to Managed Identity on Azure SQL Database because data from the database will be retrieved using this identity:

If Azure VM Name is MyDataGatewayVM an Azure AD application gets created automatically with the same name when Managed Identity is enabled.
Following are sample commands to grant Managed Identity permission on the SQL Database

CREATE USER <Azure_AD_principal_name> FROM EXTERNAL PROVIDER;
ALTER ROLE db_owner ADD MEMBER Mary;//Command for Azure SQL Data Warehouse to add user to a role is slightly differentEXEC sp_addrolemember ‘db_owner’, ‘Mary’;

Please see the documentation for more detail around granting accessing to Azure SQL and Synapse — https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-logins

4. Create Azure AD Application — This application will be used by ODBC drivers to retrieve decryption keys from Azure Key Vault. Use the instructions on the following links to create another Azure AD Application and Secret Key, note both the ClientID and Secret Key for later use.

Only execute the “Create an Azure Active Directory application” section — https://docs.microsoft.com/en-us/azure/active-directory/develop/howto-create-service-principal-portal#create-an-azure-active-directory-application
Only execute “Create a new application secret” sub-section- https://docs.microsoft.com/en-us/azure/active-directory/develop/howto-create-service-principal-portal#certificates-and-secrets

5. Grant Azure AD application permissions to read keys on Azure Key Vault using the instructions documented here — https://docs.microsoft.com/en-us/azure/key-vault/managed-identity

Make sure to grant “Key Permissions” Get, List, Decrypt, Unwrap and Verify

6. Setup On-Premises Power BI Data Gateway VM in Azure, this is software that will need to be installed on Azure VM which serves as an intermediary between SQL Database and Power BI Service — https://docs.microsoft.com/en-us/power-bi/service-gateway-onprem

Usually this is used to access data from on-premises data stores but in this solution this is required for Azure SQL Database to perform decryption of the Always Encrypted Data

If your use case is for Azure Data Factory, the solution here is exactly the same except Self-Hosted Integration Runtime software needs to be used instead of On-Premises Data Gateway — https://docs.microsoft.com/en-us/azure/data-factory/create-self-hosted-integration-runtime Self-Hosted Integration Runtime agent works in the same manner as On-Premises Power BI Data Gateway.

7. Download and Install ODBC Driver 17 or newer for SQL Server on the VM — https://www.microsoft.com/en-us/download/details.aspx?id=56567

8. Setup ODBC System DSN — Once ODBC Driver is installed you need to setup ODBC System DSN as shown in the screenshots below

Start the ODBC app by typing ODBC in the Search bar, select ODBC Data Sources (64-bit)
Select System DSN tab, click Add, select ODBC Driver 17 for SQL Server and click Finish

Go through the wizard with default options for most part except couple screens.

Make sure to put Azure SQL Server DNS Name for Server

Make use to change the default database to the Azure SQL Database Name and also check Column Encryption Checkbox

8. Registry Setting Update for ODBC System DSN — The key element of making this ODBC connection work with Power BI is that the connection needs to work in a non-interactive mode, for this we need to modify a few registry entries for ODBC connection on Power BI Data Gateway VM. Use RegEdit command to open Registry Editor and set the following Registry Entries (also shown in the screenshot below)

Authentication — Set the value to ActiveDirectoryMsi
KeystoreAuthentication — Set the value to KeyVaultClientSecret
KeystorePrincipalID — This is the ClientID for the Azure AD Application created in Step 2 above, this is used to retrieve the Decryption Key from Key Vault
KeystoreSecret — This is the Secret Key for the Azure AD Appication created in Step 2 above, this is used in conjuction with KeyStorePrincipalID to retreive Decryption Key from KeyVault

Note: Values for KeystorePrincipalID, KeyStoreSecret and Server are crossed out in the screenshot for confidentiality reasons

In use cases where the requirement is just to enable access to Azure SQL Database using ODBC with Managed Identity KeyStoreAuthentication, KeyStorePrincipalID and KeystoreSecret values can be left blank.

9. [Optional] Test ODBC Connection using Powershell — Now you can validate the ODBC connection is working properly by using the following Powershell snippet on a Powershell prompt (please don’t forget to replace the Table Name and the DSN Name in the last line). If everything is setup correctly you should see the data from database table displayed in plain text.

function Get-ODBC-Data{
param([string]$query=$(throw 'query is required.'),[string]$dsn)write-output("Dsn: " + $dsn)
$conn = New-Object System.Data.Odbc.OdbcConnection
$conn.ConnectionString = "DSN=$dsn;"
$conn.open()$cmd = New-object System.Data.Odbc.OdbcCommand($query,$conn)$ds = New-Object system.Data.DataSet
(New-Object system.Data.odbc.odbcDataAdapter($cmd)).fill($ds) | out-null
$conn.close()$ds.Tables[0]
}#replace the System DSN Name and the Table Name
Get-ODBC-Data -query "select * from XXXX" -dsn "XXXX"

10. Implement and Publish report to Power BI Service — At this point you can use Power BI Desktop on the VM and connect to Azure SQL Database using ODBC, in the Data Load Preview window you will see the un-encrypted data. Once Power BI Service is configured to use Data Management Gateway, you can publish the report to Power BI Service and report users will be able to see the desired data for their business needs. The Access Controls are available in Power BI Service to keep.

Note: One important thing to note here is that only Import Mode is supported for Power BI Reports and Direct Query Mode cannot be used.

Acknowledgement and Credit

Its important to acknowledge that in this ever changing world of technology I am writing this article in March 2020 and alternate solutions might become available in future, I will do my best to come back and update the article as things change. Lastly, I would like to attribute my colleague Sha Kanjoor Anandan who was very helpful and was the source of the solution for me.