Recently, I setup a Windows 2003 Server with SQL Server 2005 Express Edition, along with ISA Server. When I tried to connect to the SQL Server from a remote machine, I was presented with the following error:
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: TCP Provider, error: 0 – A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)
Whew! That’s a pretty long error and really doesn’t tell you much, even with all that blabber.
First thing first, you should make sure that you have remote connections enabled in SQL Server so that remote connections are even allowed. If you’re good there, then keep on reading.
In order to connect properly to an SQL Server remotely, you have to take several different items into consideration.
1. ISA Server – If you have ISA server installed, you need to first make sure that you allow port 1434 – UDP and the MSSQL port number in ISA. If you don’t, the connections will be blocked.
2. MSSQL Port – This is the port number that is set in the properties of MSSQL. Whatever the port number is here, you have to add it to ISA server.
3. SQL Browser Service – You need to make sure that the SQL browser service is also running in order to remotely connect.
Basically, when you install the Express edition of SQL Server, it is installed as a named instance, rather than as a default instance.
Because it is a named instance, by default, it will use dynamic port assignment when it is started. So in essence, your SQL Server will be listening on some random port that was assigned to it when it started.
That’s where the SQL Browser service comes in. This is the service that tells the client, which is trying to connect to the server, what port to use. If the service is not up and running, you will not be able to connect to the named instance.
So you have two choices to solve this problem:
1. Enable the SQL Browser service so that clients can be told which port they need to connect to for that instance or…
2. If you set a port number for that instance, you need to provide that port number in the connection string.
If you are still having difficulties connecting or your setup is different, post a comment here and we’ll try to help! Enjoy!