Accessing two databases from one Gateway - "Invalid connection credentials"

[mhggr]

I have an issue with an SQL view that I try to access via a Report published online.

The gateway is configured for DB1 where my view is stored. That view queries both DB1 and DB2.
Both DB1 and DB2 are on the same server.

PowerBI seems to only be able to connect to tables in DB1, not DB2 - or more specifically - only connects to tables in the database specified in the gateway settings. The credentials used for the gateway should have access to all databases on that server though.

It would be very helpful to know whether this is a known limitation or if this is a fault on my end. 


Here is the online view and below how it is is locallyweblocal

[djurecicK2 · Accepted solution]

Hi @mhggr ,

 Sounds like you need to add a data source on the gateway for DB2. Here is additional information on how to do that:

https://learn.microsoft.com/en-us/power-bi/connect-data/service-gateway-data-sources#add-a-data-sour...

[Anonymous · Accepted solution]

Hi djurecicK2 ,thanks for the quick reply, I'll add more.

Hi @mhggr ,

Regarding your question, did you configure the data source credentials only for 'Database 1'? (Using the database administrator account) You need to create different data source credentials for 'Database 1' and 'Database 2' respectively.

Best Regards,
Wenbin Zhou

[mhggr]

We did.

I noticed that it maps to two different gateways. Is this the issue? I could not find a way to put two DBs in the same gateway so I created two. They are in the same cluster though

[djurecicK2 · Accepted solution]

Hi @mhggr ,

 Sounds like you need to add a data source on the gateway for DB2. Here is additional information on how to do that:

https://learn.microsoft.com/en-us/power-bi/connect-data/service-gateway-data-sources#add-a-data-sour...

[mhggr]

I have added some screenshots.

We have gateways for both DBs and they are in the same cluster. 

Thanks for the help though. Since it seems to straightforward, it must be something I did wrong.