SQL Server security settings using the Surface Area Configuration tool

By:   |   Comments   |   Related: 1 | 2 | 3 | 4 | > Surface Area Configuration Manager


I have been put in charge of securing our SQL Server 2005 servers. Are there any tools available that can help me get started? Are any tools available to secure SQL Server independent of the logins and users that I have in place? Do I need to manage SQL Server on each machine or can I do so from a single application?


In a previous tip, How to setup security features for SQL Server, we introduced the SQL Server Surface Area Configuration tool. In this tip we'll expand on the portion of that tool called Surface Area Configuration for Services and Connections. There are two ways to view the various services and connections on a given server. The first is by instance, and this is usually the easiest method.

View Services By Instance

However, if you have multiple components on the same server, such as multiple instances of the database engine, it may be easier to group by component, which is what the second tab (View by Component) is for:

View By Component

Once you've settled on your view, the next step is to actually configure each component. Every component listed will have a Service option where you can configure the startup status for the service as well as control the current state of the service itself. For instance, the following image is of the MSSQLSERVER database engine service, which is set to Automatic, meaning it will start when the computer starts up.

Service Settings

You have the ability to set any of the following startup types:

  • Automatic - The service will start when the computer starts up.
  • Manual - The service is capable of being started, but must be started manually by an authorized user (Power Users or Administrators local group membership).
  • Disabled - The service cannot be started unless the startup type is changed.

In addition to the service configurations, the Database Engine and Analysis Services have additional options. For the database engine, there is the Remote Connections configuration.

Database Engine - Remote Connections

As the highlighted section shows, you can configure the database engine either to listen only for local connections (originating from the same computer as SQL Server) or to listen for both local and remote connections. In addition, if you choose for SQL Server to listen for remote connections as well, you'll have the choice whether to use TCP/IP, Named Pipes, or both. In general, for network connections, you'll want to use TCP/IP. In my experience, Named Pipes sometimes suffers from timeout issues that you don't get with TCP/IP. Be aware that if you want more control over the network settings for your SQL Server, you'll want to use the SQL Server Configuration Manager tool, which will be covered in another tip.

Just as the database engine can support both local and remote connections, so can Analysis Services:

Analysis Services - Connections

Again, you have the option of having Analysis Services listen only for local connections as well as listen for both local and remote connections. However, the Surface Area Configuration tool can only determine what the current settings are if the service is started. If the service isn't started, you'll see the following message:

Analysis Services - Not Started Message

As far as additional network configuration, unfortunately this is not handled by either the SQL Server Surface Area Configuration tool or the SQL Server Configuration Manager. The easiest way to do so is through SQL Server Management Studio, but that's beyond the scope of this tip.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

get free sql tips
agree to terms