A network-related or instance-specific error occurred…

Possibly one of the most common issues I see out there is the following error (yet always very informative):

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.

                Yes, we’ve all seen it before.  And it is an error that likes to rear its ugly head even to the most seasoned Database Administrator or Developer.  Nobody is immune to this problem, and with the correct troubleshooting you will be connected to that instance in no time.  The error text is actually one of the more descriptive errors that I see in SQL Server (in my opinion at least), as it gives an inkling as to what could be the problem, but there is so much more to it than those three sentences.

                So without further ado, here is a great walk-through for things to check when you receive the aforementioned error:

(This list is ordered from the most common causes and easiest solutions, followed by less likely ones)

What kind of instance is this?

                Yes, that is possibly one of the most important questions.  Everyday DBAs and Developers are trying to connect to an instance by only specifying the server name (or IP address) for the Data Source portion of the connection string, or directly through SQL Server Management Studio (SSMS).

Just a quick recap on a very basic aspect of SQL Server instances:  There are two types of instances (Default and Named Instance), and you are trying to connect to one or the other.

Default Instance – as the name suggests, it is the default instance for that server and is referenced solely by the server’s name or IP address.  A few other points about the Default Instance is that it is by default hitting port TCP 1433 (although this can be changed), and the instance name is MSSQLSERVER.

Example:  Data Source = MyServerName

Named Instance – whereas the Default Instance is just that, with a default name of MSSQLSERVER, a Named Instance is a SQL Server instance that has…yes…a name.  When trying to connect to a Named Instance, the SQL Server Browser service (running on port UDP 1434) supplies information on connecting to Named Instances.  Note, though, that a Named Instance is referenced by the server name followed by the instance name.

Example:  Data Source = MyServerName\MyInstanceName

                If you are attempting to connect to a Named Instance, only specifying the server name could result in the titled error.

Is SQL Server Browser started? (Named Instances only)

            Connecting to a Named Instance requires going through a SQL Server service: the SQL Server Browser service to be exact.  This service, as mentioned above, runs on port 1434.  If this service is not started, you simply won’t be able to connect to a Named Instance.

                You can check this by going to the SQL Server Configuration Manager (SSCM) and under SQL Server Services ensure that the SQL Server Browser service is started (also, typically set this service to automatically start for Named Instance access).

Is TCP/IP enabled?

                So now you’re on the server, and you open up SQL Server Management Studio.  You can connect to SQL Server no problem.  But for some reason, you can’t connect to SQL Server from any other machine.  What gives?  Most likely you need to enable the TCP/IP Protocol for the instance that you are trying to connect to.  SSMS connects through the Shared Memory Protocol if it is local to the instance, which would give reason for the initial findings.

                This is a big one after you install SQL Server Express edition.  By default, TCP/IP is disabled causing remote connection attempts to fail.

Is your firewall blocking the necessary ports?

            As written above, the Default Instance is connected through port TCP 1433, and the SQL Server Browser is connected through UDP 1434 and sends information about Named Instances and their corresponding ports.

                An application I like to test for port access is called PortQry.  You can use PortQry to the information of the necessary ports in order to connect to your SQL Server instance.

                As shown above, there are a few things to check out during the troubleshooting of a very popular error when trying to connect to SQL Server.  By going down through this checklist, you will be able to narrow down and correct this problem in no time.  For any questions, comments, or any other correspondence, feel free to leave a comment or email sqlsalt@gmail.com.