Using SQL Server Always Encrypted data in Power BI Report Builder (Paginated Reports)

Under the Microsoft platform, Analysis Services has provided a feature for directly decrypting data using SQL Server Always Encrypted. However, Power BI has lacked this feature. The only way was to consume the data via Azure Analysis Services or OnPrem Analysis Services.

Recently Microsoft released a new feature for decrypting the data using Power BI Report Builder (Paginated Reports). It allows to directly consume the encrypted data from the SQL database and renders the decrypted data.

For our example below the encrypted data is in an Azure SQL Database and the encryption method is Always Encrypted Keys stored as “Windows Certificate Store- Local Machine” which means the Client Certificate will be installed on the local machine. But, we can also use other options available as below.

Using SQL Server Always Encrypted data in Power BI Report Builder (Paginated Reports)

Note: It is necessary to have the client certificate installed on the machine where you are decrypting the data. In my case, I am using the same machine for encryption and decryption (i.e., Power BI Report Builder is installed on the same machine where I have encrypted the data).

Here is the Customer source data from Microsoft Azure SQL Database which will be consumed and decrypted in my paginated report.

Highlighted the encrypted column (LinkedAccount)

Steps to decrypt

1. Create a new data source using Azure SQL Database as the type and customize the data source name as needed.

2. Create a connection string using the Build option in the Data Source Properties window.

Enter the server name and credentials for the SQL Database. Then, select the database name from the list of databases available in the drop-down of Connect to Database.

3. It’s time to decrypt your data. Click on the Advanced option in the Connection Properties window and change the setting Column Encryption Setting = Enabled. Click OK to exit.