Windows XP SP2 Defaults Break SQL Server Connections

If you're running Windows XP Service Pack 2 (SP2) and SQL Server, you've probably figured out that the XP SP2 default installation prevents most network access to SQL Server—including to Microsoft SQL Server Desktop Engine (MSDE). Fortunately, making SQL Server and XP SP2 coexist isn't difficult, and I'm here to provide resources that will make the task even easier for you.

The best place to start is with "FAQ: How Windows XP Service Pack 2 (SP2) Affects SQL Server and MSDE." This FAQ answers questions about how XP SP2 affects SQL Server and MSDE, how you can tell if your installations of SQL Server or MSDE are blocked, what to do if your application needs SQL Server to listen to the network, what you should do to update your SQL Server system, and where to find more technical information about getting XP SP2 to work with SQL Server and MSDE. You also need to review "How to enable SQL Server connectivity on Windows XP Service Pack 2," to learn how to set SQL Server as an exception in Windows Firewall. Finally, you should watch Richard Waymire's archived MSDN Webcast, "SQL Server and Windows XP SP2—Level 300." Bring your popcorn and settle in for 90 minutes. The Webcast presents a wealth of important SQL Server security information such as how to enable listening on the various network protocols and when you don't need to make any changes. This Webcast is a must-see for any SQL Server professional.

XP SP2 has many valuable new features, including a set of Microsoft-developed security technologies to help reduce the risk of malicious attacks against your computers. I'm particularly fond of Windows Firewall—an enhanced version of the component previously called Internet Connection Firewall. However, the Windows Firewall default installation is what blocks Internet access to SQL Server. Years of malicious worms and other Internet nasties have proven that our SQL Server boxes need to be locked by default. I don't have the space to describe the steps necessary to maximize the security capabilities of Windows Firewall while enabling users to access SQL Server across the Internet. The Microsoft article I mentioned earlier, "How to enable SQL Server connectivity on Windows XP Service Pack 2," gives all the details. But we all need to realize that the days of leaving Internet "front doors" open in case a neighbor stops by are over.