Connect PowerBI to Azure SQL Database through on prem gateway

[rcpbi]

I have a report that uses Azure SQL database as a datasource.  I want to setup a scheduled refresh, but we do not want to expose the Azure SQL database to the public internet.  We tried using the on premise gateway to connect so we setup a connection using OAuth 2.0, but when I try to choose that connection in the service, it's not available to select.  

In the semantic model, the connection is listed under "Cloud Connections" and my only option to select on the "Maps to:" dropdown is "Personal Cloud Connection" and "Create a connection".  

When setting up the connection in PowerBI Desktop I chose SQL Server just like I do with other on prem sources.  Is there a step I'm missing?

[lbendlin · Accepted solution]

Did you choose to allow your cloud connections to be served by the gateway?

[rcpbi]

I ended up creating a new report with the same connection and the connection from the gateway shows up. There's something different about the pbix file, however, I cannot see anything noticeable when comparing the two files.  Thanks for your help @lbendlin. 

[v-tsaipranay]

Hi @rcpbi ,

I'm glad to hear the new report works with the gateway. It seems the original .pbix file might have retained outdated metadata or cached settings. This can happen sometimes, and starting fresh often resolves it.

Thanks again to @lbendlin  for the helpful insights. If this solution resolves your issue, please mark it as accepted to help others with similar questions.

Thankyou.

[v-tsaipranay]

Hi @rcpbi ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-tsaipranay]

Hi @rcpbi ,

I wanted to check if you had the opportunity to review the information provided by @lbendlin . Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[lbendlin · Accepted solution]

Did you choose to allow your cloud connections to be served by the gateway?

[rcpbi]

Thanks.  This setting on the gateway was not enabled so we enabled both, however, I'm still unable to select the new connection.   

The new connection was created as an "On-premises" connection.  

[lbendlin]

Was the connection test in the gateway successful?

[rcpbi]

Yes, the connection in the gateway was successful.

[lbendlin]

how did you test that? Did you install Power BI Desktop on the gateway VMs and created a successful connection from that?

[rcpbi]

When you setup the connection on the Gateway, there's a checkbox that says "Skip Test Connection".  We did not check this box so our assumption is, it runs a test before creating the connection otherwise it won't allow you to create the connection.

[lbendlin · Accepted solution]

That test is not always conclusive. Better verify with a real data transfer.