SQL Gateway Cluster question

erceve · ‎05-25-2020

situation

I have a single report which collects data from three separate sql servers (A, B, C) and I cannot get my gateway cluster to actually retrieve data, it fails without exception. When data from only one of the servers is used in a report, the report is able to refresh, but as soon as I add a gateway to the cluster and report to another server, refresh fails.

These three servers are completely separated and don't have shared accounts and for each different data source I've filled in the correct windows accounts which work when they are used outside of the cluster but once a report uses data from more than one server,the refresh consistently fails with the message: "invalid connnection credentials".

Am I missing something? Is there a guide on how to set this up?

Anonymous · ‎05-25-2020

Hi @erceve ,

Please refer the below topic. You can find some solutions under Comments. Check if there's a soultion works for you.

https://community.powerbi.com/t5/Issues/on-premises-data-gateway-s-service-account-failed-to-imperso...

By the way, I'm not sure why you are using Gateway Cluster. As far as i know you can use one gateway for multiple datasurces.

https://community.powerbi.com/t5/Desktop/Schedule-Refresh-w-Multiple-Datasources/m-p/1095691#M507662

Best Regards,

Jay

Community Support Team _ Jay Wang

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

Anonymous · ‎06-03-2020

Hi @erceve ,

Did the problem be solved?

Best Regard,

Jay

erceve · ‎06-08-2020

Unfortunately none of suggested solutions have been helpful

some more details:

I have 3 sql servers from which I retrieve data, my workaround right now as its a monthly report: download published pbix, move file to server 1, manually update tables, save, move file to server 2, manually update tables, move file to server 3 etc....

On premise gateway is installed in machine 1, a report which uses only data from machine 1 has working refresh.

machine 2 has on premise gateway installed as part of the gateway cluster, a report which uses data from both servers fails to refresh with the error described above.

Now I have 3 accounts, my powerbi account, an ad account from server 1, ad account from server 2, credentials are filled in correctly in the different data sources so why do I still get authentication errors?