Skip navigation

Automate Your Security Audits

Stored procedure tracks 15 commonly monitored checkpoints

Download the Code iconIs your database secure? Hardly a week passes without the announcement of some new SQL vulnerability. What’s a SQL Server DBA to do?

Fortunately, you can take actions that will greatly lessen your risks. Many SQL attacks can be prevented (or at least mitigated) by simply verifying up front that your databases and servers are properly secured. Are all listening ports correctly configured? Have you disabled all unnecessary network libraries? Did you remember to delete those old SQL Server setup files? The list goes on.

I admit, it takes time to secure every last checkpoint and stay ahead of the bad guys. New security vulnerabilities are constantly appearing, which means adding new checks and frequent monitoring to stay safe.

Wouldn’t it be great if you could somehow automate all these time-consuming auditing chores? Well, now you can if you apply the easy-to-use techniques described in this article and customize the included code for your situation.

Start with a Checklist

In "Build Your Own Automated Security Systems," June 2006, I show how to test a database configuration for security vulnerabilities. You start with a list of security policies and inspect your DBMS for potential policy violations, design vulnerabilities, and other security soft spots a hacker can exploit.

The June article presents a table that lists a number of security settings you might want to audit. You’ll also find these settings—and more—in the SQL Server 2000 SP3 Security Features and Best Practices: Security Best Practices Checklist.

You’ll want to tailor your testing to those checkpoints with the highest priority in your area of responsibility. The security checklist helps in this tailoring process by categorizing its checkpoints.

If your job is installing databases, you’ll probably want to focus on those security requirements in the preinstallation, installation, and post-installation categories in the administrator section of the checklist. If you’re an application developer, you’ll naturally gravitate to development requirements such as data encryption and database roles.

Whatever the vulnerabilities you focus on, you’ll want your security audits to be repeatable and easily updatable. After all, what good is a security test if it’s run only once or fails to keep up with new attack strategies? Automation is the key to timely and repeatable security auditing.

Automate the Checks

My June article shows the beginnings of a stored procedure, upCheckSecurityConfiguration, that can be built upon to automate your security checks and log any problems you find. The code at callout A in Listing 1 shows how upCheckSecurityConfiguration checks whether the SQL Server account is using LocalSystem authority. The Microsoft security checklist recommends a separate Windows account for each SQL Server service. So upCheckSecurityConfiguration flags a security violation when it finds a LocalSystem value in the HKEY_LOCAL_MACHINESYSTEM\CurrentControlSet\ServicesMSSQLSERVER registry subkey. To log this test result, upCheckSecurityConfiguration calls another procedure, up_InsertTestCase, which Web Listing 1 shows. up_InsertTestCase inserts the result into table tabConfigSettings (Web Listing 2). The final piece of the test-and-report framework is a trigger on tabConfig-Settings. trgLogBug (Web Listing 3) checks whether a security violation was found. If it was, trgLogBug inserts a problem report into tabBugReports (Web Listing 4).

Extend upCheckSecurityConfiguration

One neat feature of upCheckSecurityConfiguration is that you can add test cases for any number of configuration settings you want to check. Once you have a test suite that meets your needs, you can fold the corresponding test cases into upCheckSecurityConfiguration. From there, you’re free to run—and update—your entire test suite whenever you want.

Say you’d like to audit your configuration at the beginning and close of every business day. Or maybe launch your audit procedure whenever some suspicious event—such as an illegal login attempt—occurs. Simply fold the desired security checks into upCheckSecurityConfiguration and then create a SQL Agent job (or alert) that fires when you need it. In fact, while you’re at it, why not create a separate procedure for each security audit you want to run—each custom designed to the specific security checks you need?

Whichever strategy you choose, you’ll need to adapt upCheckSecurityConfiguration to fit your specific needs. To demonstrate how you might do that, I’ve extended upCheckSecurityConfiguration to include 15 commonly audited security settings from the Microsoft security checklist. Listing 1 shows the upCheckSecurity-Configuration checks. You can download the complete upCheckSecurityConfiguration procedure and the other code at the top of this article by clicking the "Download the Code" link.

The nice thing about upCheckSecurity-Configuration is that all your tests are centrally located. Need to modify one of your checks or add a new security test? Just edit upCheckSecurityConfiguration, recompile, and away you go. It doesn’t get much easier than that.

If you don’t already have a test database, I suggest you create one to host upCheck-SecurityConfiguration and its support tables along with trgLogBug. You’ll also want to consider who should be given access to these resources and set access privileges accordingly.

Audit Smartly

This article has shown you how to automate your security audits and do away with the drudgery of manually verifying that your databases are correctly configured. The key is to treat your audits as configuration tests and verify each security checkpoint just as you would if you were looking for other kinds of bugs in your database.

To monitor security vulnerabilities in this way, you need an up-to-date security testing document detailing your auditing schedule, the settings you’ll check, and the reporting procedures you’ll follow. If your organization currently has no security plan, then you should do what you can to get one created. Start with the Microsoft security checklist to see which security requirements make sense in your organization. Then convince your teammates to commit to a standard set of security auditing procedures. Most of all, be sure to document your results so everyone can see the difference audit automation can make.

Audit automation is an invaluable addition to any DBA professional’s toolkit. But before arbitrarily automating every security check you make, be sure the payoff is worth the setup involved. Your guiding rule here should be to focus on those mission-critical checks you find yourself regularly repeating. You’ll rest easy at night knowing your time has been well spent protecting your organization’s most vital data assets.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.