cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Zindros New Member
New Member

encrypted data

Hi all,

 

does anyone know if Desktop PowerBI can read and transform MS SQL encrypted data ?

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: 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.  
Capture2222.PNG

Capture1111.PNG


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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Moderator v-yuezhe-msft
Moderator

Re: 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.  
Capture2222.PNG

Capture1111.PNG


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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

krati Visitor
Visitor

Re: encrypted data

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

cdotzenrod Regular Visitor
Regular Visitor

Re: encrypted data

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.  

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)