Blogs / SAP / SAP Data Warehouse Cloud / Using SQL Server Always Encrypted data in Power BI Report Builder (Paginated Reports)

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

Dec 10, 2019

SHARE

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)

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

Steps to decrypt

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

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

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.

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

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.

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

4. Create a dataset pointing the newly created data source. Pull the required fields along with the encrypted field.

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

5. Create a report and execute it to see the decrypted data. Publish it to the Power BI service with the Premium capacity workspace.

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

Know more about Microsoft Power BI services offerings from Visual BI solutions here.


Corporate HQ:
5920 Windhaven Pkwy, Plano, TX 75093

+1 888-227-2794

+1 972-232-2233

+1 888-227-7192

solutions@visualbi.com


Copyright © Visual BI Solutions Inc.

Subscribe To Our Newsletter

Subscribe To Our Newsletter

Join our mailing list to receive the latest news and updates from our team.

You have Successfully Subscribed!

Share This!

Share this with your friends and colleagues!