cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lcasey
Post Partisan
Post Partisan

Power BI Gateway with Azure Database

Hello,

 

Is there any way to connect the enterprise Gateway with a Azure SQL database?

 

I have tried making the connecting as a Datasource oin the Gateway and it connects no issue. However, Power BI desktop cannot use the Gateway settings to connect to the Azure SQL datbase.

 

 

1 ACCEPTED SOLUTION

Hello,

 

Thanks for the reply,  I ended up creating a linked server so that I did not have to switch to import mode.  I ultimatly got the Azure SQL database working in the Gateway with no issue also, but agree with you, there is absolutely no need to use a gateway and can just use the SQL database directly.

 

I was using Our On-Premise Data Source as well as the SQL database as a source and was being forced into import mode as Power BI does not support data sources from two diferent servers. 

 

In Order to work around that, I setup my SQL Azure database as a Linked Server and now can direct query a single server and get data from that linked server. The ultimate goal was to keep direct query.

 

I hope that the Power BI team allows multiple data sources in Direct query mode like SSRS does.

View solution in original post

3 REPLIES 3

@lcasey Couple things here. Connecting to Azure SQL database doesn't require a gateway. When you connect to Azure SQL DB in the Desktop, you should just be able to publish the reports to the service and either schedule a refresh (if you imported) or it won't require a refresh if you used Direct Query... See here for more details.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

Hello,

 

Thanks for the reply,  I ended up creating a linked server so that I did not have to switch to import mode.  I ultimatly got the Azure SQL database working in the Gateway with no issue also, but agree with you, there is absolutely no need to use a gateway and can just use the SQL database directly.

 

I was using Our On-Premise Data Source as well as the SQL database as a source and was being forced into import mode as Power BI does not support data sources from two diferent servers. 

 

In Order to work around that, I setup my SQL Azure database as a Linked Server and now can direct query a single server and get data from that linked server. The ultimate goal was to keep direct query.

 

I hope that the Power BI team allows multiple data sources in Direct query mode like SSRS does.

View solution in original post

Hi @lcasey,

 

Glad to hear the issue has been solved by yourself. Smiley Happy

 

However, DirectQuery mode only supported in below data sources currently:

 

• SQL Server
• Azure SQL Database
• Azure SQL Data Warehouse
• SAP HANA
• Oracle Database
• Teradata Database
• Amazon Redshift (Preview)
• Impala (Preview)

 

In your scenario, if you connect to the on-premises data source which doesn't support the DirectQuery mode, when you connect to the Azure SQL Database in this report, you can only use Import mode, as we can't have both DirectQuery and Import mode in a single .PBIX file. For more information, please refer to this article: Use DirectQuery in Power BI Desktop.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors