As I promised in my earlier post, in this article I show you how to leverage your Power BI Desktop model using Query Parameters on top of SQL Server 2016 Dynamic Data Masking (DDM). I also explain very briefly how to enable DDM on DimCustomer table from AdventureWorksDW2016CTP3 database. We will then create a Power BI Desktop model with Query Parameters on top of DimCustomer table. You will also learn how to create a Power BI Template so that you can use it in the future for deployment.

Note: If you want to learn about using a List output in Power BI Desktop Query Parameters have a look at the next post of these series “Power BI Desktop Query Parameters, Part 3, List Output“.

Use Cases

In the previous post I explained how to create dynamic data sources using Query Parameters. You also learnt how to use Query Parameters in Filter Rows. In this post you learn :

1. Using Query Parameters on top of SQL Server Dynamic Data Masking (DDM)
2. Query Parameters in Power BI Template

Requirements

Just like the Part1 of Power BI Query Parameters, you require to meet the following requirements to be able to follow this post:

1. The latest version of Power BI Desktop (Version: 2.34.4372.322 64-bit (April 2016) or later)
2. SQL Server 2016 (You can download SQL Server 2016 Developer Edition for free)
3. AdventureWorksDW

Definitions

I’m not going to provide much details about DDM as you can find lots of information here. But, to make you a bit familiar with Dynamic Data Masking I explain it very briefly.

Dynamic Data Masking (DDM)

Dynamic Data Masking (DDM) is a new feature available in SQL Server 2016 and also Azure SQL Database. DDM is basically a way to prevent sensitive data to be exposed to non-privileged users. It is a data protection feature which hides sensitive data in the result set of a query. You can easily enable DDM on an existing table or enable it on a new table you’re creating. Suppose you have two groups of users in your retail database. Sales Persons and Sales Managers. You have a table of customers which in this post it is DimCustomer from AdventureWorksDW2016CTP3. This table contains sensitive data like customers’ email addresses, phone numbers and their residential adders. Based on your company policy, the members of Sales Persons group should NOT be able to see sensitive data, but, they should be able to all other data. On the other hand the members of Sales Managers group can see all customers’ data. To prevent Sales Persons to see sensitive data you can enable Dynamic Data Masking on the sensitive columns on DimCustomer table. In that case when a sales person queries the table he/she will see masked data. For instance he see uXXX@XXX.com rather than user@domain.com.

Create a table with DDM on some columns

It’s easy, just put “MASKED WITH (FUNCTION = ‘Mask_Function’)” in column definition. So it should look like this:

CREATE TABLE Table_Name   (ID int IDENTITY PRIMARY KEY,    Masked_Column1 varchar(100) MASKED WITH (FUNCTION = ‘Mask_Function’),    Masked_Column2 varchar(100) MASKED WITH (FUNCTION = ‘Mask_Function’),

 

…

)

GO

Alter an existing table and enable DDM on desired columns

As you guessed you have to use “ALTER TABLE” then “ALTER COLUMN”. Your T-SQL should look like:

ALTER TABLE Table_Name ALTER COLUMN Column_Name1 ADD MASKED WITH (FUNCTION = ‘Mask_Function’);

GO

ALTER TABLE Table_Name

ALTER COLUMN Column_Name2 ADD MASKED WITH (FUNCTION = ‘Mask_Function’);

GO

For more information please refer to MSDN.

Power BI Template

A template is basically a Power BI file that represents an instance of a predefined Power BI Desktop which includes all definitions of the Data Model, Reports, Queries and parameters, but, not includes any data. Creating Power BI Templates is a great way to ease the deployment of existing models. Creating templates is very easy, you just click File –> Export –> Power BI Template. We will look at this more in details through this article.

Scenario

You are asked to implement a new level of security on customers’ data (DimCustomer on AdventureWorksDW2016CTP3 database) so that just privileged users can see the customers’ email, phone numbers and residential address. Privileged users are all members of “SalesManager” database role. You are also asked to prevent “SalesPerson” database role to see sensitive data. But, all members of both “SalesManager” and “SalesPerson” database roles can query DimCustomer table. The users should NOT have SQL Server logins.

• In DimCustomer, “EmailAddress”, “Phone” and “AddressLine1” should be masked
• SalesManager database role is privileged to see unmasked data
• SalesPerson database role is privileged to see masked data only
• SQL Server database user “user1_nologin” is a member of “SalesManager”
• SQL Server database user “user2_nologin” is a member of “SalesPerson”

On top of that, you have to implement a report in Power BI Desktop  for both sales managers and sales persons. The report queries DimCustomer. You require to create a Power BI Template so that it covers the security needs.

To be able to implement the above scenario you have to follow the steps below:

• Create “SalesManager” and “SalesPerson” database roles if they don’t exist
• Create two new users without logins (user1_nologin and user2_nologin)
• Add user1_nologin as a member of SalesManager database role
• Add user2_nologin as a member of SalesPerson database role
• Grant select access to both database roles
• Mask “EmailAddress”, “Phone” and “AddressLine1” columns in DimCustomer
• Grant SalesManager database role to see unmasked data
• Create Power BI Desktop Report
• Export the model to Power BI Template

Implementation

Let’s develop the above scenario in SQL Server and then Power BI Desktop.

SQL Server Implementation

I’ll do the whole SQL Server development part using T-SQL. But, you can do lots of the job using SQL Server Management Studio UI. I leave that part to you if you want to do the job using the UI.

• Open SQL Server Management Studio (SSMS)
• Connect to your SQL Server 2016 instance
• Open a new query for AdventureworksDW2016CTP3
• Copy and paste below code snipped to query editor then run it

USE [AdventureworksDW2016CTP3]

GO

 

— Create database roles if not exist

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N’SalesManager’ AND type = ‘R’)

CREATE ROLE [SalesManager]

GO

 

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N’SalesPerson’ AND type = ‘R’)

CREATE ROLE [SalesPerson]

GO

 

— Grant select access to both database roles

GRANT SELECT ON DimCustomer TO [SalesManager]

GO

 

GRANT SELECT ON DimCustomer TO [SalesPerson]

GO

 

— Create users if not exist

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N’user1_nologin’)

CREATE USER [user1_nologin] WITHOUT LOGIN

GO

 

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N’user2_nologin’)

CREATE USER [user2_nologin] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]

GO

 

— Add user1_nologin to SalesManager

ALTER ROLE [SalesManager] ADD MEMBER [user1_nologin]

GO

 

— Add user2_nologin to SalesPerson

ALTER ROLE [SalesPerson] ADD MEMBER [user2_nologin]

GO

 

— Mask sensitive columns

ALTER TABLE DimCustomer

ALTER COLUMN EmailAddress ADD MASKED WITH (FUNCTION = ’email()’)

GO

 

ALTER TABLE DimCustomer

ALTER COLUMN Phone ADD MASKED WITH (FUNCTION = ‘partial(6,”XXXXXXX”,0)’);

Go

 

ALTER TABLE DimCustomer

ALTER COLUMN AddressLine1 ADD MASKED WITH (FUNCTION = ‘default()’);

Go

 

— Grant SalesManager to see unmasked data

GRANT UNMASK TO SalesManager

GO

Power BI Desktop Implementation

• Open Power BI Desktop
• Get data from SQL Server Database
• Type server name and database name
• Click “Advanced options”
• Copy and paste the code snipped below in “SQL statement” box then click OK

EXECUTE AS USER = ‘user2_nologin’

SELECT * FROM DimCustomer

REVERT

• If you are using Windows credentials then click “Connect” otherwise click “Database” and enter credentials then “Connect”
• Click “Load”
• You’ll get an the following error message, don’t worry, just close the error message

“DataSource.Error: Microsoft SQL: Cannot continue the execution because the session is in the kill state.
A severe error occurred on the current command.  The results, if any, should be discarded.”

• Click “Apply Changes”

• Now you should see “Query1” in the model

• Click “Edit Queries” from the ribbon
• If you scroll right you’ll see masked data for “EmailAddress”, “Phone” and “AddressLine1”

• Rename the query to DimCustomer

You now need to create a parameter for the users. This parameter will be referenced in the data source later

• Click “Manage Parameters” from the ribbon
• Click “New”
• Enter a name and description
• Select “Text” as Type and “List of values” as Allowed Values
• Type “user1_nologin” and “user2_nologin” in the values list
• Select “user2_nologin” in both default and current value then click OK

You need to reference the DBUser parameter in DimCustomer data source.

• Click DimCustomer from Queries pane
• Click “Advanced Editor”
• Replace “user2_nologin” with “”&DBUser&””

Note: Please note where you put the quotation marks.

• Click “Close & Apply” from the ribbon

It seems we are done. Now it’s time to switch the users to see what happens. To make it easier lets put a Table on the report page containing “FirstName”, “LastName”, “EmailAddress”, “Phone” and “AddressLine1” columns.

• Click “Edit Parameters” from the ribbon
• Select “user1_nologin” from the list then click OK

• Confirm running Native Database Query

Oops! You got that nasty error message again. Of course, you can close the message and click “Apply Changes”, but, it doesn’t look realistic to get that error message whenever we switch the user.

What is really wrong with the query we wrote?

The answer is that there is nothing wrong with the query indeed. The reason of getting the error message is the first line of the query. We are executing the query as a user, but, we already used another credential to connect to the database which in this sample is a Windows user. This is called “Context Switching”. Basically Power BI Desktop wants reset the status of the current connection and reuse it for a different user. Resetting the current session causes the problem.

By the way, let’s close the error message and click “Apply Changes” to make sure that we can see unmasked data after switching the user.

As you see the process works fine, but, we need a remedy for this to get rid of that nasty error message.

The solution is to encapsulate the queries in stored procedures in SQL Server side. In that case Power BI Desktop will not reset the connection. After creating stored procedures for each user we need to create a new parameter in Power BI Desktop to pass the stored procedure names to the data source rather than the users.

Note: You can create just one stored procedure. In that case, you need to define a parameter for SQL Database user then construct the stored procedure writing dynamic SQL. But, to keep this as simple as possible I created two separated stored procedures for each user.

• Go back to SSMS and run the following SQL scripts to create two new stored procedures

CREATE PROCEDURE [dbo].[DimCustomerMasked]

AS

EXECUTE AS USER = ‘user2_nologin’

SELECT * FROM DimCustomer

REVERT

GO

CREATE PROCEDURE [dbo].[DimCustomerUnMasked]

AS

EXECUTE AS USER = ‘user1_nologin’

SELECT * FROM DimCustomer

REVERT

GO

• In Power BI Desktop click “Edit Queries”
• In Query Editor click “Manage Parameters” from the ribbon
• Replace the existing values with the stored procedure names
• Select “DimCustomerMasked” for both default and current values then click OK

• Select DimCustomer from Queries pane then click “Advanced Editor” from the ribbon
• Replace the whole query with the following

“EXEC “&DBUser”

Note: Note the quotation marks.

• Click “Edit Permission” then click Run

• Click “Close & Apply” from the ribbon

• It looks much better now
• Click “Edit Parameters” from the ribbon and switch the stored procedure to “DimCustomerUnmasked”

• Click Run

Hmm, that looks nice.

Power BI Template

As stated before, creating a Power BI Template is so easy. Just save the current model then File –> Export –> Power BI Template.

Write some description and click OK.

Save the template.

Close Power BI Desktop. Now double click on the template file to open it. The first thing that happens after opening the template file is that it askes to enter parameters. As you might noticed the Power BI Desktop loads a new Untitled model.

If you switch the parameter value you’ll see you’ll no longer asked to confirm running Native Database Query.

Last Word

You can load the parameters’ data into the model which is really great. I’m passing this to you for further investigations.

Sample template is Ready to Download

You can download the sample template I created on top of AdventureWorksDW2016 and Dynamic Data Masking here. It contains the previous post’s samples as well as what you’ve learned in the current article.

• SQL stored procedure scripts to support masked data
• SQL stored procedure scripts to support unmasked data
• Power BI Template