Forum Discussion
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-server/m-p/1966089#M746105, 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?
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.
5 Replies
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.
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".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.
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.
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 Anonymous
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.
