.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Configure SQL Server 2005 to allow remote connections

Posted By:Santosh       Posted Date: October 21, 2009    Points: 25    Category: DataBase    URL: http://www.dotnetspark.com  

Configure SQL Server 2005 to allow remote connections

When you are trying to connect to an instance of SQL SERVER 2005 from your application or from a remote computer, most of the times you may get an error message. Because by default, SQL SERVER 2005 doesn't allow remote connection so you have to enable it manually. If you try to connect to remote SQL SERVER 2005 without enable remote connection first, you'll see one of these error messages:

     1 .SQL SERVER doesn't allow remote connections.
     2. SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified.
     3. An error has occured while establishing a connection to the server. When connecting
          to SQL SERVER 2005,this failure may be caused by the fact that under the default
          settings SQL SERVER doesn't allow remote connections.(provider:Named Pipes
          Provider,error:40- Could not open connection to SQL SERVER))Server does not exist
          or  access denied.

Steps for Resolution:
     Step-1. Enable remote connections on the instance of SQL SERVER that you want to
                 connect to from a remote computer.
     Step-2. Turn on the SQL SERVER Browser service.
     Step-3. Configure the firewall to allow network traffic that is related to SQL SERVER and
                   to the SQL SERVER Browser service.

Steps To Enable Remote Connection:
If you use SQL SERVER Management studio then follow below steps:
     Step-1: Open SQL SERVER Management studio
                 Click Start -->> Programs -->> Microsoft SQL SERVER 2005 -->>SQL SERVER
                  Management studio
     Step-2: Connect to the remote server.Right click on the server instance.Select properties.
     Step-3: Click Connections.Check to allow remote connections to this server.


If you are using SQL EXPRESS or SQL DEVELOPER edition then follow the below steps:

      Step-1: Click Start -->> Programs -->> Microsoft SQL SERVER 2005 -->>Configuration  
                   Tools -->> SQL SERVER Surface Area Configuration.
      Step-2: Now click Surface Area Configuration for Services and Connections.

      Step-3: Now expand Database Engine, click Remote Connections, click Local and remote
                   connections, Choose a protocol to enable for your environment, and then click
Restart MSSQLSERVER service from

        Start -->> Control Panel -->> Administrative Tools -->> Service -->> Select MSSQL
        Server -->> Restart Service


Enable Sql SERVER Browser service:
One thing noted that this may increase security risk. Microsoft recommend that you use this process only if you really require this process.To enable the SQL SERVER Browser service, follow these steps.

      Step-1: Click Start -->> Programs -->> Microsoft SQL SERVER 2005 -->>Configuration
                   Tools -->> SQL SERVER Surface Area Configuration
       Step-2: Now click Surface Area Configuration for Services and Connection
                    Select SQL SERVER Browser -->> Select Startup type Automatic -->> Apply

Create exception in windows firewall for SQL Server:

If you are running a firewall on the computer that is running SQL SERVER 2005, external connections to SQL SERVER 2005 will be blocked unless SQL SERVER 2005 and the SQL SERVER Browser service can communicate through the firewall. You must create an exception for each instance of SQL SERVER 2005 that you want to accept remote connections and an exception for the SQL SERVER Browser service.

Open Start -->> Control Panel -->> Windows Firewall
Select Exceptions tab -->> Add Program -->> Browse
Click the C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe executable program, click Open, and then click OK.


 Subscribe to Articles


Further Readings:

Author: Akhil Raj         Company URL: http://www.dotnetspark.com
Posted Date: October 22, 2009

nice post good

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend