Connect to remote SQL server

[samuelr2019]

Hi,

We are currently using Power BI to interrogate our on-site SQL server (and several Excel sheets) to design and build reports for our business. They are working very well and we are happy with how everything is working.

However, we are considering moving to a hosted CRM platform (which is still SQL based) but it would give us a new browser-based UI which is a lot faster and more efficient.

The CRM has an "open API" but I'm not sure what we'd need in order to set up a connection between Power BI and the new CRM.

My initial thoughts were that Power BI desktop must have the ability to connect to a remote SQL server (as long as have the correct persmissions) but with regard to the Power BI service - would it be possible to install a gateway on-site but point it to said remote SQL server??

It's absolutely paramount that we maintain the connection between our CRM system and Power BI. There is an alternative CRM which promises to be able to integrate seamlessly with Power BI but I'd like to see if it's possible to get the browser version of our current CRM linked to Power BI.

Any help would be appreciated.

Thanks in advance!

[dax · Accepted solution]

Hi samuelr2019,

We could connect to remote SQL server, you need to make sure you  check the “allow remote connections to this server ” in SSMS->database engine->propertries->connections. And you need to make sure you have permission to access this server.

In addition, when you publish to service, you need to make sure your gateway install in the machine which could access to machine where sql server install in(If you're planning to use Windows authentication, make sure you install the gateway on a computer that's a member of the same Active Directory environment as the data sources.). You could refer to Install an on-premises data gateway for details

Best Regards,

Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[samuelr2019]

Hi Zoe,

Thanks for your comment, much appreciated. We've had our gateway running on-site for around 2 years now with no issue but we'll just have to see how open our CRM provider is and whether they are willing to install a gateway on one of their hosted servers.

I'll relay some of this information to our provider and hopefully we can work something out with them.

Best regards, Rob

[johnmelbourne]

Hi Samuel

Did you end up finding a solution to this? I have an external vendor and am trying to connect to their SQL server and would love to know the process. They have installed an on-prem gateway in their domain, and I have it running in the power bi service. Just need to now connect it to Power BI desktop somehow. Just don't understand how it all interacts.

Any assistance greatly appreciated.

John

[satrujeet]

 As per the FAQ from MIcrosoft that can be found here.

Question: Does the gateway have to be installed on the same machine as the data source?
Answer: No, the gateway connects to the data source by using the provided connection information. In this sense, consider the gateway as a client application. The gateway just needs to connect to the specified server.