Access Azure SQL Always Encrypted Data in Power BI Online

This article is a follow-up to this awesome article from Mr. Inderjit Rana. Many thanks to him!

TL;DR;

It is necessary to configure a gateway using ODBC 18 or above.

Case Scenario

Our application receives data from an external client and stores it in Azure SQL. Some of the values are confidential, and our approach to it was to encrypt those columns with Always Encrypted.

To read those values with Power BI, I’ve used the method described in Mr. Rana’s article, and indeed the method works!

But when we tried to publish my .pbix to our online workspace…

So, what was I missing for it to work online?

The issue

To connect to the database, I was using the default “Connect to SQL Server” connector. As it does connect to the server and database, it does not provide an option for cryptographic operations (namely, columnencryption=Enabled).

Trying to add it manually to the query only returns an error:

The keystore provider AZURE_KEY_VAULT failed to decrypt the ECEK with RSA_OAEP

As mentioned before, it does work in a local environment, once you change the registers for the ODBC connector. This option (as I’m aware) is not possible for Power BI Online.

The solution: Part 1 — Infrastructure

The solution came down to two factors:

1- Using ODBC 18: as it turned out, shortly after Mr. Rana’s article, ODBC released a new version with an explicit way to add, thus not requiring to add values directly to the Regedit.

It will asks for both the Authentication method and PrincipalID for the KeyVault.

With this in mind, we implemented the second strategy

2- Using Data Gateways: As we already have a local machine connected to Azure SQL, we could install ODBC 18 on it, and use it to connect to the Azure SQL Server.

The Solution: Part 2 — Direct Query

Once the connection was confirmed, running direct queries from PowerShell, using this data gateway returned all fields decripted. But we still needed to connect the Report to it.

This now can be made through the connection query. In a new blank source:

=Odbc.DataSource(“Authentication=ActiveDirectoryPassword;columnencryption=Enabled;Database={your database};DRIVER={ODBC Driver 18 for SQL Server};keystoreauthentication=KeyVaultClientSecret;keystoreprincipalid={KeyStoreID};keystoresecret={KeyStoreSecret};Server={AzureSQL Server}”, [HierarchicalNavigation=true])

Describing the parameters:

Authentication — defines how a user will authenticate. In our case, using a Service Account registered on Azure AD.

ColumnEncryption — defines if the query will try to decrypt encrypted columns.

DRIVER — Should be ODBC 18 for it to work online.

KeystoreAuthentication — defines how Power BI will try to authenticate to Azure KeyVault.

KeyStorePrincipalid — Resource ID from the App that has permission to retrieve the secret key. It must be registered on Azure App Registration.

KeyStoreSecret — Resource Secret Value. You can only see it in its creation.

As the authentication method was set to “ActiveDirectoryPassword”, when hitting enter, a prompt will appear asking for those credentials. The user must have access to the Server and Database.

Conclusion

Through this method it was possible to work with Direct Queries, making the reports online, with no need to run schedule refreshes.

There are two downsides to this approach, namely, relying on an external machine to work as a gateway (or as Mr. Rana suggested, using a VM), and exposing the KeyStoreSecret in the query parameters.

It was a very challenging issue to resolve, so I would like to once again say thanks to Mr. Rana for his article, and may this follow-up help someone else with the same problem.

Funny thing

Not even ChatGPT was aware of ODBC 18 “Keystore Configuration”, still recommending using ODBC 17, without providing a way to fill the KeyStore Provider or Principal ID.