Data Masking in Power BI + Azure Synapse

5 min readAug 6, 2021

Data Masking helps prevent unauthorized access to sensitive data by enabling customers to designate how much of the sensitive data to reveal with minimal impact on the application layer.

Power BI is a collection of software services, apps, and connectors that work together to turn your unrelated sources of data into coherent, visually immersive, and interactive insights. You can learn more on it @ https://docs.microsoft.com/en-us/power-bi/fundamentals/

Azure Synapse is an enterprise analytics service that accelerates time to insight across data warehouses and big data systems. You can learn more on it @ https://docs.microsoft.com/en-us/azure/synapse-analytics/

Recently, I had a chance to work on specific use case involving Power Bi report that had PHI(Personal Health Information) data masked in Synapse. Few Power Bi report users should be able to see PHI data unmasked.

Here are the list of steps we would be following in this blog

Create Data Masked table in Synapse
Add AD user and provide reader access
Validate masked data using SSMS and check query activity
Create a Power BI report with Masked data and publish it
Use Gateway or Vnet integration

=============

Create Data Masked table in Synapse

Let’s start by creating a sample table with few columns masked

-- Create a Data Schema
CREATE SCHEMA [Data]-- table with masked columnsCREATE TABLE [Data].[Masktest](
    [FirstName] nvarchar(4000) MASKED WITH (FUNCTION = 'partial(1, "xxxxx", 1)') NULL,
    [LastName] nvarchar(4000) NOT NULL,
    [Phone] nvarchar(4000) MASKED WITH (FUNCTION = 'default()') NULL,
    [Email] nvarchar(4000) MASKED WITH (FUNCTION = 'email()') NOT NULL,
    [DiscountCode] bigint MASKED WITH (FUNCTION = 'random(1, 100)') NULL
    )
WITH
    (
 DISTRIBUTION = ROUND_ROBIN,
     CLUSTERED COLUMNSTORE INDEX
 )
GO

Ingest Sample data into above table

COPY INTO Data.Masktest
(FirstName 1, LastName 2, Phone 3, Email 4, DiscountCode 5)
FROM 'https://gist.githubusercontent.com/prashanthmadi/804616aee536eb8242cf3d9873a5b206/raw/1615b9da210905934b2639279933c0daf6d958a3/test.csv'
WITH
(
FILE_TYPE = 'CSV'
,MAXERRORS = 0
,FIELDTERMINATOR = '\t'
,FIRSTROW = 2
)

Display table data to make sure it ingested correctly

SELECT TOP 100 * FROM Data.Masktest

2. Add AD user and provide reader access

CREATE USER [psingla@microsoft.com]
FROM EXTERNAL PROVIDER
WITH DEFAULT_SCHEMA = [Data];EXEC sp_addrolemember 'db_datareader','psingla@microsoft.com'

Note : You should run above on your database level and make sure to insert database name in SSMS while connecting instead of using default else you would end up with login error..

3. Validate masked data using SSMS and check query activity

Here i can see all data as admin

Here is my friend with reader role access with data masked

You can check query activity in Azure portal for troubleshooting purpose

4. Create a report in Power BI desktop with Masked data and publish it

Here is the same report on power bi online after publishing..

Here is the same report as viewed by non-admin user..

5. Use data Gateway or VNET Connectivity to secure resources

I haven’t explored on VNET Connectivity for PBI yet(as it’s still in preview — 08/2021) that would eliminate maintenance of gateway nodes.. You can read more on it @ https://powerbi.microsoft.com/en-us/blog/announcing-vnet-connectivity-for-pbi-datasets-public-preview/

on-premises data gateway acts as a bridge to provide quick and secure data transfer, I will be covering this for remaining part of this blog..

At first, you need to setup on-premise data gateway, In my scenario, i have used my personal laptop.

Here is the Gateway connection for my dataset..