Connecting Power BI Desktop to a remote SQL Server with no VPN connection

[KevinColes]

Hi everyone,

I have a new situation that came up which I'm trying to find a good solution for. Here is the high level view of the setup for my client:

• Main data source for all reports is an ODBC connection provided by the vendor to access a cloud based ERP. This works great and I've developed a package of reports. I do all of my development on my local machine and publish to the service. I'm able to do this by having an ODBC DSN locally connecting to the cloud ERP that matches what the client has on their Gateway server.
• The gateway server is a dedicated server on the client's network and it works well and the Service connects fine.
• New requirement came in to develop some trending reports that will require point in time data to be captured over long periods to track Earned Value against Budgets of projects over time. In order to capture that I have a SQL Express instance on the Gateway server that queries the ERP over the ODBC connection. This is working great and I'm now capturing daily stats.
• I developed a simple report using the local SQL data and published it to the service. This worked fine and it now can connect to refresh through the gateway.
• My conundrum is that I am only being given limited access to the Gateway server and the client doesn't want it open all the time. Add to that the fact that I need to build the new report(s) in an existing report file package that I maintain locally on my machine. 

So with no VPN to the Gateway and no ports open for SQL, what are my options to develop against the SQL Express sitting on the client network? Will I have to copy my report file over to that server and have them let me do soem development over RDP? And what happens when I get back to other reports and my local machine is unable to connect to the second data source? Or should I develop through the service (which seems less intuitive and doesn't have all of the capabilities of Desktop)?

Looking for some options so any advice is greatly appreciated!

Kevin

[parry2k]

@KevinColes Not sure you have many choices? Simply you need access to the SQL server, either thru RDP to the clients networks and do the development their assuming you have access to SQL server from the RDP machine or make a replicate of SQL server at your end, develop report connecting to local server, use parameters for connection to the SQL server, after report is published, change parameter value to connect to client's SQL server and you are good to go.

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.