Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

SQL Server AlwaysEncrypted not working in Power BI

We've implemented Always Encrypted in our SQL Server 2016 database. Using Always Encrypted we encrypt a column X.

Using a .NET DirectQuery connection we decrypt the data in SSAS (a tabular model). The decryption works. Next step, we use a live connection from PowerBI to the Tabular model. The data is shown decrypted in PowerBI, so that works.  However: we cannot filter on a column with encryption enabled. If we filter (in a filter of a visual) column X in PowerBI we receive an error. This error occurs in both ways of database encryption (Deterministic and Randomized).

 

If we change the SSAS tabular .NET connnection from DirectQuery to Import the filtering works in PowerBI. This is not our ideal solution because we only want to store the data in the database, and not in the SSAS tabular model and/or in PowerBI.

 

This is the model:

encryption_model.png

 

These are the errors that occur:

 Deterministic encryption - errorDeterministic encryption - errorRandomized encryption - errorRandomized encryption - error

 

Does anyone have a solution for this problem or an explanation why this does not work?

 

 

4 REPLIES 4
Anonymous
Not applicable

We've implemented Always Encrypted in our SQL Server 2016 database. Using Always Encrypted we encrypt column X.

Using a .NET DirectQuery connection we decrypt the data in SSAS (a tabular model). The decryption works. Next step, we use a live connection from PowerBI to the SSAS tabular model. The data is shown decrypted in PowerBI, so that works.  However: we cannot filter on a column with encryption enabled. If we filter (in a filter of a visual) column X in PowerBI Desktop we receive an error. This error occurs in both ways of database encryption (Deterministic and Randomized).

 

If we change the SSAS tabular .NET connnection from [DirectQuery] to [Import] the filtering works in PowerBI. However, this is not our ideal solution because we only want to store the data - encrypted - in the database, and not - decrypted - in the SSAS tabular model and/or in PowerBI.

 

This is the model:

encryption_model.png

 

These are the errors that occur: 

Randomized encryption - errorRandomized encryption - error 

Does anyone have a solution for this problem or an explanation (based on documentation) why  this does not work?

 

 Deterministic encryption - errorDeterministic encryption - error

v-jiascu-msft
Employee
Employee

Hi @Anonymous,

 

Please refer to use-always-encrypted-data-with-ssas-and-power-bi/sql-server-2016-always-encription-features and with-always-encrypted.

 

Best Regards,

Dale

 

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

Hi Dale, 

 

Thanks for your comment. I've added the links as background info. Unfortunately, those articles do not provide the solution. 

Anonymous
Not applicable

Hi, 

 

unfortunately links seems to be unrelated as usual, have you guys find a solution yet? 

 

We are aiming for a PaaS solution but I can not seem to be finding a solution. I have been thinking about Power Query M data connector with ODBC driver but it will take time and I am trying to avoid it. Please let us know if you find out anything useful. 

 

Thanks!

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors