Configuring the Server and Firewall

Using the Surface Area Configuration Tool

The SQL Server 2005 Surface Area Configuration (SAC) tool (shown in Figure 9.1) is a new (and welcome) tool used to simplify the task of managing and securing your SQL Server instances. It can be run on most any system and be used to connect to and configure SQL Server 2005 instances on the local or remote systems.

Figure 9.1. The Surface Area Configuration tool.

Using the SAC tool to manage how much (if any) of your SQL Server Instances is exposed to the LAN or WAN is easy.

1.	Choose the server to configure by clicking on "localhost"[2]. This exposes a dialog that permits you to address another server. Nope, this won't work against older versions of SQL Server or servers behind a firewall. [2] Don't be confused. When you create a ConnectionString and want to refer to a locally running SQL Server instance, you can use "(local)" or simply "."not "localhost".
2.	Click on "Surface Area Configuration for Services and Connections". This exposes a dialog reflecting the state of all SQL Server instances on the targeted server. As shown in Figure 9.2, this server has three instances installed: SQLEXPRESS, SS2K5, and SS2K5_2. SAC is one of several tools that can be used to start, stop, or pause a SQL Server instance or any of the associated Windows services, like Integration Services or Reporting Services (when installed). Here is where you can specify whether the server is to be automatically started when the system bootsset the "Startup type" to "Automatic." Figure 9.2. Surface Area Configuration for Services and Connections.
3.	Click on "Remote Connections" in the left dialog (shown in Figure 9.3). Note that the default setting permits only local connections. To make SQL Server visible to the local intranet, you can choose either TCP/IP or Named Pipes (or both) protocols. Which is better? Generally, I prefer TCP/IP, as it's more universally supported and understood. Let's just say that Peter and I have given up trying to make Named Pipes workit seems with firewalls and other considerations, it is just simpler to concentrate on TCP/IP protocols. Figure 9.3. Select a specific SQL Server instance to inspect or modify its properties.

This step must be repeated for each SQL Server instance you want to expose to the intranet (LAN). Note this dialog does not permit you to change the TCP/IP ports that SQL Server uses to listen to the Net. These can be changed (if really necessary) by using the SQL Server Configuration Manager. Be sure to restart the SQL Server instance(s) after you make changes to the enabled protocols. This can be done in the SAC tool or the SQL Server Configuration Manager, or using the Services.msc console application.

Static vs. Dynamic Ports

As I said, the default instance of SQL Server listens for incoming connections on port 1433. The port can be changed for security reasons or because of a client application requirement using SQL Server Configuration Manager. By default, all SQL Server named instances (including SQL Server 2005 Express Edition) are configured to listen on dynamic ports. To configure a static port, leave the TCP Dynamic Ports box blank (it defaults to "0") and provide an available port number in the TCP Port box in the TCP/IP properties settings dialog exposed by the SQL Server Configuration Manager. See Figure 9.4.

Figure 9.4. Setting "dynamic" TCP/IP port assignments.

If you don't select dynamic port addressing and you choose TCP/IP protocol, SQL Server (by default) uses TCP port 1433 and UDP port 1434. This means you'll need to poke a couple of holes in your firewall to permit data to be sent and received on these ports. In this case (as shown in Figure 9.5), I created two exceptions: one to permit TCP port 1433 and another to permit UDP port 1434 to pass through the firewall. I also chose the "Change scope..." option to limit visibility of these ports to my local subnet. This prevents the ports from being visible on the web.

Figure 9.5. Using the Windows XP Firewall Exceptions tab to enable ports.

In a similar way, if you choose the Named Pipes protocol, you'll need to enable port 445 on your firewall in addition to ports 1433, 1434, and port 135^[3], as discussed earlier when using TCP/IP protocol.

^[3] See http://support.microsoft.com/kb/841251/

To test this functionality, you can start Visual Studio and use the Server Explorer^[4] to create a new Data Connection. If this works, you know your settings are working. You can also use the new SQLCMD utility (that ships with SQL Server 2005) to test the connection (as shown in Figure 9.6). Note that I passed the server\instance name (betav1\ss2k5) and the Login ID as "SA"the arguments are all case-sensitive. SQLCMD prompts for the password; if it can connect, it (quickly) returns "1>" (the prompt for a line of SQL). To exit SQLCMD, enter "exit" and press Return. If you have not configured SQL Server to support "mixed-mode" security (which permits you to authenticate a connection using a valid SQL Server Login Id and password), you won't be able to log on as SAeven if you have stolen the password from the DBA's desk.

^[4] Note that the "Server Explorer" is called the "Database Explorer" in the Standard and Express versions of Visual Studio .NET 2005.

Figure 9.6. Using SQLCMD to verify that you can connect to a specified server.

Connecting to SQL Server 2005 from older clients is possibleeven using the older MDAC stack. However, the exception/error messages you get when the server is inaccessible behind a firewall or is not configured for remote connections can be very different.

If SQLCMD can't connect for some reason, you'll get a set of exception messages, as shown in Figure 9.7. Note that SQLCMD is using the new SQL Native Client (SNC) providerthat's why it's handy when you're trying to isolate provider issues. For example, SNC might work when the .NET SqlClient managed provider doesn't for some reason.

Figure 9.7. When things go wrong and you can't connect.

Internet Access

Okay, for some reason you want to expose your SQL Server to the Internet. While this is not recommended, a subtle change to your firewall settings can expose your SQL Server to everyone on the planet with a computer. While your firewall might be different, the Windows XP firewall (installed with SP2) permits you to change scope for your port assignments. When you click Add Port (and fill in the port settings) and "Change scope...", you can choose to expose the selected port to any computer, including those on the Internet (as shown in Figure 9.8). Actually, this is the default setting, so be sure to choose "My network (subnet) only" or enter the IP addresses of selected systems in a custom list.

Figure 9.8. Exposing a port to the Internet.