Forum Discussion

Zindros's avatar
Zindros
New Member
10 years ago
Solved

encrypted data

Hi all,   does anyone know if Desktop PowerBI can read and transform MS SQL encrypted data ?
  • Anonymous's avatar
    Anonymous
    10 years ago

    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

cdotzenrod's avatar
cdotzenrod
Advocate I
7 years ago

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.