Power BI Gateway with Azure Database

[lcasey]

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.

[Seth_C_Bauer]

@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.

[lcasey · 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.

[v-qiuyu-msft]

Hi @lcasey,

Glad to hear the issue has been solved by yourself.

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