SQL Server security test checklist

SQL Server security should be tested regulary. Here is a security checklist by expert Kevin Beaver on how to find vulnerabilities before an intruder does.

In a recent tip, I outlined various hardening techniques for those who need to stay on Microsoft SQL Server 2000, rather than upgrade to the security-enhanced SQL Server 2005. I introduced some security assessment practices, but given the tools and methodologies involved, this is a topic that deserves a closer look. The following table of contents will help you navigate this security test checklist.

TABLE OF CONTENTS
   Security testing methods
   Essential security tools
   Checklist: How to test SQL Server security
   Conclusion

  Security testing methods  Return to Table of Contents

manual assessment like this
  • Windows security settings involving file permissions, network access, etc.
  • Default SQL Server stored procedures that aren't needed
  • SQL Server services account privileges (i.e., regular user privileges)
  • Type of authentication used (i.e., Windows versus mixed mode)
  • Audit logging settings
  • Physical and logical placement of the server relative to your Web and/or application servers (i.e., at least behind a firewall if not in an isolated DMZ)

In addition, Microsoft has a solid checklist for SQL Server 2000 security and resources for securing SQL Server 2005.

There are two main ways to assess the security of SQL Server. The first is a , which is essentially a trusted look at configuration settings while sitting down, logged onto the box. This would literally be walking through a hardening-type checklist, , to compare best practices on how it's actually set up. It's similar to an audit. Settings to look for in such an audit would include:

The second way to assess SQL Server is to run various security assessment tools against the underlying Windows OS and the actual database system itself. There are tools dedicated to both areas and each is essential. Running software on a secure OS is a key component of information security. This is especially true given how critical most SQL Server-based systems are in most organizations.

You can perform "automated" tests in two different ways. The first approach is to observe it as an unauthenticated outsider that should not have access to the system. This provides a true hacker's-eye view of areas that can be penetrated and exploited without authorized access. The other way to use automated assessment tools would be as an authenticated user, one with legitimate Windows and/or SQL Server accounts). This way you observe it with a trusted insider's view of exploitable security vulnerabilities. Both methods for running your security tests are equally beneficial, and most security tools support both options.

  Essential security tools  Return to Table of Contents

Tools that can assess the security of Windows and SQL Server are outlined in the following table:

Tool

Windows focused

SQL Server focused

Absinthe automated SQL injection tool

-

X

AppDetective for Microsoft SQL Servervulnerability assessment tool

-

X

Automagic SQL Injector automated SQL injection tool

-

X

ForceSQL password cracking tool

-

X

GFI LANguard Network Security Scanner vulnerability assessment tool

X

-

LanSpy Windows enumeration tool

X

-

NGSSquirrel for SQL Server vulnerability assessment tool

-

X

Metasploit penetration testing/exploit tool

X

X

Microsoft Baseline Security Analyzer (MBSA) security configuration assessment tool

X

X

QualysGuard vulnerability assessment tool

X

X

discovery and password cracking tool

-

X

SuperScan port scanner and Windows enumeration tool

X

-

WebInspect SQL Injector automated SQL injection tool

-

X


Table: Security assessment tools for SQL Server running on Windows

These tools can root out everything from Windows weaknesses, such as improper share permissions and missing patches, to SQL Server vulnerabilities, such as weak passwords, privilege escalation issues and improper table permissions. Combined there's hardly any weakness or exploit that cannot be discovered.

  Checklist: How to test SQL Server security  Return to Table of Contents
 Checklist: SQL Server security testing
Once you understand the necessary methodologies and acquire the right tools, it's time to start poking and prodding around to see which vulnerabilities exist. The following is a checklist of specific tests to perform on Windows and SQL Server to look for trouble:
Perform an external reconnaissance
Look at both Windows and SQL Server as an unauthenticated outsider connected to your internal network -- or, heaven forbid, from the public Internet. Tools that do this include Foundstone Inc.'s SuperScan, Application Security Inc.'s AppDetective, and SQLPing (About SQLSecurity.com). Use these tools to:
  • Find out where SQL Server exists. It sounds obvious, but check for SQL Server on non-standard ports and via MSDE -- you may be surprised to see what's out there.
  • Determine how the system (Windows and SQL Server) responds to port scans, authentication attempts and database queries.
    • Which ports are open?
    • Are the standard NetBIOS ports (135-139 and 445) and SQL Server ports (1433 and 1434) accessible?
    • Can you authenticate to Windows? SQL Server?
    • Can a null session connection be made?
    • Can a database connection be made?
  • Perform an external vulnerability scan
    Run this scan on the systems you discover. Tools include Qualys Inc.'s QualysGuard, AppDetective, NGS Software's NGSSquirrel for SQL Server, Absinthe and Metasploit. Use these tools to find:
  • SQL Server configuration issues (i.e., no sa password, missing patches and so on)
  • SQL Server vulnerabilities, such as LPC port request buffer overflow vulnerability, named pipe hijacking and denial-of-service vulnerabilities
  • Connections that can be made using sa or other accounts that have weak or non-existent passwords
  • Third-party vulnerabilities that can lead to denial of service or other exploitations, such as anonymous FTP weaknesses, Simple Network Management Protocol (SNMP) enabled with defaults, missing virtual network computing (VNC) patches, etc.
  • SQL injection (both regular and blind) susceptibility via your front-end application
  • Windows and SQL Server vulnerabilities that can be exploited using Metasploit, such as the Microsoft PnP buffer overflow (a.k.a. CVE-2005-1983) and the SQL Server 2000 hello buffer overflow (a.k.a. CVE-2002-1123)
  • Perform an internal vulnerability scan
    Run this scan on the systems you discover. Tools include NGSSquirrel for SQL Server, AppDetective and MBSA. Use these tools to find:
  • Accounts with weak or no passwords
  • If the C2 audit mode is enabled
  • Cleartext passwords that exist from a previous upgrade or installation
  • If "allow updates" is set to 0, which allows random unaccountable updates to the data dictionary via the DBA
  • Data Transformation Services tables being viewed by any unauthenticated database user
  • Any other SQL Server configuration vulnerabilities that were previously hidden (odds are you'll find some)
  •   Conclusion  Return to Table of Contents

    Many of these weaknesses can be discovered through manual assessment and audit, particularly by an experienced DBA who knows security. But that's really only half the story. You need to look at your SQL Server systems from different perspectives. This can only be accomplished effectively with the proper testing methodologies and tools.

    More information from SearchSQLServer.com

  • Fast Guide: Controlling access to SQL Server
  • Learning Guide: SQL Server security
  • This checklist isn't meant to be exhaustive but rather to prove a point that you need to test the right way to uncover various vulnerabilities. In addition, many network administrators and DBAs overlook the value of not only utilizing SQL Server-specific tools but also tools to help find vulnerabilities in the underlying Windows OS.

    Once you perform your initial tests, it's important to make these assessments a regular part of your job responsibilities and perform them periodically, such as quarterly or after any system patches, upgrades or other changes. If you don't have the time or inclination, at least hire an outside expert to help you with these tasks.

    Looking down the road, if you're going to be the primary security assessor in your organization, another project you should complete is setting up a database security testing environment. It can take some time getting good at these tools and techniques, so I highly recommend you have a SQL Server test environment that you can pound on without worrying about affecting production systems. For starters, you can download Microsoft's free SQL Server 2005 Express Edition. It is very limited compared to SQL Server 2005, but it does have basic functionality that you can test. However, you really need to set up your test environment as close to your production environment as possible.

    Consider installing a fully loaded test system running SQL Server 2000 or 2005 if you can justify the costs. That, combined with your actual database tables, will allow you to have a true apples-to-apples setup that will give you a better picture of how your tools work and how your environment operates. Just be careful if you load production data on your test system. This can pose unnecessary risks to sensitive information. At least sanitize any personal information or intellectual property beforehand to keep prying eyes from turning up anything sensitive that may be held against you in the future.

    About the author: Kevin Beaver is an independent information security consultant, author and speaker with Atlanta-based Principle Logic LLC. He has more than 17 years of experience in IT and specializes in performing information security assessments. Beaver has written five books, including Hacking For Dummies (John Wiley & Sons, Inc.), the brand new Hacking Wireless Networks For Dummies and The Practical Guide to HIPAA Privacy and Security Compliance (Auerbach Publications). He can be reached at kbeaver@principlelogic.com.


  • Tip: Not upgrading? Keep SQL Server 2000 secure

  • This was last published in December 2005

    Dig Deeper on SQL Server Security

    Start the conversation

    Send me notifications when other members comment.

    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

    Please create a username to comment.

    -ADS BY GOOGLE

    SearchBusinessAnalytics

    SearchDataCenter

    SearchDataManagement

    SearchAWS

    SearchOracle

    SearchContentManagement

    SearchWindowsServer

    Close