cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
samuelr2019
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
Community Support

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
Community Support

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

Highlighted
samuelr2019
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

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 Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors