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

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 \serverpipeMSSQL$instancesqlquery,
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:SQLSERVER01SQLEXPRESS,1454
SQLCMD.EXE -S np:\SQLSERVER01pipeMSSQL$SQLEXPRESSsqlquery

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 SQLSERVER01SQLEXPRESS

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 FilesMicrosoft SQL ServerMSSQL.xMSSQLBinn
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 FilesMicrosoft SQL Server90Shared
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

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles