cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
samuelr2019 Frequent Visitor
Frequent Visitor

Connect to remote SQL server

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Connect to remote SQL server

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.

View solution in original post

3 REPLIES 3
Community Support Team
Community Support Team

Re: Connect to remote SQL server

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.

View solution in original post

samuelr2019 Frequent Visitor
Frequent Visitor

Re: Connect to remote SQL server

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

 

 

 

satrujeet New Member
New Member

Re: Connect to remote SQL server

 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.

 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)