Forum Discussion
SQL Server AlwaysEncrypted not working in Power BI
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:
Randomized encryption - error
Does anyone have a solution for this problem or an explanation (based on documentation) why this does not work?
Deterministic encryption - error