The simple Lockdown.sql T-SQL script 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. Although some DBAs typically lock down security holes as they emerge, Lockdown.sql secures all vulnerabilities and requires you to specifically open up functionality that might not automatically be available because it introduces a security risk. (To access Chip Andrews’ Lockdown SQL and other free SQL Server utilities, go to http://www.sqlsecurity.com.)
The latest release of Lockdown.sql supports named instances, doesn’t break future service pack and hotfix installations, and locks down rarely used functionality. It strives not to break common application features. You can easily invoke the utility from the command prompt for mass distribution.
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. To enable any of the features that Lockdown.sql disables, simply add the functionality back by removing or commenting out the lines of code that you don’t want, if your application requires it.
Although some best practices documents have encouraged DBAs to remove unnecessary extended stored procedures, Lockdown.sql instead disables extended stored procedure permissions that represent a security risk 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, attackers 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.