Gateway 'Online: Good to Go' but refresh fails with 'Invalid Connection Credentials'

[pistachio]

Seen a few similar problems but no documented solution to solve my issue. I have a .pbix file referencing our SQL server 2008. It refreshes fine in desktop mode. I have an On-Premises gateway running with a successful connection status.

On upload to the Power BI service the dataset publishes well but cannot refresh. I have two gateway clusters (one redundant, unable to be removed as it is the 'primary gateway within a cluster'),

* In the 'Manage Gateways" screen

    - The remaining cluster (Primary Cluster) has our SQL Server as a data source with status 'Connection Successful'

    - The Primary Cluster has status 'Online: You are good to go'

* In the settings > Datasets menu

    - The dataset is configured to use the gateway.

    - Scheduled Refresh is set up.

Both scheduled and on-demand refreshes fail with the message 'Invalid Connection Credentials'. I found this person used Network certificates: https://community.powerbi.com/t5/Service/Online-Service-Invalid-connection-credentials/m-p/410539#M4... but I do not know how to troubleshoot these.

The error message does not identify a troublesome user like the Microsoft help page suggests it should:

Last refresh failed: Tue May 29 2018 12:15:45 GMT+1200 (New Zealand Standard Time) 
Invalid connection credentials.Hide details

Cluster URI:	WABI-AUSTRALIA-SOUTHEAST-redirect.analysis.windows.net
Activity ID:	c9adcd80-5fa7-46d7-bde9-ddf81b2ae282
Request ID:	3d0e1b34-f545-4671-a518-3ec27867504e
Time:	2018-05-29 00:15:45Z

[pistachio · Accepted solution]

After my tidying of the gateways using the Powershell scripts in another comment I found this link on modifying your instances to use a fully-qualified server address. I then:

- Pinged the SQL server from the computer with the gateway to get a longer domain name - '[SQL].[Server].[Domain]'

- Updated the .pbix data source to this server name. Refresh, save, upload to power BI online

- Deleted the old power BI online data source. Created a new one with this longer server name.

- Restart gateway. Log in again (unsure why I was logged out)

Refresh has now completed successfully once and will hopefully continue.

[GilbertQ]

Well done on getting it working. I always suggest putting in the FQDN so that it can br resolved via DNS

It appears that you have resolved your issue?

[Anonymous]

Does your database use any form of IP Address whitelisting or any other security feature that might be seeing your Gateway as the request source and denying it access?

[pistachio]

I do not believe so, but I do not know how to test. The Gateway is running as a service on a server which I can remote into. From this server I can also use Excel data sources to directly query the SQL server.

[GilbertQ]

Hi there,

Can you confirm that the credentials that you used to the SQL Server Database can connect and run queries against it?

It would appear that potentially it is not 100% able to run the queries.

[pistachio]

I confirm that with these credentials entered into the 'Data Source Settings' of Power BI desktop, the .pbix file can refresh data. This includes some native SQL queries so I believe my credentials have full read access. Is there any other test I should do?

[GilbertQ]

You are running the latest version of the Gateway?

[pistachio]

Yes I've updated to the May Gateway release. My Power BI desktop is May 2018 too

[GilbertQ]

Hi there

Could it possibly then be with the way the Gateways are set up?

In your message you say that you have got them in a cluster, but the Primary one is not working?

I would suggest if possible, to install the Gateway on a new server and take over using the Recovery Key.
Then using PowerShell remove the Gateway from the Cluster that is no longer needed.

[Anonymous]

Another test you could perform prior to attempting the movement of your gateway, is to install Power BI Desktop on your server and attempt to do a refresh using the PBIX file you have.  This tests that the machine itself has access to the data source.

I'm unsure if there are any incompatabilties with Power BI desktop and different Server OS's, but i know the test can be performed generally.

[pistachio]

To continue with this - I had Power BI Desktop (and a gateway instance) installed on the server. This desktop file can refresh fine on this server. In further digging I found that my actual gateway layout was:

Cluster 1 (disabled as had created new one, could not delete as primary cluster)

- Gateway 1 (disabled - was installed on my laptop. Not visible in Power BI service)

Cluster 2 (signed in on server)

- Gateway 2 (enabled - installed on server)

I deleted the second pair by uninstalling the gateway from the server, removing the online cluster, and I re-initialised a server gateway (Gateway S) in cluster 1.  The new layout was:

Cluster 1

- Gateway 1 (laptop)

- Gateway S (server)

This still failed as Gateway 1 was not contactable (the gateway was uninstalled from my laptop). 

I found my laptop and reinstalled on-premises gateway, and signed into Gateway 1. The online refresh error then changed to:

Cannot connect to the mashup data source. See error details for more information.

Using this powershell page I was able to delete the laptop gateway, which reverted the online error to 'invalid connection credentials'. I uninstalled the gateway once more from my laptop.

To my understanding I now have one gateway inside one cluster, installed on a server which can update a .pbix file but not via the online service. Any advice appreciated.