cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PC2790
Super User
Super User

Refresh using gateway failing

Hello community members,

 

Based on the solution provided in the thread-https://community.powerbi.com/t5/Desktop/Require-IP-range-for-whitelisting-to-access-the-Azure-SQL-s..., we decided to go with the approach of using a gateway for the refresh of data for data sources Azure Blob Storage and Azure SQL server.

So the following activities have been done to configure the gateway:

1) Added a public IP associated with Power BI Gateway

2) Provided "Reader" role to the service principal accounts(one for Blob and other for SQL)

3) Configured an the gateway y adding the two data sources and adding the credentials for service principal account.

Even After doing these things, I am getting this error while trying to refresh:

 

{"error":{"code":"DM_GWPipeline_Gateway_InvalidConnectionCredentials","pbi.error":{"code":"DM_GWPipeline_Gateway_InvalidConnectionCredentials","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"The credentials provided for the AzureBlobs source are invalid. (Source at https://sastdc360kpiwe1dev2.blob.core.windows.net/powerbi.)"}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.CredentialError.DataSourceKind","detail":{"type":1,"value":"AzureBlobs"}},{"code":"Microsoft.Data.Mashup.CredentialError.DataSourcePath","detail":{"type":1,"value":"https://abcxyzkjas123.blob.core.windows.net/powerbi/"}},{"code":"Microsoft.Data.Mashup.CredentialError.Reason","detail":{"type":1,"value":"AccessUnauthorized"}},{"code":"Microsoft.Data.Mashup.MashupSecurityException.DataSources","detail":{"type":1,"value":"[{\"kind\":\"AzureBlobs\",\"path\":\"https://abcxyzkjas123.blob.core.windows.net/powerbi/\"}]"}},{"code":"Microsoft.Data.Mashup.MashupSecurityException.Reason","detail":{"type":1,"value":"AccessUnauthorized"}}],"exceptionCulprit":1}}} Table: powerbipoc.

 

Can anyone please suggest why this error is being shown or what is the right way to do it please?

1 ACCEPTED SOLUTION
PC2790
Super User
Super User

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-d...

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-sou...

View solution in original post

5 REPLIES 5
Eyelyn9
Community Support
Community Support

Hi @PC2790 ,

 

As the error says you need to go into the dataset settings, click on Data Source Credentials and put in the valid credentials for your Azure Blob Storage account.

 

And please check your Azure Blob storage firewall configuration, the firewall setting of the azure storage account need to whitelist the IP address of the Power BI Service. If these IP address are not whitelisted, the access will not succeed.

 

Reference: Not able to connect with storage account for Azure backup report.

Azure blob.PNG

 

Best Regards,

Eyelyn Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks @Eyelyn9  

However I wanted to avoid putting a long list of IPs in the whitelisting and hence decided to go via gateway route.

The last post around the same can be found here

Also, in this approach the crdentials are not required to be entered in the 'Edit Credentials' section as they are automatically picked up while configuring the gateway.

PC2790
Super User
Super User

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-d...

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-sou...

View solution in original post

Greg_Deckler
Super User
Super User

@PC2790 From the error message it looks like you are using a wrong account or password. Things aren't being blocked,  you are just failing authentication. 

 

You could check the Issues forum here:

https://community.powerbi.com/t5/Issues/idb-p/Issues

And if it is not there, then you could post it.

If you have Pro account you could try to open a support ticket. If you have a Pro account it is free. Go to https://support.powerbi.com. Scroll down and click "CREATE SUPPORT TICKET".


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Thankyou @Greg_Deckler 

The credentials were correct as it was logging in fine using teh same credentials in the azure portal to access that particular storage account.

After rigorous brain storming sessions, the infra team figured out a solution and it is wokring fine now.

I'll post that in a while in case if anyone stucks due to the same issue.

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!