Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
These are the errors that occur:
Does anyone have a solution for this problem or an explanation why this does not work?
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:
These are the errors that occur:
Does anyone have a solution for this problem or an explanation (based on documentation) why this does not work?
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
Hi Dale,
Thanks for your comment. I've added the links as background info. Unfortunately, those articles do not provide the solution.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.