Tip

How to improve SQL Server security

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

Requires Free Membership to View

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.

Monitor connections
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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.