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

PowerApps can't connect to my SQL Server Express


Hello,

 

I have been trying for several hours to create a PowerApps connection to my SQL Server Express. This SQL Server is installed on an OVH server, connected directly to the internet. I can connect with Microsoft SQL Server Management Studio from my laptop, but when I try to create a connection with PowerApps I always get the error :

"The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified"

 

I can't create a gateway because I'm in office 365 business premium .

 

I think that the connection parameters I am providing are correct because if I change only one of the parameters (server name\instance, database name, username or password), the error message is different and come faster ("Credentials are missing or not valid") and because i have no problem to connect with Microsoft SQL Server Management Studio from my laptop.

 

Is it really possible to connect to a SQL server that you install yourself? Or do I have to take a paid SQL Server Azur?

Anyone can help me?
Thank you

 

Quentin

6 REPLIES 6
Community Support Team
Community Support Team

Re: PowerApps can't connect to my SQL Server Express

Hi @Quentin3 ,

Please check your firewall.

Make sure  "sqlbrowser.exe and/or UDP port 1434 are under firewall exception."

Further , for more details about the connecting error, please check the blog first:

https://blogs.msdn.microsoft.com/sql_protocols/2007/05/13/sql-network-interfaces-error-26-error-loca...

What's more, please refer to these docs about this kind of error:

https://stackoverflow.com/questions/38694867/azure-error-a-network-related-or-instance-specific-erro...

https://guyinacube.com/2016/09/21/troubleshooting-connectivity-tips/

 

 

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Quentin3
Level: Powered On

Re: PowerApps can't connect to my SQL Server Express

thanx @v-yutliu-msft .

 

I read all the content of the linked pages. I had already tried just about everything that was proposed.

  • sqlserver.exe and TCP port 1433, 135, 4022 are under firewall exception + dynamic port 61218.
  • sqlbrowser.exe and UDP + TCP port 1434 are under firewall exception.
  • sqlbrowser service run
  • sqlServer service run
  • Protocol TCP/IP in SQL server enabled for the all IP adress with TCP port 1433 (except dynamic port 61218)
  • database "ContactTest" created with all permissions to user "Quentin" with a table with a primary key

 

Often I disable the firewall for a few minutes to do my tests. But I still have the same mistake.


Here are the parameters I give in powerApps. Am I making a mistake?

Connect directly (Cloud service) : yes

Server Name :

112.145.25.132\SQLEXPRESS   note that I put a fake server IP address

 I also tried with "tcp:112.145.25.132\SQLEXPRESS" and "np:112.145.25.132\SQLEXPRESS"

Database:

ContactTest

User:

Quentin

Password:

oooooooooo

 

If I change the name of the database for a database that does not exist, if I change the username or password, I find an error in SQL Server ERRORlogs. So my connection work fine, no? If I put the right parameters, I have no error in ERRORlogs, but I have always the same error in PowerApps

 

{
  "error": {
    "code": 502,
    "source": "europe-002.azure-apim.net",
    "clientRequestId": "e5222285-2273-4e6c-8b20-d62e421991b8",
    "message": "BadGateway",
    "innerError": {
      "status": 502,
      "message": "Microsoft SQL : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)\r\nclientRequestId: e5222285-2273-4e6c-8b20-d62e421991b8",
      "source": "sql-ne.azconn-ne.p.azurewebsites.net"
    }
  }
}

My server is a VPS (Virtual Private Server). Is that the problem? Or the bug is fixed for a long time because it is an old problem ? :

http://blogs.msdn.com/sql_protocols/archive/2006/02/27/unable-to-connect-to-a-sql-server-named-insta...

 

thank you for your help

 

Quentin

 

 

 

Quentin3
Level: Powered On

Re: PowerApps can't connect to my SQL Server Express

Thanx @v-yutliu-msft .

 

I read all the content of the linked pages. I had already tried just about everything that was proposed.

  • sqlserver.exe and TCP port 1433, 135, 4022 are under firewall exception + dynamic port 61218.
  • sqlbrowser.exe and UDP + TCP port 1434 are under firewall exception.
  • sqlbrowser service run
  • sqlServer service run
  • Protocol TCP/IP in SQL server enabled for the all IP adress with TCP port 1433 (except dynamic port 61218)
  • database "ContactTest" created with all permissions to user "Quentin" with a table with a primary key


Often I disable the firewall for a few minutes to do my tests. But I still have the same mistake.


Here are the parameters I give in powerApps. Am I making a mistake?

Connect directly (Cloud service) : yes

Server Name :

112.145.25.132\SQLEXPRESS   note that I put a fake server IP address

I also tried "tcp:112.145.25.132\SQLEXPRESS" and "np:112.145.25.132\SQLEXPRESS"

Database:

ContactTest

User:

Quentin

Password:

oooooooooo

 

If I change the name of the database for a database that does not exist, if I change the username or password, I find an error in SQL Server ERRORlogs. So my connection work fine, no? If I put the right parameters, I have no error in ERRORlogs, but I have always the same error in PowerApps

 

{
"error": {
"code": 502,
"source": "europe-002.azure-apim.net",
"clientRequestId": "e5222285-2273-4e6c-8b20-d62e421991b8",
"message": "BadGateway",
"innerError": {
"status": 502,
"message": "Microsoft SQL : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)\r\nclientRequestId: e5222285-2273-4e6c-8b20-d62e421991b8",
"source": "sql-ne.azconn-ne.p.azurewebsites.net"
}
}
}


My server is a VPS (Virtual Private Server). Is that the problem? Or the bug is fixed for a long time because it is an old problem ? :

http://blogs.msdn.com/sql_protocols/archive/2006/02/27/unable-to-connect-to-a-sql-server-named-insta...

 

thank you for your help

 

Quentin

 

Quentin3
Level: Powered On

Re: PowerApps can't connect to my SQL Server Express

Hi @v-yutliu-msft ,

 

Does a PowerApps -> SQL Server connection require encrypted connection (with a certificate in SQL Server)?

 

Because I test my SQL Server login from Excel installed on my PC. I add a SQL Server connection in Excel to 112.145.25.132\SQLEXPRESS (fake IP address)

Excel can connect to my SQL Server, I can see the tables. But Excel tells me that the connection can not be encrypted, that I have to use an unencrypted connection. Indeed, I have not set SQL Server with a certificate. Is it necessary for powerApps?

 

Thanks

 

Quentin

Quentin3
Level: Powered On

Re: PowerApps can't connect to my SQL Server Express

Hi,

 

Microsoft technical support tells me that an Office 365 Business Premium license can not connect to an SQL server. Is it correct?
They tell me that I need an E1 or E2 license to connect to a SQL server.
If I need an On premise gateway, OK... I need an E1 or E2 license.

But if I connect to a SQL server directly connected to the internet, an Office 365 Business Premium license is sufficient, right?

 

Thanks for any help.

 

Quentin

 

Community Support Team
Community Support Team

Re: PowerApps can't connect to my SQL Server Express

Hi @Quentin3 ,

I'm sorry that I did not consider the license problem.

Yes, different license about using SQL is different.

1)If you want to connect with Azure SQL, Office 365 Business Premium license is enough.

2)If you want to connect with on-promises SQL(must use gateway), only PowerApps P1 or P2 can.

 

Here's a doc about PowerApps license for your reference:

https://docs.microsoft.com/en-us/power-platform/admin/pricing-billing-skus

Please pay attention to the function of "Access on-premises data or use premium or custom connectors".

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
New Ranks and Rank Icons in April

'New Ranks and Rank Icons in April

Read the announcement for more information!

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.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Community Summit North America

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

Top Solution Authors
Top Kudoed Authors
Users online (4,367)