Azure SQL DB and OnPrem data refresh question

[eskyline]

I have various reports that will connect to on-prem SQL Server DBs (via the enterprise gateway connections) and now what will also bring in data in Azure SQL DB.  I've done some experimenetation and I can get this to work.  I can use the gateway connection for the on-prem database and enter credentials directly in the service "Schedule Refresh" settings for the Azure SQL DB. 

BUT.... ideally I'd like to use an account with a non-expiring password and governed by IT (i.e. a resource account the not everyone knows) for the Azure SQL DB access similar to like we do for our On-Prem gateway connections.  So.... I tested also creating a Gateway connection to the Azure SQL DB using a straight SQL Auth User ID/Password.  That worked fine and the dataset refreshed.

My question is this...... in this scenario, where I use the gateway connection to the Azure SQL DB will the Gateway suck all the data down on-prem (from Azure SQL DB) and then push it back up to the service? 

That would make it "cloud to on-prem to cloud" as opposed to "cloud to cloud".  With small amounts of data that may be acceptable from a cost and refresh time persepctive but not for larger datasets.  I can do a network trace but wondered if anyone had any insight here.  I do understand that you can also setup a gateway in an Azure VM.  That might be an option but would have additional cost ramifications.

Any input is appreciated.

[GilbertQ]

Hi there

If your source changes from an On-Prem SQL Database to Azure SQL DB, then there is no need for a On-Premise Gateway.

Once you make the data source changes, you should be able to edit your Data Source settings and change it from using the On-Premise Gateway to just the cloud?

It will then transfer the data directly from the Azure SQL DB to the Power BI Service. And as long as they are in the same tenant there should be no egress charges

[eskyline]

GilbertQ, thanks for the response.

I do know that users can setup the credentials for the refresh out in the service.  My concern is that when their passowords expire the refreshes will fail.  I want to use an IT controlled userid/password combination with a never expiring password and I don't want to give those credentials out to my business users.  So.. the only solution for that would be for any business user that wants to publish a dataset accessing teh Azure SQL DB to contact IT to setup their refresh schedule.  That is not ideal.

Defining a connection on the gateway does allow me to have an IT managed solution that does not expose the IT controlled userid/password because there I can define the connection (I would be the only one knowing the credentials) on the Gateway and I could add each business user as a person that can publish and use that connection.  That may cause the data to traverse cloud to on prem to cloud.  I'll be tracing this scenario Wed with my network guys.