By: K. Brian Kelley | Comments | Related: 1 | 2 | 3 | 4 | > Surface Area Configuration Manager
Problem
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?
Solution
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.
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:
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.
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.
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:
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:
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
- Stay tuned for a tip covering the details of the SQL Server Surface Area Configuration for Features option.
- Review the introduction to the SQL Server Surface Area Configuration tool.
- Stay tuned for how to configure SQL Server's network settings using SQL Server Configuration Manager.
- Read the tip on how to configure xp_cmdshell in SQL Server 2005 using SQL Server Surface Area Configuration.
- Check out these related tips on MSSQLTips.com:
About the author
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
