Connections to SQL Server are
established using a standard network protocol such as TCP/IP. Depending
on the installed edition of SQL Server, certain protocols are disabled
by default. In this section, we'll look at the process of enabling and
configuring network protocols. You'll learn the importance of only
enabling the required protocols, configuring TCP/IP settings, protecting
SQL Server behind a firewall, and encrypting network communications.
Let's begin with looking at enabling and configuring network protocols.
1. Protocol selection and configuration
The following network protocols are available for use with SQL Server 2008:
Shared Memory—Enabled
by default on all editions of SQL Server 2008, the Shared Memory
protocol is used purely for local connections to an instance running on
the same machine.
Named Pipes—For
all editions of SQL Server, Named Pipes is enabled by default for local
connections only, with network connectivity over named pipes disabled.
TCP/IP—The
TCP/IP protocol is enabled by default for the Enterprise, Standard, and
Workgroup editions of SQL Server, with the protocol disabled for the
Developer, Express, and all other installations.
VIA—A
specialized protocol developed for use with specific hardware, the VIA
protocol is disabled by default for all installations of SQL Server.
Note that an upgraded instance of SQL Server
will preserve the pre-upgrade network configuration settings. Banyan
VINES, Multiprotocol, AppleTalk, and NWLink IPX/SPX are no longer
supported in SQL Server 2008. Looking at the protocols in the previous
list, if we set aside VIA as a specialist choice and ignore Shared
Memory as a local protocol only, the only two choices for a networked
installation of SQL Server are TCP/IP and Named Pipes.
TCP/IP is the most widely used network protocol.
Compared to Named Pipes, it provides better security and performance,
particularly when used over a WAN or slower network.
From both performance and security perspectives,
unused protocols should be disabled and, ideally, a single network
protocol chosen for SQL Server communication. In almost all cases,
TCP/IP should be used as the standard protocol for SQL Server instances,
with all other protocols disabled. You enable and disable network
protocols using the SQL Server Configuration Manager, as shown in figure 1.
Once it's enabled, you need to configure TCP/IP
for maximum security by assigning a static TCP port along with
appropriate firewall protection.
2. Static and dynamic TCP ports
Each SQL Server instance "listens" for client
requests on a unique TCP/IP address/port number combination. In SQL
Server 7 and earlier, we were restricted to installing a single instance
per server, with the instance listening on port 1433. To support the
installation of multiple named instances per server, SQL Server 2000
introduced dynamic ports.
Dynamic ports ease the configuration requirement
for unique ports in a multi-instance installation. Rather than having
to manually assign each named instance a unique port number, you can use
dynamic ports. That way, SQL Server will automatically choose a free port number when an instance starts up.
By default, each named
SQL Server instance is configured to use dynamic TCP/IP ports. This
means that each time a named instance of SQL Server is started, the TCP
port used may be different. The SQL
Server Browser service responds to client connection requests with the
port number that the requested instance is running on, thus avoiding the
need for client applications to be aware of the port number an instance
is currently using.
Dynamic ports present a problem for firewall
configuration. An attempt to secure a SQL Server instance behind a
firewall by only opening a specific port number will obviously fail if
the port number changes, courtesy of the dynamic port option. For this
reason, static ports are the best (and most secure) choice when placing
SQL Server behind a firewall. In return for the additional configuration
required to assign each SQL Server instance a static port number, the
appropriate ports can be opened on the firewall without running into the
connection failures typical with dynamic ports.
When assigning a static TCP port, avoid using
ports currently (and commonly) used by other services and applications.
The IANA registration database, available at http://www.iana.org/assignments/port-numbers,
is an excellent resource for this purpose; it lists registered port
numbers for common applications, as well as "safe" ranges to use for SQL
Server instances.
As you can see in figure 2,
you can set an instance to use a static TCP port by using the SQL
Server Configuration Manager tool. Simply delete the TCP Dynamic Ports
entry for IPAll and enter a port number in TCP Port. In our example, we've chosen port 49153.
The SQL Server Browser service runs on port
1434. If the browser service is stopped, or port 1434 is closed on the
firewall, the port number needs to be included in the connection
request. For example, to connect to a SQL Server instance called SALES
running on port 49153 on the BNE-SQL-PR-01 server, we'd connect using
BNE-SQL-PR-01\Sales,49153
An alternative to including the port number in
the connection string is to create an alias on each connecting client
using the SQL Server Configuration Manager tool.
We've spoken about firewalls a few times now. In
addition to network firewalls, we also have the option of using the
Windows Firewall. Since Windows XP SP2, the Windows Firewall has been
enabled by default on client operating systems. For the first time in a
server operating system, the firewall is also enabled by default in
Windows Server 2008.
3. Windows Firewall
A 2007 survey
found approximately 368,000 SQL Server instances directly accessible on
the internet. Of those, almost 15,000 were completely insecure and
vulnerable to worms such as the infamous SQL Slammer, a worm that spread
rapidly in 2003 by exploiting a buffer overflow bug in SQL Server (the
patch for it was released six months before the worm struck).
In light of the proliferation of port scanners
and widely accessible network firewall penetration techniques, having a
solid host firewall strategy is crucial. Windows Server 2008 enables the
Windows Firewall by default. For the most secure SQL Server
installation, the Windows Firewall should be left enabled, with the
appropriate ports opened—that is, the port used by each installed SQL
Server instance.
The SQL Server installation process will detect the presence of the
Windows Firewall and provide a warning to open the appropriate ports.
This can be achieved using the Control Panel, as you can see in figure 3.
You can find full details on this process in SQL Server BOL, under the
"Configuring the Windows Firewall to Allow SQL Server Access" article.
In closing our section on network security, let's examine the process of encrypting network communications.
4. Network encryption
SQL Server 2008 introduces a feature called Transparent Data Encryption
(TDE). When enabled, TDE
automatically encrypts and decrypts data as it's read from and written
to the database without the need for any application changes.
Even with TDE enabled, other than the initial login credentials, the network transmission of data is unencrypted, meaning packet sniffers could be used to intercept data.
For maximum data security, the network transmission of SQL Server data
can be encrypted using either Internet Protocol Security (IPSec) or
Secure Sockets Layer (SSL).
Requiring no SQL Server configuration, IPSec
encryption is configured at the operating system level on both the SQL
Server and the connecting clients. SSL encryption can be enabled using a
combination of an installed certificate and the SQL Server
Configuration Manager tool.
SQL Server can use either self-signed or public
certification authority certificates. Self-signed certificates offer only limited security and are
susceptible to man-in-the-middle attacks. For maximum SSL security,
certificates from a public authority such as VeriSign can be used.
Once you've installed a certificate, you can use
the SQL Server Configuration Manager tool to configure a SQL Server
instance to use the certificate and to specify whether or not client
connections are accepted that can't support an encrypted connection. In a
similar manner, Configuration Manager lets you configure the client end
of the connection, with the option to force encryption, and whether or
not to trust a server with only a self-signed certificate.
SQL Server BOL contains a full description of
the process for enabling SSL encryption for a SQL Server instance,
including coverage of cluster-specific encryption processes.
As with any encryption, SQL Server network
encryption involves processing overhead and therefore lowers performance
to some degree. In environments with sensitive data, the performance
impact of encryption is typically of less concern than protecting the
data from unauthorized access. Before implementing encryption in a
production environment, test and measure the process and overhead in a
test environment.
Even with strong network
security and authentication models in place, a SQL Server instance is
far from secure if those with legitimate access to a SQL Server instance
have more access than what they
actually need. With a significant percentage of security breaches
performed as inside jobs, the importance of least privilege is not to be
underestimated.
