XP SP2 and SQL Server - 26 Aug 2004

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:

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:

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!