SQL Server Magazine

SQL
Server enthusiast and security architect Chip Andrews was frustrated by the need to repeatedly button up the security holes he found at client installations. Because Enterprise Manager can be cumbersome for modifying the configuration of multiple servers—and Chip typically worked with large numbers of servers—he wanted to ensure a fast, effective way to reduce the security risks of a straight SQL Server 2000 installation. Chip’s Web site (http://www.sqlsecurity.com) hosts a variety of free SQL Server security utilities and links to other recommended Windows security utilities. But the most useful utility on the site isn’t an executable; it’s the simple, tiny Lockdown.sql T-SQL script.

Functionality
Lockdown.sql configures a SQL Server 2000 instance to the most secure baseline configuration possible. From this point, the DBA can simply enable the functionality needed for that instance. When using this script, a DBA’s security mindset requires the application of a bit of reverse psychology. The average DBA is used to locking down security holes as they emerge. In contrast, Lockdown.sql secures all vulnerabilities and requires you to open up functionality that might not automatically be available because it introduces a security risk.

The latest release of Lockdown.sql supports named instances, doesn’t break future service pack and hotfix installations, and locks down rarely used functionality but strives not to break common application features. You can easily invoke the utility from the command prompt for mass distribution.

What Does Lockdown.sql Secure?
Although Lockdown.sql is a simple T-SQL script, it changes many default configuration settings on a SQL Server 2000 instance. When you execute it, the script automatically

• determines whether the SQL Server service account for LocalSystem Authority is allowed.
• confirms the latest service packs and hotfixes.
• enables Windows Authentication as the only login method.
• sets a strong sa account password consisting of two concatenated unique identifiers.
• enables full logon auditing to monitor successful and failed SQL Server access.
• disables SQL Server Agent, Microsoft Distributed Transaction Coordinator (MSDTC), and MSSEARCH services.
• disables ad hoc queries for all data providers in accordance with the “minimal surface area” best practice.
• removes the Pubs and Northwind sample databases.
• tightens permissions on many system stored procedures and extended stored procedures, including SQL Server Agent job system stored procedures, Web tasks, table permissions, DTS package table permissions, and extended stored procedures.
• revokes permissions of the guest account to MSDB.
• disables remote access.
• ensures that system tables can’t be accessed.
• increases the SQL Server log history capacity for better auditing and reporting.
• removes lingering SQL Server setup files.

Some of these lockdown measures might at first seem too strong, but the script doesn’t break most applications. If you want to enable any of the features that Lockdown.sql disables, you can simply add the functionality back by removing or commenting out the lines of code that you don’t want, if your application requires it.

Note that although some best practices documents have encouraged DBAs to remove unnecessary extended stored procedures, Lockdown.sql doesn’t do this. Instead, the utility disables extended stored procedure permissions that represent a security risk. Chip took this approach for several reasons. First, removing extended stored procedures can cause problems with service packs and hotfixes during installation and can cause problems with useful tools such as Enterprise Manager. Preventing access by non-sysadmin users is more effectively and easily achieved by dropping execute permissions than by removing extended stored procedures. In addition, hackers can add back the files of dropped extended stored procedures, but they can’t alter permissions when those permissions have been explicitly denied. And DBAs can easily add privileges back, making it unnecessary to drop extended stored procedures that might later be needed for a one-off job.

How to Execute the Script—Plus Some Cautions
Executing Lockdown.sql couldn’t be easier because it’s a simple T-SQL script. I recommend that you read the entire script, which will only take a few minutes, to ensure that it’s not disabling any functions you want on your instance of SQL Server 2000. In addition, be aware that the script as written might cause errors in a case-sensitive installation. You might want to standardize the case before using the script. To execute the script from the command prompt, type:

osql -S (servername) -E -i
lockdown.sql

Note that the script doesn’t currently support SQL Server 2005 because the newest version of SQL Server has more robust security and automatically disables many of the same security vulnerabilities that Lockdown.sql does. However, Chip has said he’d like to update his script if user interest is strong enough. Chip’s Web site, which isn’t affiliated with Microsoft, offers other free SQL Server security tools and has a discussion forum that focuses exclusively on SQL Server security. I encourage you to drop Chip a note in his discussion forum or contact him directly at chip@sqlsecurity.com if you’d like to see a new SQL Server 2005 version of the script. And be sure to visit the Tool Time forum online to comment on this column and post your own tool recommendations!

End of Article