There are several steps you can take to make SQL Server databases more resistant to tampering and hacking. Some are simply part of good server management, like keeping up with SQL Server's latest patches, while others involve active-user monitoring. The five steps below will get you started.
Check for latest service packs
Every so often make sure you have the latest service pack. For SQL Server 2000, it is SP3a. Keep in mind that service packs are cumulative; if you apply SP3a you don't need to apply any of the packs that came before it, such as SP3, SP2 or SP1. SP3a is a special service pack meant for installations that didn't apply any previous updates, whereas SP3 is for installations that have installed either SP1 or SP2.
Sign up for security alerts
While patches help protect your SQL Server databases against many threats they aren't posted quickly enough to handle fast-moving security issues like the Spammer worm. You'll want to sign up for Microsoft's free Security Notification Service, an e-mail service that will let you know about breaking security issues and how to deal with them.
Run Microsoft Baseline Security Analyzer (MBSA)
This tool is available for both SQL Server and MSDE 2000 Desktop Engine, and it can be run either locally or over a network. It looks for problems with passwords, access rights, access control lists (ACLs) and the registry, and it checks for missing security packs or service packs. You'll find information on this tool at TechNet.
Delete SA and old passwords
The single biggest security mistake people make with passwords is to leave the system administrator (SA) password unchanged. You may easily overlook installation files with leftover configuration information, poorly-protected authentication information and other sensitive data that can be hacked. You should delete old setup files here: Program Files\Microsoft SQL Server\MSSQL\Install or Program Files\Microsoft SQL Server\MSSQL$<instancename>\Install folders. Also, use the KILLPWD utility to find old passwords and remove them. Knowledge Base article 263968 details this issue.
Connections tell who is trying to access SQL Server, so monitoring and controlling connections is a particularly good way to secure the database. For a large active SQL Server database, there's probably too much connection data to monitor, but it's really valuable to monitor the failed connections because they may represent exploit attempts. You can log failed connections in the Enterprise Manager by right clicking on the server group and then selecting Properties. Then click the Security tab and under Audit Level click Failure to stop and restart the service.
For more advice, a good place to find database security resources is at Microsoft's SQL Server Security.
Barrie Sosinsky is president of
consulting company Sosinsky and Associates (Medfield, Mass.). He has written extensively on a
variety of computer topics. His company specializes in custom software (database and Web related),
training and technical documentation.
This was first published in January 2005