With SQL Server 2005, you can manually configure it to balance security and functionality. However, if you want...
to ensure you cover all of the security bases and only enable services you absolutely need (thus minimizing the attack "surface"), Microsoft has made it simple for you. I'm talking about the SQL Server Surface Area Configuration tool that's built right into SQL Server 2005 and SQL Server 2005 Express Edition -- the freebie successor to Microsoft Database Engine (MSDE).
You can access Surface Area Configuration via Start/Programs/Microsoft SQL Server 2005/Configuration Tools or simply click the Surface Area Configuration tool link on the final setup window during installation as shown in Figure 1 below.
Figure 1: Microsoft SQL Server 2005 Setup
Once the tool loads, you have two main configuration options as shown in Figure 2 below:
1. Surface Area Configuration for Services and Connections
2. Surface Area Configuration for Features
Figure 2: SQL Server 2005 Surface Area Configuration
You make these changes on the local host or on a remote system, which doesn't seem too intuitive given the default setting to disallow network connections. The default is the local host, but you can select a remote system via the [change computer] link as shown in Figure 3.
Figure 3: Surface Area Configuration for Services and Connections
The Configuration for Services and Connections option allows you to manage Database Engine and Browser services and more, depending on your SQL Server edition, as well as network connections as shown in the following configuration screen for a server running SQL Server Express.
The Configuration for Features option allows you to configure options for Service Broker, xp_cmdshell, and more as shown in the following figure.
Figure 4: Surface Area Configuration for Features
There's also the SAC command line tool used to export a hardened SQL Server 2005 configuration and import it on other servers without having to repeat the configuration steps. This tool (sac.exe) is located in \Program Files\Microsoft SQL Server\90\Shared and its command-line switches are shown in Figure 5 below.
Figure 5: Command prompt
Keep in mind that previous SQL Server versions will retain their settings when upgraded to SQL Server 2005, so running the Surface Area Configuration tool is a must.
An even more important step in locking down your SQL Server 2005 installation is to test it from a bad guy's point of view. You can try to crack SQL Server passwords, look for stored procedure weaknesses, root out buffer overflows and more.
No matter what tools, techniques or checklists you use to lock down a system, odds are that a serious vulnerability or two will still exist. The Microsoft Baseline Security Analyzer (MBSA) tool won't cut it now, since it currently doesn't support SQL Server 2005, or in the future since it's not a robust security testing tool. The best way to get a tried-and-true external look at security holes that may still exist is to use security testing tools such as Qualys' QualysGuard and Application Security's AppDetective and/or their commercial equivalents.
About the author: Kevin Beaver is an independent information security consultant, author and speaker with Atlanta-based Principle Logic LLC. He has more than 18 years of experience in IT and specializes in performing information security assessments. Beaver has written five books including Hacking For Dummies (Wiley), Hacking Wireless Networks For Dummies, and The Practical Guide to HIPAA Privacy and Security Compliance (Auerbach). He can be reached at email@example.com.
More information from SearchSQLServer.com
SQL Server 2016 allows you to Configure SQL Server at database level