DriveWorks Pro 15: How To: Configure SQL Server 2005 for Remote Access (KB13010802) [send feedback...]

Introduction

This document will show you how to use the surface area configuration wizard to enable users to connect to a SQL Server from remote computers.

Applies To

  • Microsoft SQL Server 2005
  • Microsoft SQL Server Express 2005

Prerequisites

None.

Steps

If you are working with a server managed by an IT department, make sure you consult your IT department before following any of these steps. They may already have particular policies that you will need to adhere to.

The following steps should be executed on the machine running Microsoft SQL Server either physically, or by using Microsoft Remote Desktop.
Step 1

Open your Windows Start Menu, and navigate to "All Programs" -> "Microsoft SQL Server 2005" -> "Configuration Tools" -> "SQL Server Surface Area Configuration"

On Windows Vista and later it is generally quicker to start typing "SQL Server Surface Area Configuration" into the search bar at the bottom of the start menu.
Step 2A dialog similar to the one on the left will appear, click the option at the bottom called "Surface Area Configuration for Services and Connections"
Step 3A dialog like the one on the left will appear, click the "Remote Connections" option in the tree on the left.
Step 4

The option "Local connections only" will probably be selected by default.

To make sure that remote clients can talk to the SQL Server, one of the three options under "Local and remote connections" should be selected.

The easiest option is to select "Using both TCP/IP and named pipes".

Step 5

Although remote clients can now talk to the SQL Server, they don't have a way to find it in the first place.

The SQL Server Browser service allows remote clients to search for the SQL Server, although this isn't strictly necessary, it does make life easier. If you are unsure, please read the SQL Server help, or consult an IT professional.

To enable the SQL Server Browser service, expand the "SQL Server Browser" node in the tree on the left, and click "Service", then change the "Startup type" from "Disabled" to "Automatic".

Step 6

The SQL Server is now configured for remote access, click "Apply" and you will get a message like the one on the left.

To finish making the changes, you need to restart the SQL Server service as described in the remaining steps, first though, we will start the SQL Server Browser service.

Step 7

Click the "Start" button which will be enabled now that you've applied the settings. The SQL Server Browser service will be started.

Step 8

To restart the SQL Server service, you need to use a different tool.

Close the surface area configuration wizard, and use the start menu to navigate to "All Programs" -> "Microsoft SQL Server 2005" -> "Configuration Tools" -> "SQL Server Configuration Manager"

Step 9In the SQL Server Configuration Manager dialog which appears, make sure the "SQL Server 2005 Services" node is selected.
Step 10

On the right hand side, right click the SQL Server to restart, and choose the "Restart" option.

The SQL Server service will be restarted, and the SQL Server will be accessible to remote clients.

Troubleshooting

If, after following the above steps, you can't access the SQL Server from remote clients, it is possible that you may have a firewall which is interfering. For information on setting up the default Windows Firewall to give access to SQL Server, please consult the following Microsoft Knowledge Base article which may help:

http://support.microsoft.com/kb/914277


Knowledge Base Article Ref:KB13010802

Table of Contents