Check into the steps to resolve this error.
1) SQL Server should be up and running.
Go to All Programs >> Microsoft
SQL Server 2008 >> Configuration Tools >> SQL Server
Configuration Manager >> SQL Server Services, and check if SQL
Server service status is “Running”.
In addition, ensure that your remote
server is in the same network. Run “sqlcmd -L” in your command prompt to
ascertain if your server is included in your network list.
I have confronted numerous situations
when these nerve-wracking errors crop up, and most of the time when I
try to troubleshoot I notice that SQL Server services are neither
running nor installed. If SQL Server is not installed as default
instance SQL Server Browser should be running together with it; we will
explore this further in Topic 5.
2) Enable TCP/IP in SQL Server Configuration
When two or more SQL Servers are
connected across network they do all communication using TCP/IP. The
default port of SQL Server installation is 1433. This port can be
changed through SQL Server Configuration Manager. TCP/IP should be
enabled for SQL Server to be connected.
Go to All Programs >> Microsoft
SQL Server 2008 >> Configuration Tools >> SQL Server
Configuration Manager >> Select TCP/IP
Right Click on TCP/IP >> Click on Enable
You must restart SQL Server Services for
all the changes to take effect. Right click and go to menu properties
to select location where default port of SQL Server can be changed.
3) Open Port in Windows Firewall
Windows Firewall is
very efficacious in protecting the Operating system from all sorts of
malicious attacks. By default, many of the ports and services are
refrained from running by firewall. Time and again, SQL Server ports are
not open in firewall as well. All the ports on which SQL Server is
running should be added to exception and firewall should filter all the
traffic from those ports. As described, by default SQL Server runs on
port 1433, but if default port is changed then the new port should be
added to exception. If SQL Server has named instance (another instance
besides default instance) is installed, SQL Server browser should also
be added to the exception, as described in Step 7.
Go to Control Panel >> Windows Firewall >> Change Settings >> Exceptions >> Add Port
Make the following entries in popup “Add a Port” and click OK.
Name : SQL
Port Number: 1433
Protocol: Select TCP
4) Enable Remote Connection
Enabling remote connection is another
important, yet oft-neglected step that is frequently missed by database
administrators while setting up SQL Server. If this feature is turned
off SQL Server will function smoothly on local machine, but it will let
another server connect to it remotely. By default this feature is ON in
SQL Server 2008.
Right click on the server node and select Properties.
Go to Left Tab of Connections and check “Allow remote connections to this server”
5) Enable SQL Server Browser Service
If SQL Server is not installed as
default instance but instead installed as named instance and also if
there is no specific TCP/IP port configured, it will give rise to the
error that is being discussed in this article. If SQL Server Browser
service is enabled, it will allow the server to be connected through
dynamic TCP/IP port. Enabling this service is a one-time process, as on
enabling it once it will apply to all the instances installed on the
same server.
Go to All Programs >> Microsoft
SQL Server 2008 >> Configuration Tools >> SQL Server
Configuration Manager >> SQL Server Browser
Right Click on SQL Server Browser >> Click on Enable
6) Create exception of sqlbrowser.exe in Firewall
As elucidated in Step 6, sqlbrowser
service needs to be enabled for named instance. Windows Firewall may
prevent sqlbrowser.exe to execute. So, it is imperative to add exception
for the same in windows firewall.
Search for sqlbrowser.exe on your local
drive where SQL Server is installed. Copy the path of the sqlbrowser.exe
like C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe and
create the exception of the file in Firewall, as delineated in Step 3.
7) Recreate Alias
It is getting quite common to create
alias of SQL Server and use it in application. This will ensure that in
future if any physical SQL Server has to be moved, it will not be
required to change any code or connection string. You can simply create
alias with the same name pointing to different SQL Server and it will
start working instantaneously. I have observed that a couple of times
due to internal error while recreating alias this error was fixed.
Go to All Programs >> Microsoft
SQL Server 2008 >> Configuration Tools >> SQL Native Client
10.0 Configuration >> Aliases
Delete the alias that is giving problem and recreate it with identical parameters.
I have tried my best to include all the
methods of fixing this error and if I have missed any, please leave a
comment and I will be very glad to include them here. I have put in my
effort to encompass this issue in one article that needs to be refereed
when any connection error comes up.