Refresh using gateway failing

Below are the steps taken to fix the issue:

AZURE

• For Storage account :
• Allow access to data Gateway subnet for the storage account.
• Add “Storage Blob Data Reader” permission to the SP for the storage account.
• For SQL server :
• Allow access to data Gateway subnet for the sql server.
• Add “Reader” permission to the SP for SQL server.
• Add SP to local SQL server.

POWER BI:

• From Power BI desktop:
• Two sample reports created, one for connectivity with Azure SQL Server and other one with Azure Blob Storage
• From Power BI service:

1) Configuring the Gateway with the data sources- Azure SQL databases & Azure Blob Storage

               a. Authentication Method used- OAuth2

               b.Privacy Level Setting- Organizational

Refer article: https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-data-sources

2) Addition of users to data source so that the configured gateway is visible to them

Settings --> Manage Gateways --> Users

Refer article: https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-data-sources#add-users-to-a-data-source

3) Connection to the data sources using the Application account credentials

As there was a blocker because of an issue in Power BI service where it was picking up the admin’s credentials every time while trying to put credentials of the service principal account.

Workaround: Add service account for BLOB and AZURE SQL  as admin to enter the credentials for Gateway. Please note that the accounts were removed post saving the credentials from the application account.

Refer article: https://docs.microsoft.com/en-us/power-bi/admin/service-admin-role

4) In the Dataset --> Settings section, enable the usage of gateway which should show as Green and Running and ready to be used.

Refer article: https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-data-sources#use-the-data-source-for-scheduled-refresh-or-directquery