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
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
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.