Use Always Encrypted data with SSAS and Power BI

I got an interesting question today about using Always Encrypted columns with SSAS and Power BI and it turns out it this just works. Let’s take a look at how we can set this up.

The way that Always Encrypted works is that a SQL client driver (provider) transparently decrypts the data after reading it from the column. The data, stored in the encrypted column, never appears in plaintext inside SQL Server or over the wire. To be able to decrypt and read the data, the client connecting to SQL Server needs to have access to a column master key, such a certificate installed on the client machine, which is then used to decrypt a column encryption key, which subsequently allows the provider to decrypt the data locally. For more please read this article: https://aka.ms/AlwaysEncrypted.

To start I need to create some encrypted data. I created a database and create a single table where I encrypted two columns using the Always Encrypted wizard:

That takes care of generating the keys, including the certificate I will be using as the column master key, and encrypting the data. Of course this is not an in-depth post on how you should set up Always Encrypted – I would recommend reading the best practices and considerations at BOL. One side note here is that you can only use certificates, the Azure key vault option doesn’t work with SSAS.

Next I created a SSAS model in SSDT in Direct Query mode (as described here) pointing to this table in SQL Server using the general steps. When I now connect Power BI desktop to my model I get what I expected – encrypted data:

Now here comes the trick. In this case Analysis Services is our SQL client, it receives DAX queries from the client tools and then translates these into SQL queries and then sends them to SQL on the BI clients (Excel / Power BI desktop / Power BI service) behalf. This means we can use SSAS to do the decryption, to configure this I need to do 3 things:

1. This features uses legacy data source settings, you can enable these in the options menu of Visual studio when you are using compatibility level 1400 or up. This is not yet possible with M enabled data sources.
2. Install the client certificate I am using as the column master key on my AS Server. In my case, the AS Server is running on the machine I generated the certificate on by running the Always Encrypted wizard, so I don’t have to do anything. If your AS Server is running on a different machine than the computer you generated the certificate on, you will need to install the certificate on the AS Server machine and make sure the AS Server has a permission to access the certificate. More on this here.
3. The second thing is that I need to switch the provider in SSAS from the default, the SQL Native client, to the .Net Framework provider. Note: the  .NET Framework 4.6.1 or later, needs to be present on the AS Server machine.
   
4. The last thing I need to do, is to change the provider setting “Column Encryption Setting” to “Enabled”
   

That’s it. I now just deploy the changes and refresh my Power BI desktop file:

Now instead of encrypted data I see the plaintext values.  Now just to show you, this also works when I publish my report to Power BI and use the gateway:

The reason for this, and I want to make sure this is clear, is that the data is decrypted at the AS Server and then transported to the clients connecting to AS in plain text. In this case the data is still in encrypted text between AS and SQL but in plain text between AS and the Gateway and then on to Power BI, of course the data still goes through encrypted network communication (thanks Greg for pointing this out).

Just a note, this works for both Direct Query and import mode but importing the data kind of defeats the purpose as the data is now stored in SSAS unencrypted. It also works for Multi dimensional models where you can follow the same steps as above to set the provider settings to support AE.

Now if you want to use other SQL Server security features like data masking or RLS you need to make sure you pass on the username of the user connecting to SSAS on to SQL, then the correct security features will be in effect. This is possible as I described here: https://www.kasperonbi.com/use-sql-server-rls-with-ssas-and-power-bi/