How To: Configure SQL Server for Remote Access (KB14022401) (DriveWorks Documentation)

How To: Configure SQL Server for Remote Access

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 2012 and newer
Microsoft SQL Server Express 2012 and newer

Prerequisites

Microsoft SQL Server Management Studio

Microsoft SQL Server (a supported edition) 2012 and newer

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.

Launch Microsoft SQL Server Management Studio.
In the Connect to Server dialog, select the SQL Server from the Server Name field.
Click Connect, and enter any login credentials that are required to administer the server.
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.
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.
Protocols for the remote connection are set in SQL Server Configuration Manager.
Launch Computer Management.
This can be found from Windows Control Panel > Administrative Tools.
Expand the "SQL Server Network Configuration" node and select "Protocols for <SQL Name>"
Right click Named Pipes and select Enable
Right click TCP/IP and select enable
Although remote clients can now talk to the SQL Server, they don't have a way to find it.
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.
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:	KB14022401

DriveWorks Pro 22 How To: Configure SQL Server for Remote Access (KB14022401)