Lockdown.sql - 18 Dec 2006

SQL

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

Functionality

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

The latest release of Lockdown.sql supports named instances, doesn’t break futureservice pack and hotfix installations, and locks down rarely used functionality but strivesnot to break common application features. You can easily invoke the utility from thecommand 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 scriptautomatically

Some of these lockdown measures mightat first seem too strong, but the scriptdoesn’t break most applications. If youwant to enable any of the features thatLockdown.sql disables, you can simplyadd 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 DBAsto remove unnecessary extended storedprocedures, Lockdown.sql doesn’t do this.Instead, the utility disables extended storedprocedure permissions that represent a security risk. Chip took this approach for severalreasons. First, removing extended storedprocedures can cause problems with servicepacks and hotfixes during installation andcan cause problems with useful tools suchas Enterprise Manager. Preventing accessby non-sysadmin users is more effectivelyand easily achieved by dropping executepermissions than by removing extendedstored procedures. In addition, hackers canadd back the files of dropped extendedstored procedures, but they can’t alter permissions when those permissions have beenexplicitly denied. And DBAs can easily addprivileges back, making it unnecessary to drop extended stored procedures that mightlater be needed for a one-off job.

How to Execute the Script—PlusSome 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 thescript from the command prompt, type:

osql -S (servername) -E -ilockdown.sql

Note that the script doesn’t currently support SQL Server 2005 because the newestversion of SQL Server has more robustsecurity and automatically disables manyof the same security vulnerabilities thatLockdown.sql does. However, Chip has saidhe’d like to update his script if user interestis strong enough. Chip’s Web site, whichisn’t affiliated with Microsoft, offers otherfree SQL Server security tools and has adiscussion forum that focuses exclusivelyon SQL Server security. I encourage you todrop Chip a note in his discussion forumor contact him directly at [email protected] if you’d like to see a new SQL Server2005 version of the script. And be sure tovisit the Tool Time forum online to comment on this column and post your owntool recommendations!