SQL Server and the Windows Server 2008 Firewall

We’ve long recommended that customers use the Windows Firewall to protect SQL Server installations. Starting with Windows XP/SP2, and continuing with Windows Vista, the firewall has been enabled by default on Windows client operating systems. Windows Server 2008 marks the first time this protection has been extended to a Windows Server OS.

For those of you migrating from Windows Server 2003 or earlier to Windows Server 2008, if you have not previously heeded the advice to enable the firewall, you may be surprised by connectivity failures caused by the firewall (for any version of SQL) and you will need to take action to enable the connectivity you want.

Don’t panic! J Choosing the right firewall strategy isn’t as hard as it may seem, and it will pay dividends over the long run. We have a books online entry that has lots of good information on how to use the firewall. That document is available at

https://msdn.microsoft.com/en-us/library/cc646023(SQL.100).aspx.

We strongly recommend you read that before making changes to your firewall strategy. For detailed information about the firewall, see

https://technet.microsoft.com/en-us/network/bb545423.aspx

I use the term “firewall strategy” intentionally, because there are some tradeoffs that only you can make. You might want to simply “configure the firewall” to make it all “just work,” and you could do that, but it might expose you in ways you don’t intend. To highlight that point, I will refer you to a recent survey by David Litchfield of NGS Software. Quoting from the executive summary:

The survey found that there are approximately 368,000 Microsoft SQL Servers directly accessible on the Internet and around 124,000 Oracle database servers directly accessible on the Internet.

That’s a lot of servers directly exposed to the Internet, and I doubt strongly that level of exposure is intended. So we’re hopeful that you will spend some time making choices you are comfortable with, and that the change to enable the firewall on Windows Server 2008 will lead to a level of exposure that more closely matches your real business needs. And only you can determine your real business needs.

I should note that exposure by itself does not imply that there is any particular vulnerability. Indeed, only 4% of the exposed SQL servers were running a vulnerable version of SQL Server, and those few vulnerable servers appear to have gone unpatched for many years now (our competitors fared much worse in this regard, candidly). But in the event of a newly-discovered vulnerability in SQL Server those 368,000 servers could become 368,000 targets overnight (or faster), and we all want to avoid that.

So, our first piece of advice: review your existing firewall strategy, including your host and network firewalls, to ensure that none of your servers are unintentionally exposed to the internet or to untrustworthy insiders.

Step 0: Establish the scope of your scan, and appropriate policies. Will you be performing a simple audit of connectivity, or a full penetration test? What knowledge will the auditor have of your systems ahead of time? Who is authorized to have the results? Who is tasked with making changes based on the audit?

 ISO-27002 discusses “ guidelines and general principles for initiating, implementing, maintaining, and improving information security management in an organization” and is worth reviewing if you don’t have established policies in place. Additional resources include

· The CERT OCTAVE Method

· The Open Source Security Testing Methodology

· The Center for Internet Security

· The Information Systems Audit and Control Association IS Auditing Procedure

 Doing an audit carelessly can be costly and embarrassing. Review the resources above, and plan well.

Step 1: Locate your SQL Servers. Not only are SQL Servers in the datacenter serving enterprise-wide functions, but SQL Servers power desktop and department-wide applications outside the data center as well. And SQL Server comes in free editions like SQL Server Express, and SQL Server Express Advanced, which end users can download and install on their desktops, and which are bundled in other products like Visual Studio.

You can use SQL Browser to search for SQL Servers from different places on your network. Because Browser broadcasts to other instances of Browser in order to locate SQL Servers, there are some limitations.

· If the Browser service on a remote machine is not running, or is blocked by a firewall, this method won’t work of course

· If the firewall is enabled on the machine from which the scan is performed, that may interfere with gathering complete results. If a server takes more than 3 seconds to respond, that response may be blocked by the client-side firewall. See the UnicastResponsesToMulticastBroadcastDisabled property of the Windows firewall for more information.

· Because SQL Browser uses broadcasts, it can only “see” SQL servers in the same broadcast domain. So, you may have to do this from different places in your network.

· On clustered systems, be aware that browser is not a clustered resource, and does not support failover from one cluster node to the other.

For more information on Browser, see https://msdn.microsoft.com/en-us/library/ms181087.aspx.

Beyond using SQL Browser, you can perform a network scan looking for services listening on default ports for any of the SQL components. These default ports are

This is just the default ports for the most common scenarios, not an exhaustive list of possibilities. For more detail, see https://msdn.microsoft.com/en-us/library/cc646023(SQL.100).aspx.

Step 2: review the host firewall configuration to ensure it complies with your policy. For the Windows Firewall with Advanced Security, the basic checks are to ensure that there are appropriately configured exceptions for the services you intend to offer, and otherwise, ports in the firewall are blocked. In today’s highly networked world, with complex schemes to embed one protocol within another, and advanced scanning techniques designed to penetrate network firewalls, a host firewall is more important than ever.

Review the documents below for detailed information on using the firewall with various components of SQL:

Beyond just a simple “port open” or “port closed” decision, you should consider at least the network scope (all systems, local subnet, or a specific list of IP addresses), whether or not IP Sec is enabled, and what profile you are configuring. Review “Configuring the Windows Firewall to Allow SQL Server Access” for more information.

Step 3: Review your network firewall configuration to ensure that access to your network is correctly configured. Here, configuration choices vary widely and there are many makers of network firewall systems, including the Microsoft Internet Security and Acceleration (ISA) Server.

The ISA team has provided online training to help you design a perimeter firewall defense, covering the various classes of network firewalls, security, scalability, performance, and other topics. That material is available at

https://www.microsoft.com/technet/security/learning/perimeterdefense/all/default.mspx.

Step 4: Verify that your defenses are operating as expected. This may be the most difficult part because the job of the network is to deliver packets, and it may succeed despite your best efforts to stop it.

The basic idea is to attempt to establish connectivity to SQL Server from a location from which you believe the server is not accessible. Even if you have a machine that is providing a service to the whole world by design, it’s unlikely that you want to provide all services available on that machine to all comers. So make sure that you are checking for all the services the machine might offer.

It can help here to draw your network as a set of zones, and enumerate the connectivity policy within and between zones. A zone might be the “demilitarized zone” which traditionally provides a buffer between your public and private networks, or it may be “the sales zone,” where access is permitted to sales data. “The sales zone” does not consist of a list of authorized sales people, but rather a list of networks from which those sales people need access (after providing appropriate credentials of course).

As part of that process, you