다음을 통해 공유

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

 

Database Engine

1433

SQL Browser

1434 for DB engine

SQL Broker

4022, by convention

Analysis Services

2383

Reporting Services

80/443

 

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:

Database Engine

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

Analysis Services

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

Reporting Services

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

Integration Services

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

 

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

Comments

  • Anonymous
    July 02, 2008
    SDL alum, Shawn Hernan (now in the SQL Server team), has written an excellent post about SQL Server 2008,
  • Anonymous
    July 02, 2008
    Sounds like a movie title. No, it is actually a topic you need to know about if you plan to install SQL
  • Anonymous
    July 02, 2008
    Windows Server 2008 are și el firewall-ul activat by default, ca și Windows XP SP2 sau Windows Vista.
  • Anonymous
    July 03, 2008
    Did you know that Windows Server 2008 installation, by default, enables the Windows firewall to be ON
  • Anonymous
    July 06, 2008
    The comment has been removed
  • Anonymous
    August 07, 2008
    After I knew about the SQL 2008 RTM release news I have blogged SQL2008RTM-official here and as usual
  • Anonymous
    August 07, 2008
    After I knew about the SQL 2008 RTM release news I have blogged SQL2008RTM-official here and as usual
  • Anonymous
    November 06, 2008
    Here are some of the links related to SQL Server 2005 on Windows Server 2008 Cluster: Support WebCast:
  • Anonymous
    November 26, 2010
    No matter what program, once installed on the PC, you often can find it out in the Add/Remove programs (we also called it currently installed programs list), and from where you often can uninstall the program you don't want.  
  • Anonymous
    November 15, 2013
    after i moved from server 2k3 enterprise to sever 2008 enterprise and install SQL 2005 for my need, the remote users stopped connect to SQL, if I disable firewall in server2k8 every thing works fine but not with active firewall. I found way in this article (support.microsoft.com/.../de) by making a batch file, if the page is not longer active you can copy and paste bellow script in a notepad file and safe it as a batch file, i named it as OpenSQLPorts4Remotes-server2k8ONLY.bat (unusual long ;)). this is the scriptnetsh advfirewall firewall add rule name="Open Port 80" dir=in action=allow protocol=TCP localport=80@echo =========  SQL Server Ports  ===================@echo Enabling SQLServer default instance port 1433netsh advfirewall firewall add rule name="SQL Server" dir=in action=allow protocol=TCP localport=1433@echo Enabling Dedicated Admin Connection port 1434netsh advfirewall firewall add rule name="SQL Admin Connection" dir=in action=allow protocol=TCP localport=1434@echo Enabling Conventional SQL Server Service Broker port 4022netsh advfirewall firewall add rule name="SQL Service Broker" dir=in action=allow protocol=TCP localport=4022@echo Enabling Transact SQL/RPC port 135netsh advfirewall firewall add rule name="SQL Debugger/RPC" dir=in action=allow protocol=TCP localport=135@echo =========  Analysis Services Ports  ==============@echo Enabling SSAS Default Instance port 2383netsh advfirewall firewall add rule name="Analysis Services" dir=in action=allow protocol=TCP localport=2383@echo Enabling SQL Server Browser Service port 2382netsh advfirewall firewall add rule name="SQL Browser" dir=in action=allow protocol=TCP localport=2382@echo =========  Misc Applications  ==============@echo Enabling HTTP port 80netsh advfirewall firewall add rule name="HTTP" dir=in action=allow protocol=TCP localport=80@echo Enabling SSL port 443netsh advfirewall firewall add rule name="SSL" dir=in action=allow protocol=TCP localport=443@echo Enabling port for SQL Server Browser Service's 'Browse' Buttonnetsh advfirewall firewall add rule name="SQL Browser" dir=in action=allow protocol=UDP localport=1434@echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK)netsh firewall set multicastbroadcastresponse ENABLE