Refresh data from VPN-secured Salesforce

[Anonymous]

I have an issue when I try to refresh a published report from Salesforce objects.

Salesforce is white-listed so it can be accessed only from VPN.

It is ok when I try to refresh data from PowerBI desktop. It is obvious that I can't use Salesforce content pack to refresh data in app.powerbi.com. I installed Personal Gateway on the machine that is connected to the VPN.

I see this personal gateway in dataset settings. But Salesforce credentials after publishing report becomes invalid.

Your data source can't be refreshed because the credentials are invalid. Please update your credentials and try again.

When I press Edit Credentials it forwards me to Salesforce login page.

As a result, I have this error message.

Is there a way to enter/update secured Salesforce credentials?

[CoderZen08]

Hi, were you able to find a solution? As a workaround, maybe you can try to test your connection with a 3rd party connector, which does not even require a gateway in order to refresh the report. You can directly publish your report to the service and then set the refresh without a gateway. I've tried windsor.ai, supermetrics and funnel.io. I stayed with windsor because it is much cheaper so just to let you know other options. In case you wonder, to make the connection first search for the Salesforce connector in the data sources list:

After that, just grant access to your Salesforce account using your credentials, then on preview and destination page you will see a preview of your Salesforce fields:

There just select the fields you need. It is also compatible with custom fields and custom objects, so you'll be able to export them through windsor.  Finally, just select PBI as your data destination and finally just copy and paste the url on PBI --> Get Data --> Web --> Paste the url. 

[Anonymous · Accepted solution]

I got a reply from MS support

Credential acquisition and connection test are always happening from the Power BI Service. so there is no currently a workaround on our side.

It’s happen because SF is an online data source, so it will never use the PSW

But even if the data source itself ends up using a gateway, all OAuth in DMTS happens entirely in the cloud.

It seems to be true - PowerBI checks credentials online first.

To workaround, we use some trick with Salesforce DataLoader to export all data into CSV first on the machine connected to VPN and then access that CSV with the gateway.

[v-qiuyu-msft]

Hi @Anonymous,

Thank you for your sharing the solution which benefits other members.

Best Regards,
Qiuyun Yu

[v-qiuyu-msft]

Hi @Anonymous,

As you mentioned this dataset use personal gateway, is there any other data source in the report which requires gateway? If there is only Salesforce reports data source in the report, it doesn't require any gateway once publish to service. If your dataset indeed use personal because of other data source, please configure the personal gateway with proxy follow this article.

Besides, based on my test, if I create a report in desktop which only connects to the Salesforce Reports, after publish to service, we are not able to edit credential and get the same error as yours. I will report this issue internally, and update here once get feedback.

Best Regards,
Qiuyun Yu

[Anonymous]

Hi Qiuyun, thank you for the answer.

The main reason to use the gateway is that Salesforce is VPN-protected, so app.powerbi.com can't connect to Salesforce.com directly.

The interesting thing I found from Salesforce logs - after I enter credentials in dataset settings, app.powerbi.com tries to connect directly and in Salesforce log I see rejected login attempt from Microsoft IP addresses. It's pretty strange because credentials are for the Gateway and it is not supposed that Azure will try to connect Salesforce directly during credentials editing process.

[v-qiuyu-msft]

Hi @Anonymous,

Please run the Fiddler, then repeat the steps to edit the credential for salesforce. Share .saz file with us.

Best Regards,
Qiuyun Yu