Surface Area Configuration for SQL Server

The Macmillan dictionary defines surface area as the total area of a surface or surfaces, especially the outside surfaces of an object.  If we apply this definition to the world of IT security, it refers to the amount of exposure a system has to potential attackers.  In Microsoft SQL Server, there are a number of services, ports, and connections that can be configured to control how much “surface area” is exposed. 

Microsoft SQL Server 2005 (including SQL Server Express) includes a configuration tool called SQL Server 2005 Surface Area Configuration.  In SQL Server 2008, this tool has been incorporated into the SQL Server Configuration Manager.  The idea behind either tool is to give administrators a centralized way to configure which services and connections are available in the different instances of Microsoft SQL Server.

For instance, the SQL Server Surface Area Configuration tool allows administrators to control connection access as Local connections only, or Local and Remote connections.  This seems reasonable, and beneficial if you’re using the database as localized storage or as a computation engine.  But if you’re setting up client-server software like PLM, ERP, or DA, and you can only seem to make the system work from the server, you may not immediately consider surface area configuration as the culprit (many people would suspect the network first).  Surface area configuration tools also allow you to control which network protocols (TCP/IP, Named Pipes, or both) are available for remote connections also.

Another common problem with improper surface area configuration can occur if the SQL Server Browser (SQLBrowser) service is not enabled.  The SQL Server Browser service helps with locating available servers on the network, connecting to the correct server instance, and connecting to dedicated administrator connection endpoints.  This service is especially important if there are multiple instances of SQL Server on the same machine.  It helps direct client connections to the correct instance and port.  Without the SQL Server Browser, you must know the correct port number or named pipe to be able to connect to the correct SQL Server instance.  This manifests itself when you are trying to make a connection to the SQL Server and the dropdown list of available servers is empty.  For security purposes, you may want to disable the SQL Server Browser service, but you need to be aware of the ramifications.  The disadvantage of doing this is that configuring connections to the databases gets a little trickier.  When setting up systems like PLM, ERP, and DA, this is another potential stumbling block since you may immediately suspect network connectivity as the problem when security (surface area configuration) is actually the culprit.

While it is important to be cognizant of the “surface area” that your applications, like SQL Server, present to the outside world, you should also be aware of how surface area that is controlled too tightly can create problems.  If you have questions about SQL Server surface area configuration, and how it might relate to your PLM, ERP, or DA systems, please contact us – we maintain significant expertise in Microsoft SQL Server.

Tags: Microsoft SQL Server, SQL Server 2005, SQL Server 2008, SQL Server Configuration Manager, surface area configuration