SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Security Basics: Understanding the Surface Area

When it comes to securing a system, it's important to understand how it might be attacked. That's what surface area is all about. The surface area is the parts of the system which are exposed. For instance, in the case of a default instance of SQL Server 2005 or 2008, we typically see two TCP ports open:

  • TCP port 1433 open to all network traffic.
  • TCP port 1434 (the DAC) open only to traffic originating from the server where SQL Server is running.  

We also typically see the Shared Memory protocol enabled, meaning an attack is possible if you can get on the server itself. Since the DAC is certain limitations as to what can be run (but it also allows access to things that you can't get to with a normal connection), the connection using TCP/1433 or Shared Memory might be preferable. And in some cases we see Named Pipes on as well. If that's the case, this protocol can be used as well. All of this can be checked with the SQL Server Configuration Manager. And all of this is part of a SQL Server's surface area. If you're in charge of administering a SQL Server and you've never checked all of this out, there's no time like the present. And depending on what other features you have enabled, you might see more (such as a Service Broker or HTTP endpoint which exposes more TCP ports).

But what about once you establish a successful connection? What then? I've already mentioned Service Broker and HTTP endpoints, which can expose even more access to SQL Server. Within the context of SQL Server there is the ability to enable other features which increase the surface area. For instance, you can flag DAC so it's also available remotely. That means I don't have to compromise the server SQL Server is running on any longer to attempt to connect to the DAC. I can try and hit it remotely. You can allow certain features like OLE automation or access to xp_cmdshell, which are not normally accessible to any users, not even member of the sysadmin fixed server role (though they certainly have the capability of turning these on). It's important to know what features are on and which are off. In SQL Server 2005 there's SQL Server Surface Area Configuration (SAC) but in 2008 the tool is no longer present. Instead you've got to use Policy Management with the settings you care about. The advantage in 2008 is SQL Server can enforce these settings. SAC can read when you start it up and it can apply changes to settings, but it's not a continual monitoring type of tool. That's the reason 2008 uses Policy Management to maintain surface area configuration.

The point of all of this is to understand how an attacker might get into your system. the starting point is understanding the surface area: what's on and what's not. You need that to go to the next step, which is figuring out how an attacker might use an exposed interface or feature to attack said system. For instance, once I've determined that SQL Server is up on TCP 1433, how might the attacker go about using that? If there's a web server in the DMZ which connects to that SQL Server, then by compromising the web application I may be able to come in on that port. That's the attack. But since I know what's exposed and a potential attack, I can think of appropriate defenses. For instance, I can have IDS/IPS looking at the network traffic for certain key things where that web server talks to that SQL Server on TCP 1433. If I see xp_cmdshell, I know that's not right. And I can flag it and do something about it. But it all starts with me understanding the surface area.


K. Brian Kelley - Databases, Infrastructure, and Security

IT Security, MySQL, Perl, SQL Server, and Windows technologies.


Posted by Steve Jones on 2 June 2009

Excellent post, and good to see some examples of how these items can be compromised. Good reference to give to sysadmins when they are wondering how to configure the firewall, or be aware of what's open.

Posted by ta.bu.shi.da.yu on 10 June 2009

Just be careful that you make sure you don't turn off anything you actually need!!!!!

Posted by pdooley on 10 June 2009

I'm alwasy concerned when I see an article like this. Not that they aren't important but The Miceosoft world seems to think that security is about building walls instead of the true basics of security which are knowing who has access and making sure that they have a business reason for it. Security is about managing not guarding.

Posted by K. Brian Kelley on 10 June 2009

pdooley, I would disagree with you that this is a Microsoft world thing.

Security best practices say if you don't need it, make sure it's not installed. If it can't be uninstalled, at least disable it. This has been one of the claims made of the Linux side not only with respect to performance, but also security. When we're talking surface area, especially with respect to SQL Server, that's exactly what we're doing. We can't uninstall xp_cmdshell. But we can ensure it's deactivated.

And to further that argument, would you seriously consider putting LAMP servers on the Internet without a firewall in place? And that would include software firewall solutions such as ipchains. If so, I know a lot of well-respected security folks who would disagree with your opinion.

Posted by John Scarborough on 11 June 2009

Ahh, short, sweet, and to-the-point.  My favorite kind of article!

Leave a Comment

Please register or log in to leave a comment.