Skip to main content

Privacera Platform

Table of Contents

Configure PowerBI Gateway with MSSQL server

:

This topic shows how to publish your on-premises SQL data source to the Power BI service in Azure via the Power BI gateway, and configure access-control for the following:

  • On-premises SQL data source using the Privacera MSSQL connector

  • Power BI service using Privacera Power BI connector

Configuration

  1. Download and install the following on your Windows system:

    • SQL Server 2019. For more information, refer the Microsoft Azure documentation - click here.

    • Power BI gateway and log in using AAD credentials. For more information, refer the Microsoft Azure documentation -click here.

    • PowerBI Desktop and log in using AAD credentials. For more information, refer the Microsoft Azure documentation -click here.

  2. Configure the UserSync for AAD to pull users/groups into Ranger. For more details, refer Azure Active Directory - Data Access User Synchronization.

  3. Configure PolicySync MSSQL connector with the Microsoft SQL server installed on the Windows system. For more details, refer to the topic MSSQL connectors.Microsoft SQL Server

    Follow the steps in the link, and configure the following properties:

    1. Set the MSSQL_EXTERNAL_USER_AS_INTERNAL to true. On-premises SQL server does not support the authentication of an external AAD user. You need to set this property to convert external AAD users to local database users.

      MSSQL_EXTERNAL_USER_AS_INTERNAL:"true"

    2. Add the external AAD users to be converted to internal database users.

      MSSQL_MANAGE_USER_LIST:"user1,user2,user3"

      Now, you can provide access-control on these users.

Use Case

Let's see how is access-control achieved in the Power BI Desktop application.

  1. In to the Privacera Portal, add a MSSQL connector and create the following policy for the AAD user listed in the MSSQL_MANAGE_USER_LIST property of the MSSQL connector.

    powerbi-sql.jpg

  2. On the Power BI Desktop, connect to the database in the Microsoft SQL server with the AAD login credentials, and load the data. For more information, refer the Microsoft Azure documentation -click here.

    Depending on the permissions granted to the AAD user in the policy, the SQL server will load the data for the AAD user.

  3. Click the Publish icon from the ribbon on the Home tab.

  4. On the Save As dialog, enter File name, and then click Save.

    Workspace will be listed as per the user’s workspace level permissions.

  5. Select the workspace, and then click Publish.

    After you publish, the data gets uploaded to the workspace of the Power BI service. For more information, refer the Microsoft documentation - click here.

    Now, you can apply access-control on the workspace of the Power BI in Azure. For more information, see Power BI PolicySync.Power BI