Forum Discussion
encrypted data
Hi all,
does anyone know if Desktop PowerBI can read and transform MS SQL encrypted data ?
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
4 Replies
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 ZhangHi, I recently opened a ticket to Microsoft for this reason. The only way to read SQL Always encrypted columns is via odbc:
(And it works 🙂 )
Please also notice that RLS can't be applied in this context. I suggested to add this feature in this Idea:
Elena
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.
