Synapse>>Power BI

Sourav 80 Reputation points


We have some sensitive data in azure synapse, the synapse view will be accessed by Power BI user for reporting.

  1. What is the best practice for sensitive data (PII) for production , do we mask the columns that has sensitive information ?
  2. If we mask the column data in synapse how can a user from Power BI access it if they need to access the columns ? What is the RBAC or permission that we need to grant, where and how ?

Thanks !

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,553 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Harishga 4,875 Reputation points Microsoft Vendor

    Hi @Sourav
    Welcome to Microsoft Q&A platform and thanks for posting your question here.

    To handle sensitive data (PII) in Azure Synapse Analytics when accessed by Power BI users for reporting, it is recommended to use column-level security. This means that you can restrict access to specific columns in a table or view based on the user's role or permissions.

    Regarding masking columns with sensitive information, it is a good practice to mask the data if it is not required for the user's reporting needs. You can use dynamic data masking to mask the data in Synapse Analytics. This will ensure that the sensitive data is not visible to unauthorized users.

    If you mask the column data in Synapse Analytics, Power BI users can still access the data if they have the appropriate permissions. You can grant the necessary permissions to Power BI users by assigning them to the appropriate role in Synapse Analytics. The roles that you can assign to Power BI users are:

    • Data Reader: This role allows users to read data from tables and views in Synapse Analytics.
    • Data Contributor: This role allows users to read and write data to tables and views in Synapse Analytics.
    • Data Owner: This role allows users to manage data in Synapse Analytics, including creating and dropping tables and views.

    To assign roles to Power BI users, you can use Azure Role-Based Access Control (RBAC). RBAC allows you to manage access to Azure resources based on roles. You can assign roles to users, groups, or applications. To assign roles to Power BI users, you can follow these steps:

    • In the Azure portal, navigate to your Synapse Analytics workspace.
    • Click on the "Access control (IAM)" tab.
    • Click on the "Add" button and select "Add role assignment".
    • Select the appropriate role (Data Reader, Data Contributor, or Data Owner) from the "Role" dropdown.
    • In the "Assign access to" section, select "User, group, or service principal".
    • Enter the email address of the Power BI user in the "Select" field.
    • Click on the "Save" button to assign the role to the Power BI user.

    By following these best practices, you can ensure that sensitive data in Azure Synapse Analytics is secure and only accessible to authorized users.

    Hope this helps. Do let us know if you any further queries.

    If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

    0 comments No comments