Unable to connect to SQL Express "Error: 26-Error Locating Server/Instance Specified)

user_785971 0
Viewed 257487

I am at an loose end with one particular box that is running SQL Server 2008 R2 Express.

  • Windows Firewall is configured to allow inbound TCP & UDP 1433, 1434.
  • Windows Firewall is configured to allow outbound TCP & UDP on any port.
  • No other software AV/FW is currently running.

When I try to connect to xxx.xxx.xxx.xxx\sqlexpress, it times out with the following error:

TITLE: Connect to Server

Cannot connect to xxx.xxx.xxx.xxx\SQLEXPRESS.

ADDITIONAL INFORMATION:

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) (Microsoft SQL Server, Error: -1)

I have checked that remote connections are allowed on the server.

I have confirmed TCP/IP is enabled and configured in SQL Server Configuration to allow connections on the external IP to 1433.

I have also used Wireshark from my local machine and I can see requests for 1433 UDP going to the remote server.

But using Wireshark on the remote server shows no inbound connection requests for 1433 UDP or anything from my external IP (with filtering to remote RDP from results).

This makes me think it is a firewall issue.

The server is hosted by an external company who have control over the PIX in front of our server.

I need to troubleshoot this as much as possible myself as the hosting company have no clue what they are doing.

sql-server-2008r2-express sql-server sql
19 Answers

If you have done anything but in spite of all couldn't connecting?!! In my case i have changed connection part splitting port "," instead of this ":"

The corresponding TCP port or pipe name is not specified in the connection string (such as Srv1\SQL2008, 1433).

user_9142840

This issue also confused me a few days, after the IT guy changed some security settings on the SQL Server.

I have an EntityFramework for the Web application and for a desktop application.

After I changed some settings on the SQL Server, the Web application comeback to work, but the desktop still facing issue. But I used the same connection string for both applications, it make no sense one is working but the other doesn't.

Then I searched a lot until I found someone saying here it is needed to add port number 1433 after the $ServerName\$DatabaseInstanceName,1433.

After I added it, the exception became:

System.Data.SqlClient.SqlException: Login failed for user 'domain\name-PC$'

Then I found this link. It says to add Trusted_Connection=False;.

The whole connection string should be like:

data source=XXXXX\SQLSERVER,1433;initial catalog=XXXDB;user id=UserID;password=PWD;Trusted_Connection=False;MultipleActiveResultSets=True;

Hope this answer will help the ones out off Generic exception:

Error: 26-Error Locating Server/Instance Specified

user_119974060

While there can be many reasons for this error - In my case it was pretty simple which I overlooked for a while. I was using server IP address along with instance name for Server as -

<add name="MyDBConnection" 
connectionString="Persist Security Info=False;User ID=sa;Password=xxx;Database=MyDB;Server=nn.nn.n.nn\SQLInstance01; Connection Timeout=180" providerName="System.Data.SqlClient"/>

Correct connection string will look something like this (note this one doesn't have SQL Server instance name)

<add name="MyDBConnection" 
connectionString="Persist Security Info=False;User ID=sa;Password=xxx;Database=MyDB;Server=nn.nn.n.nn; Connection Timeout=180" providerName="System.Data.SqlClient"/>

HTH.

user_1862800
Related