PowerApps can't connect to my SQL Server Express

[Quentin3]

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

[v-yutliu-msft]

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/

[Quentin3]

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]

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]

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]

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

[v-yutliu-msft]

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,