In June, I wrote about the effects that Windows XP Service Pack 2 (SP2) has on SQL Server (see "Windows XP SP2 Defaults Break SQL Server Connections" at http://www.winnetmag.com/article/articleID/42854/42854.html ). Now that XP SP2 is dribbling out through Windows Update and other sources, I want to revisit the topic. XP SP2 has some great new features, such as the easy-to-use Windows Firewall that comes turned on by default. Considering the security problems that have plagued the Windows community over the past few years, putting strong firewall protection into users' hands is essential. However, blindly installing XP SP2 on a machine that's running SQL Server will cause you a lot of grief. That wonderful new firewall will cause SQL Server to stop working if you do a simple, default installation of SP2. You need to know what knobs to tweak to ensure SQL Server will continue to work. Few production server applications are running on XP right now, but countless SQL Server instances are running on developer boxes on top of XP. And don't forget about the instances of Microsoft SQL Server Desktop Engine (MSDE) that are running on XP.
There's a rumor floating around the newsgroups that Microsoft recommends not installing XP SP2 on machines that are running SQL Server. That information isn't true, but you do need to understand the steps necessary to keep SQL Server working after you install SP2. I can't describe all the steps necessary to maximize the security capabilities of Windows Firewall while enabling users to access SQL Server across the Internet, but the following resources will get you up and running in no time. The most complete Microsoft article about XP SP2 and SQL Server that I'm aware of is "FAQ: How Windows XP Service Pack 2 (SP2) Affects SQL Server and MSDE," available at http://www.microsoft.com/sql/techinfo/administration/2000/security/winxpsp2faq.asp . In addition, the following Microsoft articles include tips to avoid XP SP2 gotchas:
- "Some programs seem to stop working after you install Windows XP Service Pack 2," http://support.microsoft.com/default.aspx?kbid=842242
- "BUG: The changes in a remote subscriber are not updated to the publisher for a queued updating replication that uses Message Queuing on a computer that is running Windows XP Service Pack 2," http://support.microsoft.com/default.aspx?kbid=839278
- "You may not be able to connect to an instance of SQL Server that is configured to use the Named Pipes server network library on a computer that is running Windows XP Service Pack 2," http://support.microsoft.com/?id=839269
- "You may receive a 7391 error message in SQL Server 2000 when you run a distributed transaction against a linked server after you install Microsoft Windows XP Service Pack 2," http://support.microsoft.com/?id=839279
- "You may receive a 7391 error message in SQLOLEDB when you run a distributed transaction against a linked server after you install Microsoft Windows XP Service Pack 2," http://support.microsoft.com/?id=873160
Do you like debugging stored procedures from Visual Studio.NET? Too bad: "BUG: SQL debugging does not work in Visual Studio .NET after you install Windows XP Service Pack 2," at http://support.microsoft.com/?id=839280 , describes another XP SP2 problem. But your security administrators will be thrilled by "A message stating that an instance of SQL Server is vulnerable to virus attacks is logged in the application event log when you install SQL Server 2000 or MSDE 2000 on a computer that is running Windows XP Service Pack 2," http://support.microsoft.com/?id=841375 . And the following articles cover best practices for implementing XP SP2 with SQL Server and important troubleshooting information:
- "How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server," http://support.microsoft.com/?id=841249
- "How to enable SQL Server connectivity on Windows XP Service Pack 2," http://support.microsoft.com/?id=841251
- "How to manually enable TCP/IP on Windows XP Service Pack 2 for SQL Server 2000," http://support.microsoft.com/?id=841252
- "How to enable SQL Server 2000 Reporting Services on Windows XP Service Pack 2," http://support.microsoft.com/?id=841253
- "How to enable the ISAPI component on SQLXML on Windows XP Service Pack 2," http://support.microsoft.com/default.aspx?kbid=842005
- "How to enable SQL Server 2000 Analysis Services and OLAP Services on computers that are running Windows XP SP2," http://support.microsoft.com/?id=841256
- "How to use a script to programmatically open ports for SQL Server to use on systems that are running Windows XP Service Pack 2," http://support.microsoft.com/default.aspx?kbid=839980
- "Troubleshooting Windows Firewall settings in Windows XP Service Pack 2," http://support.microsoft.com/default.aspx?kbid=875357
Finally, watch and learn from the "MSDN Webcast: SQL Server and Windows XP SP2 - Level 300," available at http://msevents.microsoft.com/cui/eventdetail.aspx?eventid=1032253410&culture=en-us . Bring your popcorn, kick off your shoes, and settle in for 90 minutes as Richard Waymire presents this Microsoft Webcast. The Webcast, a must-see for any SQL Server professional, presents a wealth of interesting security information. With any luck, you'll be finished with all that learning just in time for SP3!