Power BI Security

Owned by Ian MacRae
Oct 12, 2021

The Power BI service uses a multilayered security approach to ensure end-to-end security and limit the the data available to that which is necessary for the Power BI Sync.

Power BI database roles and user

Two roles are setup in the SQL Server

  • Synergetic_PowerBI_dbo_ReservedDBRole 
  • Synergetic_PowerBI_finance_ReservedDBRole    

Note: If you have additional finance schemas, you will have additional roles per finance schema

Each of the roles grant read only access to the database objects required for sync.

There is one database user created zPowerBI. This user is a member of the above roles.

The roles and user are maintained by Synergetic. Do not make any changes as they may be overwritten during the next update. See below for how to customise the security.

The zPowerBI user is also added to Synergetic Group / User Security. It is not added to any groups but is granted access to all GL accounts


You may alter the GL accounts that this user has access to. However, the user does not have access to the GL, this will prevent it from reading the data for the finance dashboards. Results may not be accurate.

Power BI Gateway

Synergetic install the Power BI Gateway service on a server in your school. The gateway service acts as a conduit between the school and your Microsoft cloud instance of Power BI Online.  The gateway is configured in Power BI Online to connect to your server and database

Do not alter this connection. Doing so will break the sync.

Dashboard datasource configuration

Each dashboard has at least 4 parameters that are configured to use the gateway.

Do not alter these settings.

Scheduled Sync

Each dashboard is configured to automatically synchronise once a day at some point between midnight and 6:00 am. Microsoft imposes the following conditions.

  • The sync time may start within a 30 minute window (15 minutes before to 15 minuts after) of the scheduled time.
  • If a dashboard is not visited for 4 weeks it is considered inactive and the sync is disabled. Visiting the dashboard will re-enable the sync which will take place at the next scheduled time.
  • If a sync fails four consequtive times, it is disabled. It will have to be manually re-enabled

You may adjust the sync settings to suit your school. Bear in mind that the sync imposes a load on the server.  It is recommended to stagger the dashboards so that there are not too many synchronising at once, and that there is no other load on the server (for example backups).

Customising security

If you create your own dashboards, you will need to customise the security to the allow additional access to the database objects.

Create a custom role

Create a custom role in Microsoft SQL management studio. The role name must start with "U". For example, UPowerBI_Role. The owner is dbo.

Add the zPowerBI user to the role

In the Securables page grant read access to the resource that you use.  That is grant Exectute or Select access as required.

Create a database user

Create a SQL user with password in Microsoft SQL management studio.

  • It is recommended that the name reflect the purpose. For example, uPowerBI.
  • The password must meet the password requirements of the school. Note the password. You will need it when creating Power BI dashboards.
  • The default schema must be dbo

In the Membership page add the user to the Synergetic Power BI roles and the custom role you created

Configure Power BI Desktop

When you create a dashboard and connect to your server, use the user and password created above. This will ensure that the user has the correct access to the database objects and ensure a reliable sync.

In the connections window, choose the database tab to connect using a database user and enter the credentials