I have an app I developed that uses a sql server.

Rick DeRousse 0

I installed the SQL server on Windows Pro 10. I can access the app from my pc but get the 'can't find the SQL server' error when trying to access from another PC. Not firewall, or SQL database config. It is set to allow remote connections. I can connect with my app using SQL authentication or Windows authentication from my PC but no others. Why can the app not find the SQL server but i can connect using PowerShell, etc ? What is am I missing, will this only work if I move the SQL server over to a server and not run it on a Windows Pro 10 PC? Even though Microsoft says it should work on a Windows 10 computer.

Thanks

Rick

Erland Sommarskog 112.7K Reputation points MVP

2023-06-20T21:17:14.3166667+00:00

So how does the connection string in your application look like?
Yuri Moyses 0 Reputation points

2023-06-20T21:35:26.07+00:00

Hello,

Before you check the SQL configurations, you're using default port 1433 ? if yes, in another computer, what is the result for telnet "IP or Hostname of Your SQL Server W10" 1433

If the telnet are ok, your problem is not firewall but remote conection from SQL Server Configuration.

Using SQL Management Studio, go to Server Properties / Connections, check if are enable the "allow remote connections to this server".

In SQL Server Configuration manager, go to SQL Server Network Configuration / Protocols for %instance% / TCP/IP set Enable.

Right-click on TCP/IP again and select properties

Enter the value 1433 in the TCP port Field (IPALL)

After this, restart the SQL Service and test again.
Rick DeRousse 0 Reputation points

2023-06-20T21:53:26.3366667+00:00

I have the configuration manager set to port 1433 for the NIC card. Also have the APALL also set to 1433. Also server is set to 'Allow Remote Access' . When I run this command in Powershell > If (Test-Connection -ComputerName PC-102838 -Count 1 -Quiet) {Try {Invoke-Sqlcmd -ServerInstance MSEXPRESS -Query "SELECT 1"} Catch {Write-Host "Error connecting to SQL Server: $_"}} Else {Write-Host "Unable to reach SQL Server."} ... I get this error >>

Invoke-Sqlcmd : 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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

ConnectionString > ' Private connectionString As String = "Data Source=\.\pipe\MSSQL$SQLEXPRESS\sql\query;Initial Catalog=CR2;Integrated Security=True;Connect Timeout=45;Packet Size=4096" > I can see in the sql server log file that I am connecting correctly with my PC but from another PC there is no log file since it never connects ? Thanks

2 answers

Erland Sommarskog 112.7K Reputation points MVP

2023-06-20T22:03:50.7666667+00:00

You should not have the named-pipes path in the connection string. If the instance name is SQLEXPRESS, the parameter for Data Source should be MACHINENAME\SQLEXPRESS. If the language is C#, you need to double the backslash, so that it does not function as an escape character in the string literal.
Please sign in to rate this answer.
Rick DeRousse 0 Reputation points

2023-06-20T22:27:12.73+00:00

Changed the ConnectionString to what you suggested but it is still generating the same error >

Private connectionString As String = "Data Source=725-2-1559-new\SQLEXPRESS;Initial Catalog=CR2;Integrated Security=True;Connect Timeout=45;Packet Size=4096"

Rick DeRousse 0 Reputation points

2023-06-20T22:29:43.4833333+00:00

I have also tried using the SQL client method of connecting to the sql server. > And I can see in the log file that this connects to the sql server properly but still no other client can connect ?

'Private connectionString As String = "Data Source=PC-102838.LACSN.LOCAL\SQLEXPRESS\LACSN\RDeRousse,1433;Initial Catalog=CR2;User ID=Test;Password=test;Connect Timeout=45;Packet Size=4096;TrustServerCertificate=True"

Erland Sommarskog 112.7K Reputation points MVP

2023-06-21T06:35:28.0866667+00:00

I have also tried using the SQL client method of connecting to the sql server. > And I can see in the log file that this connects to the sql server properly but still no other client can connect ?

Sorry, but I don't understand much of this. What do you mean with "the SQL Client method"? Weren't you using SqlClient all the time?

What log file do you refer to, and what does it say? And what is it that connects properly?

You talk about port 1433, but it seems that you have a named instance. Typically a named instance does not run on port 1433; you would need to configure it to run on that port. And to connect by instance name, you need to have the Browser service running and UDP port 1434 open.

Anyway, it seemed from your original post that you were able to connect remotely by other means. So tell us what does work.

Rick DeRousse 0 Reputation points

2023-06-21T16:44:44.76+00:00

Well I turned off > Named Pipes and am now just using sql server authentication. It is still just working from my workstation but none others.Thanks

Rick

Erland Sommarskog 112.7K Reputation points MVP

2023-06-21T21:10:17.67+00:00

I asked a couple of questions to get clarification, but you did not answer. No that you are obliged to, but if you not share enough information we can't help you.

Can you connect with SQL Server Management Studio from another machine that the one that SQL Server runs on?

Can you show a screenshot from SQL Server Configuration Manager of the SQL Server Services page?

And then on the Protocols page, can you share the corresponding to the screenshot below?

Note: I've converted your Answer to a comment. Only use the Answer box if you find out the solution yourself.

Rick DeRousse 0 Reputation points

2023-06-21T21:17:47.1533333+00:00

I also can move the SQL server to another building and my workstation when running the app connects to the SQL server w/o error. But I can't even run the app on top of the SQL server, gets the exact same error, can not find the SQL server.

Erland Sommarskog 112.7K Reputation points MVP

2023-06-21T21:24:31.91+00:00

So can you connect with SSMS from another machine or not?

As for the screenshots, I need to see the bottom part of the first one. And you did not post what you have on the SQL Server pages.

If you don't answer the questions we ask you, it is not very likely that we will be able to help you.

Rick DeRousse 0 Reputation points

2023-06-21T21:41:09.17+00:00

I have the IPALL set to the the default TCP port 1433. And yes I can connect from my PC using SSMS to the computer hosting the SQL server. I can connect using either SQL or Windows Authentication.

Erland Sommarskog 112.7K Reputation points MVP

2023-06-21T21:51:16.0433333+00:00

Good. Then you specify in Data Source exactly the same thing as you use when you connect in SSMS. Beware that if you are using C# that you need to double the backslash or put a @ before the string literal. (But the example looked more like Visual Basic, and in that case you don't need this.)

If this does not work, please share the error message.

Rick DeRousse 0 Reputation points

2023-06-21T22:12:48.54+00:00

It is in VB. So my app can run only from my workstation? I can turn the firewall off on the sql server > no difference. I get the same error when I run the app on the computer that is hosting the SQL server? Can't locate the SQL server or instance error 26. ?

Erland Sommarskog 112.7K Reputation points MVP

2023-06-21T22:16:56.8833333+00:00

No, if you can connect with SSMS remotely, you can connect with the app. But you need to have the connection string right. Just like you need to specify the server\instance name when you connect with SSMS.

Rick DeRousse 0 Reputation points

2023-06-21T22:22:36.0233333+00:00

The current connection string I am using >

Private connectionString As String = "Data Source=192.168.107.127\SQLEXPRESS;Initial Catalog=CR2;User ID=RickD;Password=*****;Connect Timeout=45;Packet Size=4096;TrustServerCertificate=True"

Erland Sommarskog 112.7K Reputation points MVP

2023-06-22T06:41:17.3366667+00:00

And if you can connect to 192.168.107.127\SQLEXPRESS through SSMS from the same machine that should work.

But... Are you in a corporate environment? It sounds a bit like that since you talked about different buildings. It may be that the firewall your company is using also requires applications to be whitelist to get out of the firewall. Maybe you should consult your IT folks.

Rick DeRousse 0 Reputation points

2023-06-22T14:19:17.3033333+00:00

I can connect with these creds with SSMS but my connection string will only work from my computer ? And I can connect with SSMS using either SQL or Windows authentication. And if I change the connection string in my code I can make it fail so I know it is working?

Private connectionString As String = "Data Source=192.168.107.127\SQLEXPRESS;Initial Catalog=CR2;User ID=RickD;Password=*****;Packet Size=4096;TrustServerCertificate=True"

Rick DeRousse 0 Reputation points

2023-06-22T16:08:26.03+00:00

yes, but i have disabled the local firewall endpoint for testing purposes, also checked the firewall rules and am passing all ports from any IP address, VPN connection between buildings. what I dont understand is that i get the same error if i run the app on the sql server? no firewall, no routing, should loopback. So i can run my app from my pc whether the sql server is in my building or a remote building and it connects to the sql server w/o error. But it can not find the sql server if the app is ran on top of the sql server ?

Rick DeRousse 0 Reputation points

2023-06-22T16:57:36.4033333+00:00

I am the IT admin, wear many hats ;)

Rick DeRousse 0 Reputation points

2023-06-22T20:45:07.4366667+00:00

Well since this seems to be such a pain in the ass to get this configured to work properly using either SQL's Windows or SQL authentication I think I'm going to try to configure it to connect using ODBC. I think maybe why this is not working even though I have everything setup correctly is because I installed the SQL on a Windows computer instead of a Microsoft server. It's just not a large DB so I didn't think it would require a server. I know my connection string is good because I can change say the target DB and it will fail from my PC.

Rick DeRousse 0 Reputation points

2023-06-22T20:49:47.9533333+00:00

Also thanks for responding to my questions, It's hard to find anyone knowledgeable of this subject. I'm the only admin here so I'm doing both network administration and some programming to challenge myself.

Erland Sommarskog 112.7K Reputation points MVP

2023-06-22T20:57:49.3766667+00:00

ODBC or .NET, there is no difference (except that keywords may be different).

It seems that in the screenshot you connect with IP-address only. Then try that in the application. Since you say that you've pegged the port to 1433, you don't need the instance name.

But if you use the instance name, you need to make sure that the SQL Server Browser Service is running and that port UDP 1434 is open in the firewall.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
AniyaTang-MSFT 12,446 Reputation points Microsoft Vendor

2023-06-21T06:51:16.2766667+00:00

Hi @Rick DeRousse

Here is a document for a network-related or instance-specific error while establishing a connection to SQL Server: https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/connect/network-related-or-instance-specific-error-occurred-while-establishing-connection. You can refer to these steps to check.

Best regards,

Aniya
Please sign in to rate this answer.
Rick DeRousse 0 Reputation points

2023-06-21T22:14:58.4733333+00:00

I went through the checklist and everything is configured correctly, but still get the same error 26 > can't locate the SQL server or SQL instance. I have checked the DNS and it is working properly.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

I have an app I developed that uses a sql server.

2 answers

Your answer