By: K. Brian Kelley | Updated: 2009-01-15 | Comments | Related: > SQL Server Configurations
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
With the Microsoft SQL Server 2005 client tools installation is the SQL Server Surface Area Configuration tool. The surface area for a system or application is whatever is accessible. For instance, if SQL Server is listening on TCP/IP, that's part of the surface area. You can find it by navigating to Start | All Programs | Microsoft SQL Server 2005 | Configuration. When you first start up SQL Server Surface Area Configuration, you should see a screen similar to:
For a normal SQL Server, the options you will probably be interested in are highlighted with the red box. They are:
- Change Computer
- Surface Area Configuration for Services and Connections
- Surface Area Configuration for Features
Besides the Change Computer option the tool will tell you what SQL Server's configuration you're currently modifying. If you need to configure a different computer, for instance, you're running the tool from your workstation and you need to alter the surface area of a server, click on the change computer link. It should bring up a simple interface like so:
Once you have the correct computer selected, you'll want to configure what services are running and how you'll be able to connect to the SQL Server. This can be done by clicking on the Surface Area Configuration for Services and Connections option. This will bring up an interface which will show all the SQL Server-related services installed on the computer:
- Database Engine (the traditional component for SQL Server)
- Analysis Services
- Reporting Services
- Integration Services
- Full-Text Search
- SQL Server Agent
- SQL Server Browser
You can view these components by SQL Server instance or by the components individually:
You can set the start-up options for each component as well as basic options with respect to connecting to that component. For instance, under Database Engine | Remote Connections, you can configure whether or not to listen on Named Pipes and/or TCP/IP. For more detailed configuration, you'll need to go to SQL Server Configuration Manager.
The other set of options is contained in the Surface Area Configuration for Features. This interface allows you to configure particular options with respect to the surface area such as whether or not allow Ad Hoc Remote Queries, .NET Assembles (via CLR Integration), and the like:
Next Steps
- Stay tuned to further tips covering the details of 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
Article Last Updated: 2009-01-15
