Connecting to on-premises SQL Server

[rbrtbvsschllnr]

I hope someone can help me... I develop for a customer from home. I use Power BI Desktop on my laptop to connect to a backup of their SQL database hosted on my laptop (mycomputername\sqlserverinstance). I modify the reports / dashboards as pbix files, then change the data sources in the pbix file to point to their local server (clientsqlservername\instance). I then save the pbix file without applying the pending query changes (of course I can't, I'm not on their network). The client then opens the pbix file, hits refresh, and the dashboard updates by connecting to their on-premises SQL server. It's worked.

Now, we are moving to the Power BI Service, but I still need to develop from home. The gateway is setup (enterprise mode, on their SQL server), and connected to a Power BI Pro account. We have also added the data sources (databases) to the gateway cluster. The connection works.

The problem I'm having, is that I cannot access the SQL databases required in my reports from home. If I change the data source settings in the pbix file, and publish to the service, the service still thinks my data source settings are pointing to my local SQL instance (mycomputername\sqlserverinstance, not clientsqlservername\instance), and does not refresh in the service.

How, oh how, do I go about doing this? I don't want to have to develop on-site, on their network..?

[v-piga-msft]

Hi @rbrtbvsschllnr ,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best Regards,

Cherry

[v-piga-msft · Accepted solution]

Hi @rbrtbvsschllnr ,

I'm afraid that If your power bi desktop and the SQL server are not in the same domain, you could use SQL authentication to have aceess to the database.

In addition, if you want to refresh the dataset in Power BI Service, I think the Server name, database name and the Authentication method should be the same in the power bi desktop.

Best Regards,

Cherry