How to setup security features for SQL Server

By:   |   Comments   |   Related: > SQL Server Configurations


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?


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:

SQL Server Surface Area Configuration initial screen

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:

Select Computer dialog window

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:

Surface Area Configuration for Services and Connections

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:

Surface Area Configuration for Features

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