Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to setup security features for SQL Server


By:   |   Last Updated: 2009-01-15   |   Comments   |   Related Tips: More > 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:

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



Last Updated: 2009-01-15


get scripts

next tip button



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

View all my tips
Related Resources




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.



    



Learn more about SQL Server tools