SQL 2005+

Allowing Remote Access to SQL Server Express

by Richard Carr, published at http://www.blackwasp.co.uk/AllowRemoteSQLExpress.aspx

If SQL Server Express Edition is installed using default setup options access is only permitted from the local machine. This tip explains how to enable remote access to databases via TCP/IP or named pipes to allow the creation of centralised data stores.

SQL Server Surface Area Configuration

Once Microsoft SQL Server Express Edition has been installed, an icon appears in the start menu for the SQL Server Surface Area Configuration tool. This tool is used to configure services, network protocols and SQL Server components. On starting the tool, select the Surface Area Configuration for Services and Connections option to open the configuration dialog box.

Remote Connection Options

The configuration dialog box is divided into two areas. The left side of the window provides navigation and the right side allows modification of the available settings. Select Remote Connections from the navigation area. You will see that the server is configured to allow local connections only.

Enabling Remote Connections

To enable remote connections, select the radio button for Local and Remote Connections. Next select which network protocols you wish the server to accept. Once set, every database in the SQL Server Express instance is accessible remotely. Remember that a SQL Server accepting remote connections can be a security risk, so ensure that you have appropriate security measures in place.