Restrict access to data during report design and scheduled refresh

[xabikos]

The company I am working for offers a data collection platform hosted in Azure and offered as SaaS.

Our requirement is to allow our users to design reports in Power BI desktop, then upload those reports in our application, store them in Power BI and finally render them inside our web application. This is the app owns data workflow as described in Power BI site. A hard requirement is to not import any data on the report but rather use the Direct Query method so every time the report is render in the app fresh data are shown to the user. To achieve this we need to connect directly to the azure database which supports direct query. 

In order to authenticate against database we are using Azure Active Directory users as we want to apply security permissions on the database level by using Row Level Security. As a result the user should is able to only see the portion of data is allowed during designing the report. This workflow is represented in the following picture

Currently the only way to connect to an azure sql database through AAD authentication is by using the ODBC driver. When we do that though we can't create a direct query report. 

After trying all the different options we could find online we decided to use the ODBC connection method from Power BI desktop, to design and import the report in Power BI. The next step is to schedule refresh of the data, even if the report uses the ODBC connection. We decided to do this one in order to mimc the direct query/live refresh functionality. To achieve this you need to set up a data gateway which is a really to complicated for what we want to achieve. Another problem is that this refresh is either daily or weekly.

- Is it indeed not possible to use direct query in the above setup using AAD authentication on the Azure Database (no direct query with ODBC/AAD)?
- Is the only other way to refresh the data the usage of the the 'complicated' data gateway? The setup works but it adds quite some complexity which we rather avoid.
- Is there a way to increase the refresh rate? With the data gateway we only achieve daily/weekly

And to take it a bit further I would be more than happy to hear if there is an alternative of connecting to the database from Power BI desktop and be able to apply data access security permisions to users designing the reports? 

To give a better understanding of the probelm I am posting two additional workflows, the first one describes how we would like to import the reports to Power BI and the second how to embed the report inside our web app.

[v-yulgu-msft]

Hi @xabikos,

---

- Is it indeed not possible to use direct query in the above setup using AAD authentication on the Azure Database (no direct query with ODBC/AAD)?

- Is the only other way to refresh the data the usage of the the 'complicated' data gateway? The setup works but it adds quite some complexity which we rather avoid.
- Is there a way to increase the refresh rate? With the data gateway we only achieve daily/weekly

---

- Yes. Currently, Power BI doesn’t support AAD authentication when connecting to Azure SQL Database, but using Microsoft ODBC Driver 13 it is possible to do it which connects to data source with import mode. Someone has submitted this request here and PG says this is in plans for the rest of CY17.

- For Power BI, only direct query/ live connection supports automatic update. To refresh a dataset in import mode, we have to configure gateway for it. Reference: Databases in the cloud

- Gateway only provides two options: Weekly and Daily. While you can add additional refresh time, the maximum refersh rate is 8 per day. Power BI Premium doesn't have this limitation.

Best regards,
Yuliana Gu