Multiple SQL Databases with Enterprise Gateway?

[martynshort]

Hi all,

Firstly, I'm not a PowerBI or SQL guru, so go easy on me if I ask any stupid questions please 🙂

I'm trying to configure PowerBI for my company, I've got the enterprise Gateway set up and working, and I'm trying to add my data sources. I've got 2 SQL databases I'm trying to connect to, both on the same server, and I've been able to add these to the gateway without issue. However, if I try to connect to both in the PowerBI Desktop app, I get a "Switching to Import Mode" message, saying that the data source is different?

I thought this should work, but I may be doing something wrong. it would be amazing if anyone had any suggestions!

[ankitpatira]

@martynshort 'Switching to import mode' warning usually comes up when you're connecting to multiple data sources within power bi desktop (in your case 2 sql database) and one is using directquery and other import method ie when you're connecting to two data sources then mechanism to connect should be same for both. If using directquery for one then other should be DQ as well and if using Import then both should be import.

[martynshort]

Thanks both for your replies. Ive check that the names in the gateway settings and what I'm using in PowerBI Desktop match, and I'm pretty confident I've got this right. I'm also trying to connect to both databases using DirectQuery. 

I can connect to both SQL databases using DQ individually, but if I try to add in the second (doesn't matter which way around I do this) I get the 'Switching to Import Mode' error...

Can I just confirm that what I'm trying to do is actually possible? I'm assuming this should be somethng that's easy to do, but I could be wrong?

[v-qiuyu-msft]

Hi @martynshort,

In your scenario, it supposed that you are connecting to two databases from the same SQL Server instance. But when we connect to the SQL Server databases in DirectQuery mode, it requires "All tables must come from a single database". So if you connect to two different databases, it will prompt you to change the DirectQuery mode to Import mode. For more limitation in DirectQuery mode, please refer to this article:  Limitations of DirectQuery .

If you have any question, please feel free to ask.

Best Regards,
Qiuyun Yu

[GilbertQ]

Hi there

When working with SQL Server databases and the On-Premise gateway ensure that the name you use to connect to your databases is identical when setting up the connection in your gateway settings in the Power BI Service.

For example when connecting in Power BI Desktop you put in the following names:

Server Name: DatabaseServer1

Database Name: Sales

Now when in the Power BI Service if you had to put the following it would not work

Server Name: DatabaseServer1.mydomain.com

Database Name: Sales

This is because the Power BI Service looks at the Power BI Desktop file and where they match exactly, it will determine that these are the same, and then enable the data refresh, as well as the direct query.