SQL Server 2005 Express Edition - Part 5 - Post-Installation Network Configuration Tasks

August 27, 2007

In the first few installments of our series dedicated to SQL Server 2005 Express Edition, we have presented an overview of its main characteristics that distinguish it from Compact, Workgroup, Standard, and Enterprise-level products, documented its basic installation process, and described some of the initial configuration steps that employ Surface Area Configuration utility. In this article, we will continue exploring other post-setup tasks, focusing on network specific tasks.

As we have mentioned earlier, the only available method of interacting with a newly installed SQL Server 2005 Express Edition relies on shared memory connection type, which requires direct access to the computer hosting the instance. In order to be able to connect remotely, you need to perform a few additional steps. The first one involves enabling network protocol support. While this can be accomplished with Surface Area Configuration for Services and Connection (as described in our previous article), you might want to use a more versatile SQL Server Configuration Manager (whose shortcut appears in the Configuration Tools submenu of Microsoft SQL Server 2005 All Programs menu) for this purpose. This utility, implemented as a Microsoft Management Console snap-in, gives you the ability not only to selectively enable each protocol for each local instance of SQL Server 2005 Server (as well as for SQL Native Client, which implements OLE DB and ODBC compatible client-side of a transmission channel), but also to independently configure their parameters, such as IP addresses with the corresponding ports on which the database engine will listen to incoming connections or "Keep Alive" interval (a value expressed in milliseconds that determines frequency of checks verifying the status of communication partners) for TCP/IP settings or the label used to establish a Named Pipes-based exchange (set by default to \\server\pipe\MSSQL$instance\sql\query, where instance and server are names of the SQL Server 2005 instance and its host computer, respectively). TCP/IP is used more commonly, not only as the sole practical choice for direct, Internet-based, firewalled connections, but also because of its superior performance and reliability in situations where bandwidth is limited (such as congested LAN environments or dial-up VPN links). Note that even though VIP (Virtual Interface Adapter) protocol is listed as one of available options, Express Edition does not support it, presumably due to the fact that Virtual Interface Architecture (for more information on this subject, refer to Evolution of the virtual interface architecture article on the IEEE Web site) relies on high-speed, low-latency networks, which rarely are part of low-end database deployment scenarios. Consistent with changes introduced in other editions of SQL Server 2005, it is not possible to establish connectivity over legacy protocols such as IPX/SPX, AppleTalk, or Banyan Vines.

When configuring TCP/IP properties for local instances of SQL Server 2005 Express Edition using Configuration Manager, you have a choice of either static or dynamic listening port assignments for every IP address associated with the host computer (including the loopback address 127.0.0.1). While the default installation designates TCP port 1433 for this purpose, such an arrangement is not suitable when multiple, named instances come into play (since conflicts between duplicate ports would prevent them from operating). One possible resolution to this dilemma is to configure automatic, dynamic port selection that takes place at the database engine startup time and which outcome can be determined by examining the content of TCP Dynamic Ports entries in the IP Addresses tab of the Properties dialog box for the TCP/IP protocol of a target instance using the SQL Server Configuration Manager utility (or by searching through the output of executing NETSTAT -abn from the Command Prompt for entries containing references to sqlservr.exe process). Since tracking port values in such cases and adjusting them accordingly for every new connection would not be practical, the responsibility of discovering active instances and communicating their listening ports in response to client requests has been delegated to SQL Server 2005 Browser Service, which can be reached via UDP port 1434. Interestingly, this behavior can be disabled for any individual instance by turning on the "Hide Instance" setting in its Protocols dialog box, effectively forcing remote clients to include the target port number in their connection strings. To take advantage of these capabilities, SQL Server Browser Service must be running (there is a single instance of such service per operating system, regardless of the number of concurrent SQL Server installations). This typically requires changing its default Start Mode from Disabled to Automatic and initiating its startup. Both of these actions can be accomplished from the SQL Server Browser Properties dialog box, accessible from SQL Server 2005 Services node in the SQL Server Configuration Manager (alternatively, you can perform them using Services MMC snap-in).

The above described mechanism simplifies connectivity from the internal network, but does not address challenges that surface when attempting to allow SQL Server-related traffic to cross network boundaries guarded by firewalls. In such situations, you might have to resort to static ports, which can be done using the same TCP/IP Properties dialog box in SQL Server Configuration Manager (ensure that you assign a unique value to each instance).

Choosing an appropriate port type (static vs. dynamic), "Hide Instance" setting, or Browser Service startup mode on the server side affects the way connection strings are constructed by database clients. For example, once all conditions essential to remote access functionality have been satisfied, you could run the following to connect to the SQL Server 2005 Express Edition instance named SQLEXPRESS on the computer named SQLSERVER01, using SQLCMD.EXE over TCP/IP and Named Pipes (assuming target TCP port 1454 and default pipe name) protocols, respectively, via Windows integrated authentication and with the Browser Service not running:

SQLCMD.EXE -S tcp:SQLSERVER01\SQLEXPRESS,1454
SQLCMD.EXE -S np:\\SQLSERVER01\pipe\MSSQL$SQLEXPRESS\sql\query

In the same circumstances but with the SQL Server Browser Service operational, you would be able to accomplish the same goal with:

SQLCMD.EXE -S SQLSERVER01\SQLEXPRESS

As you can see, the Browser Service eliminates the need for referencing a specific port (in case of TCP/IP) or named pipe (for Named Pipes protocol) since it possesses all necessary information to determine connection parameters for a particular instance. The protocol to be used is determined based on the client configuration. For example, in the case of SQL Native Client, the relevant settings are assigned using Client Protocols and Aliases subnodes of the SQL Native Client Configuration node in the SQL Server Configuration Manager. Client protocols can be enabled and ordered according to your preferences while aliases allow you to associate arbitrary names (designating target instances) with other parameters required to establish a connection (such as target server, protocol to be used, as well as the protocol settings, such as a TCP/IP port or a value of a Named Pipe). Obviously the configuration on the server and its clients must match for connection attempts to be successful.

Starting with Windows XP Service Pack 2, Microsoft introduced its own firewall software as part of the operating system, in this way imposing restrictions on all unsolicited inbound connections. This needs to be taken into account when evaluating requirements for remote access to SQL Server 2005 Express Edition. Extra procedures associated with this change consist of adding the SQL Server 2005 database engine and the Browser service executables to the list of programs for which incoming connection requests are allowed. To implement these steps, launch the Windows Firewall applet (the quickest way to accomplish this is by invoking FIREWALL.CPL either from the Windows XP and 2003 Start->Run box or via Vista Search entry, depending on the host operating system). Switch to the Exceptions tab in the Windows Firewall dialog box (assuming that Windows Firewall is turned on and the Windows XP "Don't allow exceptions" - or equivalent Vista's "Block all incoming connections" - checkbox is not enabled). Click on the Add Program command button, and locate (using Browse... feature) the SQLSERVR.EXE file for the target SQL Server 2005 Express Edition instance (by default, this file is located in the Program Files\Microsoft SQL Server\MSSQL.x\MSSQL\Binn folder, where x is a unique integer, assigned consecutively, starting with 1, to each subsequent installation of the database engine). Once this process is completed, you should be able to connect to this instance via TCP/IP protocol as long as you know its listening port and you specify it in your connection string. (You would need to repeat the same sequence of steps for all SQL Server instances installed on the same computer, pointing each time to their respective SQLSERVR.EXE main executable). If you intend to allow connections via Named Pipes, include TCP port 445 in the Exceptions list (keep in mind that this port would already be open if you have enabled File and Printer Sharing connections) and designate the appropriate scope (any computer, local subnet, or custom list, depending on the network location of client computers). To leverage SQL Server Browser Service, you need to create another entry in the Exceptions list, pointing to the SQLBROWSER.EXE, located by default in the Program Files\Microsoft SQL Server\90\Shared folder.

In the next article of this series, we will take a closer look at other post-setup configuration options relevant from the manageability, performance, and security perspectives.

» See All Articles by Columnist Marcin Policht