XP SP2 and SQL Server - 19 Oct 2004

Windows XP Service Pack 2 (SP2) solves some serious security problems but carries with it some connectivity issues for SQL Server users. XP SP2 is more than a set of bug fixes or a rollup of Microsoft's latest security patches. XP SP2 also adds many new features to XP and changes some of the system's default security settings.

The most significant XP SP2 addition is the new Windows Firewall (WF). Turned on by default, WF replaces the old Internet Connection Firewall (ICF). If you've used your XP laptop system on a public wireless network, you can appreciate the importance of using a personal firewall to protect your system and network from viruses, worms, and other security exposures. However, the pre-SP2 ICF caused so many application incompatibilities that many users simply ignored it. Although WF is a great improvement over ICF, it's also the source of important connectivity issues with SQL Server—more about those in a moment.

XP SP2 also features the new Security Center, whose graphical indicator shows the state of your system's antivirus protection, firewall, and automatic update settings. Microsoft enhanced Outlook Express to automatically block the display of images in the message preview window and added a pop-up blocker for Internet Explorer (IE). And new Data Execution Prevention (DEP) functionality stops buffer-overflow exploits against 64-bit systems. XP SP2 contains new Bluetooth support for cell phones, printers, keyboards, mice, and Palm and Pocket PC devices. And a new Wireless Network Connection dialog box provides a user-friendly view of available wireless networks and lets you connect to or disconnect from them.

Although SP2 adds valuable security and usability features to XP, you need to watch out for SQL Server compatibility problems. For the common setup where a client machine running XP needs to connect to a SQL Server system that's running on a Windows Server OS, you have nothing to worry about. When the XP SP2 system makes the connection to SQL Server, WF opens port 1433 on the client. The server is already listening on that port, and everything works fine.

However, if SQL Server is actually running on XP SP2, the scenario is more complicated. Microsoft SQL Server Desktop Engine (MSDE) and SQL Server Personal, Developer, and Enterprise Evaluation editions can run on XP. So, the server setup applies mainly to SQL Server development machines and MSDE applications running on XP. In this environment, WF shuts off network access to SQL Server for all TCP and UDP ports and blocks in-bound access to Analysis Services and Reporting Services. However, using Named Pipes on the same subnet works, and shared memory connections are unaffected, so local MSDE applications run with no changes. To enable network access to an instance of SQL Server running on XP with WF enabled, go to Control Panel, Security Center, Windows Firewall, Exceptions, Add Port and specify 1433. If you're running named instances, you need to follow this procedure for each named instance's TCP port. For more information about XP SP2 and SQL Server, see Richard Waymire's MSDN Webcast, "SQL Server and Windows XP SP2," at http://go.microsoft.com/fwlink/?linkid=27819.

XP SP2 simply has too many improvements and security benefits to pass up. So, the question for SQL Server customers isn't whether to apply XP SP2 but how to do so effectively.