Service principal to DECRYPT 'Always Encrypted Azure SQL DB' using certificate instead of client secret

Question

I have a Azure SQL DB with always encrypted feature enabled using Azure Key Vault Key.

I want to consume this from a Power BI report. So I'm using a Data Gateway in the middle, with ODBC connection and a Service principal (Client/Secret) who is able to access the Key (from Azure Key Vault). Everything is working fine but for security constraints I need to change this Service principal Authentication to Certificate.

Imagen1

Imagen2

I want to know if it is possible to change this authentication (that allows ODBC connection to get the Azure Key vault and Decrypt data) to Azure service principal with a certificate (and not a client secret).

Answer 1

Unfortunately, For now there’s no option in ODBC Driver Administrator to add app certificate for column encryption.

I tried to add the SQL Server connection like below:-

There’s no option to add the certificate or certificate based app authentication in the key store authentication:-

In SSMS also there’s no option to add client certificate in the column encryption, Refer below:-

While connecting to Azure SQL Data source with Power BI, there’s no direct authentication available for app certificate, refer below:-

But you can connect your Azure service principal to Power BI by allowing Power BI to access service principal:-

Add your Service principal with certificate in one Azure AD group and then allow that group here:-

Created an Azure AD group and added my Service Principal:-

Power BI:-

Visit app.powerbi.com > Log ion to your Power BI workspace > Settings on the right > Admin Portal > Tenant Settings > Developer Settings > allow Service principal to use Power BI API’s > ENABLE and add ypur security group that contains your service principal

Share your dataset directly with the Service Principal or by giving the permissions to the Service Principal:-

Also, SQL server authentication with certificate is only available for Azure ARC based servers.

References:-

Set up Azure Active Directory authentication for SQL Server - SQL Server | Microsoft Learn

Tutorial: Getting started with Always Encrypted - SQL Server | Microsoft Learn

Tutorial: Getting started with Always Encrypted - SQL Server | Microsoft Learn

Embed Power BI content in an embedded analytics application with service principal and an application secret - Power BI | Microsoft Learn