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

Introduction

This document will show you how to use the SQL Server Management Studio to enable users to connect to a SQL Server from remote computers.

Applies To

  • Microsoft SQL Server 2008
  • Microsoft SQL Server Express 2008

Prerequisites

SQL Server Management Studio

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 2008" -> "SQL Server Management Studio"

On Windows Vista and later it is generally quicker to start typing "SQL Server Management Studio" into the search bar at the bottom of the start menu.
Step 2

Once SQL Server Management Studio opens a Connect to Server dialog will open. In the Server Name field select the SQL Server to be configured for remote access.

Click Connect to connect to the server, and enter any login credentials that are required to administer the server.

Step 3

Once connected the server objects will appear in the object explorer on the left hand window of SQL Server Management Studio.

Right click on the Server name and select properties.

Step 4

In the Select a page section of the Server properties dialog click Connections.

Check the box next to "Allow remote connections to this server".

Click OK to close the server properties dialog.

The server is now configured to accept remote connections.

The settings will not be applied until the SQL Server service is restarted. This will be done in step 8 below.
Step 5

Protocols for the remote connection are set in Sql Server Configuration Manager.

Open your Windows Start Menu, and navigate to "All Programs" -> "Microsoft SQL Server 2008" -> " -> "Configuration Tools" -> "SQL Server Configuration Manager"

Step 6

Expand the "SQL Server Network Configuration" node and select "Protocols for ..."

Right click Named Pipes and select Enable

Right click TCP/IP and select enable

Step 7

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, click the "SQL Server Services" node in the tree on the left, and right click "SQL Server Browser", select Properties.

In the SQL Server Browser Properties dialog click the Service tab then change the "Startup Mode" from "Disabled" to "Automatic". Click Apply and then OK to close the dialog

Step 8

The final step is to start the SQL Server Browser service and restart the SQL Server service

From the Sql Server Configuration Manager select the "SQL Server Services" node from the tree on the left.

Right click the SQL Server Browser service from the main window and select Start.

Right click the SQL Server service from the main window and select Restart

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:KB13010801

Table of Contents