Rookie here. Need help connect to SQL on a VPS

[geoffd]

I've been using Report Builder 3.0 and SSRS for years. I just started using Power BI and was able to make a few reports using Power BI Desktop, the SQL data on my workstation and a Gateway connection. That went well.

However, we just finished setting up a new VPS (Windows Server 2019, SQL 2019) but the SQL on it is locked down and the only way to RDP to this server or view SQL data is via VPN first.

What I don't understand is how to refresh data in Power BI online, using my server's SQL, after I publish it and subscribe. Do people install Gateways on servers? I can't seem to find any detail on what to do in my scenario. My SQL is not visible via public IP, I need to use VPN to access it through SQL Management Studio

Any insight would be most appreciated.

[NamishB]

Hi @geoffd - So just to reiterate what you are saying is-

- You have a Virtual Private Server 2019 with SQL Server 2019 installed

- From Power BI Service you trying to refresh the data stored in SQL server on your Virtual Server

- And its not allowing you to do so.

If this is the case, first can you check in Service account: Under Datasets > Status. Does it gives you error saying not configured correctly? it will first gives some recommendations to fix it, mostly "Add to Gateway" option. Try to follow the steps listed on MS Docs:

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-sql-tutorial

Yes Gateways can be installed on Virtual Servers as well as their primary function is to allowing/Denying access from other applications to your server.

Let me know how you go, it will be a good learning for me as well.

Cheers,

-Namish B  

[geoffd]

I just confirmed my issue but installing Power BI Desktop directly on the server and creating a report from there. It picked up the data source without issue and was able to refresh.

So my real issue is that I can only access my server's SQL via VPN and local IP. Unfortunately, I don't know enough about network administration to solve this.

[v-easonf-msft]

Hi, @geoffd 

Are you currently having difficulty adding data sources to the gateway?

Have you check your server name and datasource credentials? service-gateway-enterprise-manage-sql 

If you are are a pro user ,you can create a  support ticket to  submit your issue  to powerbi support team for better suggestions.

https://powerbi.microsoft.com/en-us/support/

Best Regards,
Community Support Team _ Eason

[geoffd]

Yes, I do have a "not configured correctly" message. The Gateway is "connected" but I have yet to be able to use it.

Could the issue be that on my workstation I can only access the SQL database over VPN by local IP address (10.20...)? If I look at network I can only see my own SQL, not the Server's.