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

Question

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.

Answer 1

I had a similar problem which was solved by going to the "SQL Server Configuration Manager" and making sure that the "SQL Server Browser" was configured to start automatically and was started.

I came across this solution in the answer of this forum post: http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/8cdc71eb-6929-4ae8-a5a8-c1f461bd61b4/

I hope this helps somebody out there.

Answer 2

press windows+R open RUN Window

services.msc 

find SQL Server(SQLEXPRESS) right click on that and start the service then check

Answer 3

Go to SQL Server Configuration Manager > SQL Server Services > In Right Side window show all the servers which may stop. If you are using "SQLEXPRESS" then , Right click on SQL Server(SQLEXPRESS) and start. After try to connect server... I had same problem but I resolved by these steps.

Answer 4

I recently encountered the same issue however I am using Windows 10 Technical Preview Build 10041 and SQL Server 2014 (Advanced).

Follow the same advice from above:

In particular, my problem was that I did not enable the TCP/IP in Sql Server Configuration Manager->SQL Server Network Configuration->Protocols for SQLEXPRESS.

Once you open it, you have to go to the IP Addresses tab and for me, changing IPAll to TCP port 1433 and deleting the TCP Dynamic Ports value worked.

Follow the other steps to make sure 1433 is listening (Use netstat -an to make sure 0.0.0.0:1433 is LISTENING.), and that you can telnet to the port from the client machine.

Finally, I second the suggestion to remove the \SQLEXPRESS from the connection.

AND ----> That last line is important! It looks like to be Windows 10 specific; remove \SQLEXPRESS from your connection string. What was frusting was that SQL Management Studio connects just fine using either connection string (full or short), however Visual Studio only accepted the connection string without the \SQLEXPRESS.

Answer 5

Here is a simple method to troubleshoot connection issues:

1. Create an empty file called test.udl using a text editor
2. Double click the file test.udl, then specify your connection properties
3. Hit the "Test Connection" button.

Answer 6

The active firewall on the server might be causing this. You can try to (temporarily) turn it off and see if it resolves the issue.

If it is indeed caused by the firewall, you should allegedly be able to resolve it by adding an inbound rule for TCP port 1433 set to allowed, but I personally haven't been able to connect this way.

Answer 7

It's security all about. Make sure you have double check your firewall (windows and anti virus) in some cases when you disabled av firewall and restart your computer, automatically windows firewall is active and it's still block your application. Hope this is helpful ..

Answer 8

I had a Same Problem but i solved by Restarting the browser service in SQL CONFIGURATION MANAGER and removing \SQLEXPRESS from the instance name in the connection window.

Answer 9

Could you post the connection string you're using that's giving you trouble?
You might need to add the port number to the Data Source value, as omitting it can also produce SQL Error 26.

E.g.: Data Source=ServerHostName\SQLServerInstanceName,1433

Answer 10

Have you Disabled the VIA setting in the SQL configuration manager? If not, do disable it first (if VIA is enabled, you cannot get connected) and yes TCP must be enabled. Give it a try and it should be working fine.

Make the changes only for that's particular instance name.

Cheers!

Answer 11

I found this url to be very useful: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/2cdcab2e-ea49-4fd5-b2b8-13824ab4619b/help-server-not-listening-on-1433

In particular, my problem was that I did not enable the TCP/IP in Sql Server Configuration Manager->SQL Server Network Configuration->Protocols for SQLEXPRESS.

Once you open it, you have to go to the IP Addresses tab and for me, changing IPAll to TCP port 1433 and deleting the TCP Dynamic Ports value worked.

Follow the other steps to make sure 1433 is listening (Use netstat -an to make sure 0.0.0.0:1433 is LISTENING.), and that you can telnet to the port from the client machine.

Finally, I second the suggestion to remove the \SQLEXPRESS from the connection.

EDIT: I should note I am using SQL Server 2014 Express.

Answer 12

This really Works .. i had verified lot of sites and finally got the answer

This may occurs when the master.mdf or the mastlog.ldf gets corrupt . In order to solve the issue goto the following path

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL , there you will find a folder ” Template Data ” , copy the master.mdf and mastlog.ldf and replace it in

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA folder .

Thats it . Now start the MS SQL service and you are done .

Answer 13

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).

Answer 14

In my case, I Installed SQL Express 2012 and the problem raise after I reboot my PC. I solved so:

I go to Services -> SQL Server (SQLEXPRESS) and I see that was stopped. I started and It's say an login error. In properties, Tab Log On, I change it to Local System account , then I can start the SQL service.

Then to test it, I go to SQL Server Management Studio and I connect to .\SQLEXPRESS and it works!

Answer 15

I had a similar problem which was solved by going to the "SQL Server Configuration Manager" and making sure that the "SQL Server Browser" was configured to start automatically and was started. it works for me

Answer 16

I got a similar problem with sql server , I have tried every thing but does not connect to database engine & it shows error:26.

• First check if the database engine is running or not. by going into configuration manager. start > sql server >sql server configuration manager. On the right pane you should see the sql server (mss .. ) should be running state with a green indication.

• IF the database engine is not running, simply uninstall sql server / format your system if possible and then download sql server 2012 and management studio. from
  https://www.microsoft.com/en-ca/download/details.aspx?id=29062

• Install server first, make sure to add server on installation phase
  by clicking add server and then install management studio.

Answer 17

All you need to do is to go to the control panel > Computer Management > Services and manually start the SQL express or SQL server. It worked for me.

Good luck.