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:
- 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.
- 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.
- 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.
- 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/
7 Replies to “Use Always Encrypted data with SSAS and Power BI”
Good day, if you change that setting (Column Encryption Setting=Enabled), would that mean ALL users can now view the encrypted column data?
This is an excellent article and has helped me enormously. Thank you.
I just wanted to add a bit of detail on a couple of bits that I struggled with should anyone else want to step through this:
1) To enable legacy data source settings for SSAS. In Visual Studio go to Tools > Options > Analysis Services Tabular, and select ‘Enable legacy data sources’.
2) Once legacy data sources are enabled you can right click Data Sources in your tabular project and select ‘Import from data source (legacy)’. This is where you’ll be able to edit your provider settings.
3) If you have any existing tables in your model you’ll need to remove them before adding tables from the new data source as Direct Query mode will only allow you to have tables from one data source.
I also found that I could only get this to work where the Encryption Type is set to Deterministic, and when using the ‘Connect live’ option rather than the ‘Import’ option in Power BI.
from what i see this works only if you have SSAS on your own VM. I am trying to make this to work with Azure Analysis Services. I assume there is no support for this until now.
Did any of you already try using Always Encrypted table with SSAS Tabular (i would like to do it directly in PowerBI but from what I saw SSAS as an intermediate service is required)
Octavian, did you try using AAS with a Data Gateway and installing the cert on the DG VM?
Hi. Great article and would solve my HR scenario .
I’m almost there, but I struggle with this :
‘Failed to save modifications to the server. Error returned: ‘The DataType property of column ‘Sats’ in table ‘Aditro_payroll’ cannot be changed from Binary to non-Binary, or vice versa.’.
How do I get the definition of the Binary-fields into the Tabular. Yes, I have DirectQuery enabled.