Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
KevinColes
Helper III
Helper III

Connecting Desktop to a remote SQL Server

Hi everyone,


I have a situation where I need to be able to develop in Desktop against both an ODBC data source in the cloud as well as a local server. Here is a high level view:

  • Main data source is a cloud based ERP that the vendor provides an ODBC connection to. 
  • The Gateway is installed on a utility server and uses the ODBC to fetch data from the ERP. This all works fine
  • We have a new requirement to capture some point in time data to do some trending so we installed a SQL Express instance on the Gateway server so it can pull through ODBC and store it in a custom DB/table. This also works fine and I'm now collecting the data for our trending reports.
  • I was able to create a simple report with a connection to the local data while logged into the Utility / Gateway server. I published it to the service and was then able to add that data source to the Gateway for refresh purposes. This also works as expected.
  • Now my conundrum. I need to build the new Trending Reports in an existing report file which I work on locally on my own machine which is not connected to the network where the Gateway is located. Up to this point I have an ODBC DSN to the cloud data source so it's never been an issue. So how can I now also access that new on premise SQL Express instance without a VPN connection (I was only recently given RDP access directly to the Gateway server)

I'm thinking that my only options may be to have a VPN connection to the Gateway server where SQL Express sits or open up ports for SQL for remote access but I think the client may not be open to that. My other concern is making sure that however I am connecting locally to develop doesn't cause issue in the Power BI Service once published.

Any advice is greatly appreciated!

Kevin

2 REPLIES 2
lbendlin
Super User
Super User

 

so we installed a SQL Express instance on the Gateway server 

 

Highly not recommended. They will fight tooth and nail for the available RAM and compute cycles.  At a minimum you should set a RAM limit for the SQL server.

 

The solution to your problem are -sadly- dataflows. (Or - gasp- datamarts, if that is your thing)  They allow you to run the ETL in a walled-off environment but still be able to create your reports from whereever.

The SQL Express has one database with one table with only 6 columns and is updated daily with about 3K rows (only takes maybe 2 min). Power BI serice is updating once a night through the gateway at a different time so I'm not overly concerned about the performance.

In the end I was able to get an ODBC connection into the Gateway from my local machine so this will work.

Thanks!

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.