European Microsoft Fabric Community Conference

The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!

Register Now

Hi @Zindros ,

As per my test, when I encrypt a column of data for a SQL Server table and import the table to Power BI Desktop, Power BI Desktop cannot read and transform the data of encrypted column.





As illustrated in the above figures, the Binary type of Data Model column is currently not supported in Power BI Desktop. Binary Data columns are removed from the resulting table in Power BI Desktop.

Thanks,
Lydia Zhang

I've been told this is not possible currently but I don't like being told no.  I was very close to getting this to work when I used a generic ODBC connection from Power BI to my encrypted columns where the said columns are encrypted with a key stored in Azure Key Vault.  The driver worked and connected to my database but crapped out when tried to load the table that had encrypted columns.  This was my error:

DataSource.Error: ODBC: ERROR [CE275] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Error requesting access token, HTTP status 400, expected 200
ERROR [CE275] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Error requesting access token, HTTP status 41360, expected 200
ERROR [CE258] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Error retrieving key information for https://cjdsqlvault.vault.azure.net:443/keys/CMKAuto4/d881 ....

To me it seems like if I can get to the bottom of the key vault error this might actually work and Power BI will be able to load and decrypt data from encrypted columns.

I am facing same issue and Power BI desktop fails to decrypt the binary data  and says "Expression Error - Value was not specified". Below is the query in Advanced editor: TableName{[Column1=Binary.FromText(...)]}[Column2]

Please suggest what are the steps to follow for displaying encrypted columns data in Power BI desktop report. Also, what are the steps required to make those columns visible once published to app.powerbi.com

Hi @Zindros ,

As per my test, when I encrypt a column of data for a SQL Server table and import the table to Power BI Desktop, Power BI Desktop cannot read and transform the data of encrypted column.





As illustrated in the above figures, the Binary type of Data Model column is currently not supported in Power BI Desktop. Binary Data columns are removed from the resulting table in Power BI Desktop.

Thanks,
Lydia Zhang

Hi, I recently opened a ticket to Microsoft for this reason. The only way to read SQL Always encrypted columns is via odbc:

https://medium.com/microsoftazure/access-azure-sql-always-encrypted-data-in-power-bi-reports-using-m...

(And it works 🙂 )

Please also notice that RLS can't be applied in this context. I suggested to add this feature in this Idea:

https://community.powerbi.com/t5/Custom-Visuals-Ideas/RLS-when-loading-SQL-Always-Enrypted-columns/i...

Elena

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.