Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PC2790
Community Champion
Community Champion

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
Community Champion
Community Champion

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
v-eqin-msft
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 @v-eqin-msft  

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
Community Champion
Community Champion

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

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors