A
thought to blog inspired from spending ample of time resolving a very common
error in SQL Server while connecting to a instance i.e. "A network-related
or instance-specific error occurred while establishing connection to SQL
Server. The server was not found or was not accessible …". Below are
the steps to follow to resolve the error.
1. Check Services: "SQL Server"
and "SQL Server Browser" is up and running. We can check this
by going to "All Programs à SQL Server à Configuration Tools à SQL Server Configuration
Manager"
OR search "SQL Server Con" in windows search.
2. Check Client Protocols: Now check for the native
client protocols e.g. TCP, Named Pipe etc. is enabled. Most of the times we'll
get this information in error message towards end of it. Enable the provider if
it is not done. Check image below.
3. Check Server Protocols: Check the "SQL Server
Network Configuration" and make sure that respective client protocol
is enabled here as well. Check the image below.
4. Know ports and open them: Now check the properties of
the protocol by right clicking and selecting properties or double clicking on
it. Select "IP Address” tab and go till end of the window till
"IP All" comes as in image below. Check for the port where SQL
server is listening in this case it is 3680 (not 1433 which is default) and it
is dynamic. We can specify static as well in "TCP Port"
section. Now we need to open the same port in this case it is 3680 in the local
machine by creating inbound rule (or exception) in "Windows Firewall".
We may also need to open the port for "SQL Server Browser Service"
as well.
5. Check Remote Connection: Make sure that SQL Server is
enabled for remote connection by running below T-SQL command or from the below
screen by “Right Click on the Server from
SSMS à Select Connections”.
SP_CONFIGURE 'remote access', 1
RECONFIGURE WITH OVERRIDE
GO
|
6. Force Protocol and Port: While connecting from SSMS we
can also provide port and protocol to connect.
Protocol can also be forced by
prefixing protocol name e.g. "tcp:ServerName, PortNumber" TCP/IP or
"ip:ServerName, PortNumber" for named pipe etc. Same can be achieved
from "Options >>" option as well.
