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

Can't connect to SQL database through Gateway

Hi all,

 

Today I set up a new dekstop and installed SQL Server 2017 Express, and SQL Server management studio.

 

I set up a gateway on the same desktop.

 

Now I had a lot of trouble remotely connecting to the server using SQL Server Management Studio. But eventually by opening SQL ports I could handle a tcp connection.

 

However, I still can't connect using my Microsoft Gateway! That connectes using HTTPS://

 

Is there something I need to configure to allow my SQL Server instance to allow these HTTPS connections?

 

The gateways diagnostic service passes all tests.

 

Any help much appreciated.

 

 

6 REPLIES 6
Super User III
Super User III

Hi @DevPower 

Could you confirm how you're connecting to the gateway and what type of authentication you're using? In terms of setting up a connection from PowerApps to an on-premise instance of SQL Server, the 'server name' defines the path from the gateway to the SQL Server. So if both the gateway and SQL Express is installed on the same machine, you can use the syntax localhost\instancename. Unless you named the instance differently, the default instance name will be SQLExpress. 

Your connection should look something like this.

 

image.png

 

Microsoft
Microsoft

Hello @DevPower 

 

When we create an environment, we have the option to choose a location, such as Europe, but we can’t pick West Europe or North Europe.

 

 

 

Gateway Regions:

However, when we create a gateway, we have more granular options to choose from. We can pick West Europe or North Europe: 

 

 

 

Difference:

Even though Power Apps environment locations are not as granular, they are mapped to Azure regions.

If we look at the properties of an Environment created in Europe, we can see that it has a property azureRegionHint which points to the Azure region of West Europe.

 

Could you please check the region of your environment and the region of the gateway

 

Please click and accept as Solution if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a Thumbs Up

Hi @Yeiner, the environment I'm working in is labelled as 'Europe'  - I can't find the 'azureRegionHint' anywhere to get a more granular reading of this.

 

My Powerapps gateway is set to 'UK South' - how can I modify this?

 

Thanks for your help so far

Hello @DevPower During the installation of the on-premises data gateway, you can set the datacenter region used by the gateway. Please try to reinstall the data gateway and select the proper region.

 

https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-data-region 

I have now tried with several regions, and still no luck.

 

I'm totally at a loss with this, I can't possibly think what else I can try. No matter what I do, it is the same error message I get:

 

Capture.PNG

 

 

 

 

 

 

I wish there was further debugging information. The connection test, as I say, works fine.

 

Please does anyone else have any ideas?

Hello, @DevPower Is there a way that you can open a ticket?

Helpful resources

Announcements
News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

Power Apps Community Call

Power Apps Community Call- January

Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST

PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

Top Solution Authors
Top Kudoed Authors
Users online (9,063)