SQL Server AlwaysEncrypted not working in Power BI

[Anonymous]

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:

 Deterministic encryption - errorRandomized encryption - error

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

[Anonymous]

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

[v-jiascu-msft]

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

[Anonymous]

Hi Dale, 

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

[Anonymous]

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!