SQL Server security settings using the Surface Area Configuration tool

By:   |   Updated: 2009-01-22   |   Comments   |   Related: 1 | 2 | 3 | 4 | More > 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.

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


Last Updated: 2009-01-22


get scripts

next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools