cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jonty
Level: Powered On

Connect to on premise SQL server

I am trying to connect to an on premise SQL server and cannot seem to return any data. I think this is primarily down to me but need some clarification. What I have done:
- installed the data gateway coincidentally on the machine that has the SQL server I am trying to connect to
- configured the gateway and have it appearing in my power apps gateway list

When I create a new connection I am presented with entries for serve name, database name etc. What exactly should I put in here? Is the server name machinename\instancename or is it just machinename or just instancename. What do I type in the database name? Instancename\databasename or something else. I am also using the sa password for now to check connectivity, and select basic authentication. Any help appreciated, can't seem to find anything that tells me how this works.
1 ACCEPTED SOLUTION

Accepted Solutions
Jonty
Level: Powered On

Re: Connect to on premise SQL server

Not to worry, figured the problem out. I have dual network cards and the database was only set to communicate on one of them. And in answer to the question:

 

Server name: servername\instance

database: databasename

 

 

View solution in original post

8 REPLIES 8
Microsoft dimazaid
Microsoft

Re: Connect to on premise SQL server

hi @Jonty,

What do you mean by instance name in this context? what have you tried and didn't work?

The server name is the machine name where you have the database running in. 

 

What you could do to verify the connection, is try connecting via SQL server management studio: https://msdn.microsoft.com/en-us/library/mt238290.aspx to ensure you have the right connection info and see all information about your SQL server and database.


 

Jonty
Level: Powered On

Re: Connect to on premise SQL server

I mean the instance of SQL that the database resides in. I have tried pretty much every combination I can think of. I can connect via management studio fine, with the connection details of servername\instancename along with the SQL authentication credentials. This loads the databases in the instance accordingly.
Microsoft dimazaid
Microsoft

Re: Connect to on premise SQL server

Have you tried "servername\instancename" for the server name, and database name in the database name section? And choosing the basic authentication (not windows) if you're doing SQL auth
What error is it returning? or is it connecting but not showing any data? 
Do you see any errors in the gateway logs? Here's some info on how to get these logs: https://powerapps.microsoft.com/en-us/tutorials/gateway-reference/#troubleshooting-1

 

Jonty
Level: Powered On

Re: Connect to on premise SQL server

I tried the ip of the server rather than localhost\instancename. The error I get is the generic a "network related instance not found" etc. I am thinking the issue is more my lack of understanding of how the data gateway works. My assumption is that it gives essentially a proxy that prevents having to open up SQL ports and also encrypts the data. This is then passed through the gateway to the connector. And because you can specify the server, I am guessing it can access anything on that subnet that is normally accessible from management studio.

It's a frustrating problem because it's hard to troubleshoot without knowing how the connection details should be correctly formed.
Jonty
Level: Powered On

Re: Connect to on premise SQL server

And just to complete my answer I have tried the following:
Server: serverIP\instancename
Database:databasename

Server: serverIP
Database:instancename\databasename

I am now wondering having looked at the gateway documentation whether the fact it runs as a local service is preventing it from accessing data on the network. Perhaps changing th service it runs under to networkservice will make it work?
Jonty
Level: Powered On

Re: Connect to on premise SQL server

Not to worry, figured the problem out. I have dual network cards and the database was only set to communicate on one of them. And in answer to the question:

 

Server name: servername\instance

database: databasename

 

 

View solution in original post

Microsoft dimazaid
Microsoft

Re: Connect to on premise SQL server

Glad you got it figured out! If possible, please mark this post as solved/closed.

GonzoDesperado
Level: Power Up

Re: Connect to on premise SQL server

Thanks Jonty, that did the trick....

Helpful resources

Announcements
thirdimage

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors
Users online (6,719)