Failover clustering
97Authentication mode
In Windows Authentication mode, SQL Server will only accept connections from ses-sions bearing a security token assigned during a successful Windows login. Not only does this simplify SQL Server login management (logins are already managed in Win-dows), Windows logins can be the subject of powerful password complexity and expi-ration policies, options that weren’t available in SQL Server authentication in versions prior to 2005.
In SQL Server 2005, Microsoft significantly strengthened SQL Server authentica-tion by providing a means to enforce password complexity and expiraauthentica-tion policies to match the host Windows Server, as long as the server is running Windows Server 2003 or above. Despite this addition, Windows authentication remains the best choice.
6.1.1 Windows Authentication mode
Windows Authentication mode offers many advantages:
It’s the most secure. Passwords are never sent across the network, and expira-tion and complexity policies can be defined at a domain level.
Account creation and management is centralized, and usually handled by spe-cialist IT groups.
Windows accounts can be placed into groups, with permissions assigned at a group level.
SQL Server, with its ability to accept Windows authenticated logins, can leverage all of these benefits, providing both a secure and simplified login and a permissions man-agement environment.
If Windows Authentication mode is chosen during installation, the setup process still creates the SA account but disables it by default. Changing to Mixed Mode security, discussed next, requires this account to be reenabled before it can be used. If the authentication mode is changed, SQL Server will check for a blank SA password and prompt for a new one if appropriate. Given that brute-force attacks tend to target the SA account, having a nonblank, complex SA password is crucial in preventing unau-thorized access.
In some cases, connections may originate from clients or applications unable to connect using Windows Authentication mode. To enable such connections, SQL Server also supports the SQL Server and Windows Authentication mode.
Application vendors and the SA account
Unfortunately, many application vendors rely on SQL Server authentication, and in the worst examples, hard-code SA as the username in the connection properties (some with a blank password!). As DBAs, we should be exerting pressure on such vendors to ensure there’s at least an option to use something other than the SA account, and ideally to use Windows Authentication mode.
6.1.2 SQL Server and Windows Authentication mode (Mixed Mode)
Unlike Windows authentication, SQL Server authentication works by validating a user-name and password supplied by the connecting process. For example, in Windows Authentication mode, a process connecting from the WIDGETINC\JSmith account would be automatically accepted if the Windows account is defined as a SQL Server login with access to the appropriate database. No password needs to be supplied because Windows has already validated the login. In contrast, a SQL Server authentica-tion session supplies a username and password for validaauthentica-tion by SQL Server.
Despite welcome improvements to SQL Server authentication mode in 2005, some concerns with this login method remain:
For applications using SQL Server authentication, SQL passwords are commonly included in connection strings stored in clear text in configuration files or reg-istry entries. If you’re using this authentication mode, store passwords in an encrypted form before unencrypting them for use in establishing a connection.
Despite the login credentials being encrypted during the SQL Server login pro-cess, the encryption is typically performed using a self-signed certificate. While such encryption is better than nothing at all, it’s susceptible to man-in-the-mid-dle or identity spoofing attacks. In Windows authentication, passwords are never transmitted over the network as part of the SQL Server login process.
While password expiration and complexity policies are available in SQL Server authentication, such policy enforcement isn’t mandatory, and each SQL Server’s policies could potentially have varying degrees of strength, compared to a policy defined and enforced at a domain level using Windows authentication.
For these reasons, Windows authentication remains the most secure choice for a SQL Server installation. If you do choose the SQL Server Authentication mode—for exam-ple, to support connections from non-Windows authenticated clients—ensure that pass-words are adequately complex and you have an appropriate expiration policy in place.
A strong login authentication model is a crucial aspect of a secure SQL Server environ-ment, as is locking down network access, as you’ll see next.
6.2 Networking
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
Surface area configuration
Unlike SQL Server 2005, there’s no Surface Area Configuration tool in SQL Server 2008; a variety of tools are used in its place, including policy-based management (cov-ered in chapter 8), sp_configure, and Management Studio. Fortunately, the default installation and configuration settings are secure, so unless configuration settings are explicitly changed, the surface area of a SQL Server instance will remain secure.
99 Networking
disabled by default. In this section, we’ll look at the process of enabling and config-uring 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 con-figuring network protocols.
6.2.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, Stan-dard, 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 pre-vious 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 dis-abled 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 6.2.
Figure 6.2 The SQL Server Configuration Manager is used to enable and disable network protocols.
Once it’s enabled, you need to configure TCP/IP for maximum security by assigning a static TCP port along with appropriate firewall protection.
6.2.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 multi-instance a unique port number, you can use dynamic ports. That way, SQL Server will automati-cally choose a free port number when an instance starts up.
By default, each named1SQL 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 connec-tion 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. As we saw in chapter 4, the setup process sets the browser service’s startup type to automatic.
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 obvi-ously fail if the port number changes, courtesy of the dynamic port option. For this rea-son, 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 fire-wall 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 pur-pose; 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 6.3, 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 IPAll2 and enter a port number in TCP Port. In our example, we’ve cho-sen 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
1 If installed, a default instance, that is, a non-named instance, will use port 1433 unless you change it manually.
2 To configure SQL Server on a multihomed server, set the Active value to false for IP addresses that SQL should not listen on, and configure the TCP port for individual IP entries rather than the IPAll entry.
101 Networking
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. Full details of this process can be found in SQL Server Books Online (BOL) under the
“New Alias (Alias Tab)” article.
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 Win-dows 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.
6.2.3 Windows Firewall
A 2007 survey3 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
3 The Database Exposure Survey 2007, David Litchfield, Next Generation Security Software.
Figure 6.3 SQL Server Configuration Manager lets you configure SQL Server instances with a static TCP port.
2008 enables the Windows Firewall by default. For the most secure SQL Server installa-tion, the Windows Firewall should be left enabled, with the appropriate ports opened—that is, the port used by each installed SQL Server instance.
As we covered in chapter 4, the SQL Server installation process will detect the pres-ence 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 6.4. You can find full details on this process in SQL Server BOL, under the “Configuring the Win-dows Firewall to Allow SQL Server Access” article.
In closing our section on network security, let’s examine the process of encrypting network communications.
6.2.4 Network encryption
SQL Server 2008 introduces a feature called Transparent Data Encryption (TDE), which we’ll discuss later in this chapter. 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 trans-mission 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 encryp-tion can be enabled using a combinaencryp-tion of an installed certificate and the SQL Server Configuration Manager tool.
SQL Server can use either self-signed or public certification authority certificates.
As mentioned earlier in this chapter, self-signed certificates offer only limited security
Figure 6.4 The Windows Firewall can be configured to allow communication on specific ports.
103