Is 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,
InstantDoc ID 50027, 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 at http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec04.mspx.
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 (http://www.sqlmag.com, InstantDoc ID
94185) 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).
Prev. page
[1]
2
next page 
