Since the 2005 version, SQL Server implements the Secure by Default principle, which means that upon installation SQL Server disables a number of features that are considered potentially insecure; this includes support for remote connections.
The following actions must be performed on the computer containing the SQL Server instance that will be remotely accessed (either by the stored procedures, or by any application that will run the stored procedures):
SQL Server Configuration Manager
This can be found from the Start Menu:
Microsoft SQL Server 2005* -> Configuration Tools -> SQL Server Configuration Manager.
Select “SQL Server 2005* Network Configuration”. (If the operating system is 64-bit and a 32-bit SQL Server instance is being configured, select “SQL Server 2005* Network Configuration (32-bit)” instead.)
Select “Protocols for instance”. Note that instance refers to the name of the SQL Server instance; there will be at least one instance listed (the default instance is named MSSQLSERVER). Select the correct instance.
The right-hand pane lists the available remote connection protocols. By default, only the Shared Memory protocol is enabled. To enable remote connections, right-click the TCP/IP protocol and select Enable from the menu.
Acknowledge the confirmation box, then close the Configuration Manager.
SQL Server Browser
The SQL Server Browser service must be running on the remote computer.
Open up the Services console by using Run from the Start Menu, then typing:
services.msc
and clicking OK.
Within the console, locate the SQL Server Browser and open its properties dialog. If the service is stopped, click Start.
Additionally, the service’s startup type can be set to Automatic so that the service is always available, even after the operating system is restarted.
Click OK to close the dialog, then close the Services window.
Firewall
Two exceptions must be added to any running firewall.
The exact steps to do this are dependent on which firewall is being used – or, in the case of Windows Firewall, the operating system itself. If unsure, please consult your network administrator.
Firstly, add the full pathname of the SQL Server instance to the firewall’s exception list.
Instances can usually be found at “C:\Program Files\Microsoft SQL Server”. For example, the default instance will likely be
“C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe”.
Alternatively, locate the service within the Services console (see above), bring up its properties, then look for the service’s full pathname within the ‘Path to executable’ box (ensure that the arguments are ignored).
Secondly, add the full pathname of the SQL Server Browser to the firewall’s exception list: “C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe”
(for 32-bit operating systems use “Program Files” instead of “Program Files (x86)”).
User Access
Refer to the following section, Configuring SQL Server Security.