Executive Summary: Occasionally, there are miscellaneous attributes, such as yes/no attributes or comment attributes, that don’t fit into tight star schemas. Rather than discarding flag fields and yes/no attributes, place them in a junk dimension. In addition, you can handle comment and open-ended text attributes by creating a text-based junk dimension.

One of the best features of SQL Server 2005 is that it’s secure by default: A pristine installation (i.e., one with the defaults largely intact) is about as secure as it will ever be. But then DBAs and developers mess up this pristine security by creating and installing databases, giving users and groups access to data, and building Web applications that indirectly give the untrusted masses access to sensitive enterprise data. Suddenly, SQL Server is a security nightmare. You can do all those things securely, of course, if you’re careful and monitor the entire attack surface. But diligent monitoring can be a full-time occupation for several people even in small enterprises, and it’s all too easy to miss obscure vulnerabilities.

That’s where tools come in. Over the last few years, dozens of tools have appeared to simplify the job of anyone concerned with the security of a SQL Server system and its databases. The tools run the gamut from free to expensive, single to general purpose, simple to complex. Although most are intended for the good guys, you can be sure that the bad guys are using the same tools to probe and poke at your servers. Over the years, I’ve found and used a number of security tools; Table 1 lists some of my favorites. Here I’ll introduce you to several tools I’ve found especially useful for boosting SQL Server security. Together, the tools perform a gamut of security-testing functions: They locate your SQL Server instances, assess your network security for conformance with best practices, crack your passwords, perform a vulnerability analysis, and keep your system updated. I’ve found the tools especially useful for finding the holes in a SQL Server instance and identifying vulnerabilities.

Before We Start…
…I’ll assume that you’re familiar with SQL Server’s built-in security tools. You should be well acquainted with the capabilities of the Surface Area Configuration tool and SQL Server Configuration Manager as well as SQL Server Management Studio (SSMS). SQL Server 2005 has some marvelous security features that you should put to use. You shouldn’t use external tools as a substitute for careful security planning. Start secure and use the tools to strengthen and fine-tune security.

I also avoid discussing SQL injection and cross-site scripting (XSS) testing tools, such as Absinthe and others. SQL injection and XSS are UI vulnerabilities, rather than direct attacks on the database server. Think about it: From the perspective of the server, how can SQL Server distinguish friendly versus malicious code? By the time the code arrives at the server, all context that could help to identify it has vanished from the database request, other than what connection it’s coming in on. SQL injection is a serious threat, but it isn’t a server vulnerability as such, so I won’t cover it here. Vulnerabilities in the server can enable these kinds of attacks, however.

Find SQL Server Instances
You can’t secure servers you don’t know about—or have forgotten to add to your audit list—and that’s where the free SQLPing 3.0 from SQLSecurity.com comes in. Ever since the SQLsnake worm appeared, we know that the SQL Server Browser service is a security vulnerability, exposing information about available database servers to attackers. As a result, finding SQL Server instances running on your network can be problematic. There are several known ways to scan and find running instances, and SQLPing takes advantage of them all.

SQLPing is easy to use, which is good since there’s no documentation for the tool beyond the description on the SQLSecurity.com Web site. You can perform active scans in which the tool actively pings the network—noisily announcing its presence—or a stealth scan where it simply searches Active Directory (AD) for any SQL Server registrations and checks the SQL Server Browser Service to see whether any servers have broadcast their existence. Active scans are more accurate and more clearly reveal activity on your SQL Server network, so in general you should use active scans. As a bonus, SQLPing will also perform dictionary and brute-force password checking. However, the brute-force password checks are less robust than those of other tools.

Figure 1 shows the results of scanning a small network. In this case, the scan successfully found all the SQL Server instances on two machines, RiverChaser and Puppy, and extracted information about them. In my experience using SQLPing, I’ve found that it almost always finds every instance when other tools, even SSMS, find nothing.

Security Best Practices
One you’ve found all the SQL Server instances on your network, it’s time to get to work evaluating how secure they are. So the very next thing you should do is run Microsoft’s SQL Server 2005 Best Practices Analyzer. This free, easy-to-use tool works surprisingly well for catching all the low-hanging fruit of security vulnerabilities.

The Best Practices Analyzer lets you select which SQL Server instances to scan on the local machine. You can scan other machines on the network, but because the tool accesses the registries and other resources, you get a better scan when you run it locally. If you do want to scan across the network, you’ll probably need to be a domain or local administrator with permissions on the remote machine’s registry. The tool has various options for selecting which components to scan for in each instance and can import or export component lists. You can also select which databases to include in the scan; the default is to scan all databases, including the system databases. The analyzer defines a large set of rules that define best practices, and you can control which rules it uses to scan a particular server.

Once you’ve set the analyzer’s options, click Scan Selected Components to start the scan. The scan can take anywhere from a couple minutes to a very long time, depending on the number of server instances and components you select. The scan checks more than 100 server and database issues related to known vulnerabilities, then produces a report similar to that in Figure 2. Each issue discovered includes a brief description, often a link to the Help file, and an option to stop checking the rule for any or all SQL Server instances for future scans, when appropriate. You can enable any rules you’ve disabled by selecting Other Reports on the View Best Practices Report page to view the Hidden Item reports. That’s not exactly an intuitive location, and I have to hunt for it every time I need it. Unfortunately, the Help documentation only tells you to open the Disable Issues list but doesn’t say how to find this nonexistent list.

The scan rules are briefly documented in the Help file under the misleading section name Microsoft SQL Server Best Practices Analyzer – Articles. Although the section doesn’t contain much detail, it briefly describes each rule and the best practices associated with each. So far, I’ve found that the best practices are indeed good advice, although sometimes you might have valid reasons to violate a practice.

The analyzer’s UI is reasonably simple, but its modified wizard interface is a bit klunky if you want to go back a step and is sometimes confusing about where to find various options. But after you’ve used the Analyzer wizard a few times, you’ll get the hang of how it works. Probably the biggest downside to the Best Practices Analyzer is that you can’t customize the predefined rules it scans for. You can tell it to ignore selected rules, but that’s the extent of the customizations you can make. Nevertheless, make it a habit to run the Best Practices Analyzer regularly to keep tabs on the most common vulnerabilities.

Another Microsoft tool you might come across is the Microsoft Baseline Security Analyzer (MBSA), which claims to support SQL Server. Although this tool is fine for general security analysis for a machine, the latest version 2.0.1 supports SQL Server 2000 only and is of limited use for SQL Server 2005 machines. MBSA’s only support for SQL Server 2005 is to make sure that you have the latest patches, which certainly is a useful feature by itself.

Cracking Passwords
Strong passwords are the foundation of a secure server. It’s a rare SQL Server instance that can get away with using Integrated Windows authentication alone, so you probably have lots of SQL Server logins with weak passwords. Many SQL Server passwordcracking tools are available, but NGSSQLCrack from Next Generation Security Software is probably the easiest to use. This is a commercial tool and costs around $500 depending on how you license it and the current exchange rate (NGSSoftware is based in England). NGSSQLCrack will connect to the SQL Server instance of your choice and grab the SQL login password hashes, or you can either enter the password hashes manually or copy them into the tool. NGSSQLCrack relies on both dictionary and brute-force attacks and provides some simple options for customizing the session. For example, you can specify your own dictionary file and specify the character set—including case-insensitive options—for the brute-force attacks. NGSSoftware’s Web site says NGSSQLCrack works with SQL Server 7.0 and 2000, but it worked just fine on SQL Server 2005 passwords for me.

Continued on page 2

It can take a long time to perform a complete crack, depending on the size of your dictionary file, the character set you select for the brute-force analysis, and the password size range you select. The tool reports any passwords it discovers immediately, as you can see in Figure 3, so you can take whatever action you want without waiting for the session to finish. Figure 3 shows the session after only a few minutes, already with a successful dictionary crack. After NGSSQLCrack had run for hours on my system, I was relieved that it still hadn’t cracked the strong passwords for sa and carol.

NGSSoftware claims that NGSSQLCrack isn’t a hacker’s tool, since you need administrative access to a machine to get the password hashes for cracking. But it’s all too easy to gain such access through applications, such as by using SQL injection. Once an attacker has cracked some of your passwords, all kinds of nasty attacks become possible. At that point, you might as well just post your data on your Web site for all the world to see.

If you want to get into industrial-strength password cracking, the tool of choice is the free, cross-platform Cain & Abel. This tool gives you many more options than NGSSQLCrack for gathering, sniffing, and cracking all kinds of passwords—from Windows and other OSs as well as SQL Server—along with much more robust cracking options. Cain & Abel is a true hacker’s tool, and you’ll probably need to spend some time figuring out the tool and learning how to use it effectively. It’s almost scary how well Cain & Abel can crack passwords, so much so that you’ll never again create a simple or short password for any use whatsoever.

The choice between NGSSQLCrack and Cain & Abel is a matter of cost and ease of use. NGSSQLCrack makes the whole cracking process easy but is expensive. Cain & Abel is free and has more power and flexibility but is also more complex and harder to learn. Overall, the results seem to be similar.

Industrial-Strength Vulnerability Analysis
Many SQL Server hacking tools are niche products, focusing on one aspect of security such as password strength or port visibility. But there are literally hundreds of potential vulnerabilities in a product as complex as SQL Server, and it would take the most diligent administrator years to find all the problems. That’s where a comprehensive, industrial-strength vulnerability scanner is a lifesaver. Many such commercial vulnerability scanners are available, most of which are general network analyzers that happen to include scans of SQL Server instances. These include commercial, opensource, and freeware products. The SQL Server–specific features of these products are often fairly insubstantial, but such products do provide a full set of tools for monitoring all interactions the server makes with the network. And often these products provide the infrastructure you need to develop custom attacks and scans.

The heavyweight entry in this group of products is the Metasploit Project. As its Web site describes it, Metasploit is an “open source platform for developing, testing, and using exploit code.” A key part of the project is the Metasploit Framework, a development platform that supports creating both security tools and exploits. The framework is largely the reason for Metasploit’s wide use by both the good and bad guys, since it’s relatively easy to adapt the tools for specific purposes. Over the years, many of SQL Server’s vulnerabilities have been discovered using these tools. Metasploit isn’t for the faint of heart—you have to be really focused and dedicated to learning to use it effectively—but it’s incredibly powerful. Unfortunately, much of that power is used for evil, and you can bet it’s being used right now on your servers. At the very least, you should assume that it is!

SQL Server–specific vulnerability scanners are less common than the general network analyzers, but NGSSoftware offers one: NGSSQuirreL for SQL Server. This is a powerful SQL Server security analyzer that performs more than 700 tests to find most of the known vulnerabilities in various SQL Server versions. The product is a bit picky about getting the connection and login credentials just right before starting a scan; it took me about a half dozen tries to configure everything correctly to make a successful connection for a scan. Other applications, including a local version of SSMS, had no trouble connecting to the server I wanted to scan, so I’m not sure what the problem was.

Once you’ve set up NGSSQuirreL correctly on your system, start the scan and go get some coffee. By the time you get a cup of coffee and return to your desk, the scan should have finished—that’s surprisingly quick and what you can expect for an NGSSQuirreL scan, even on a remote server over a broadband connection near the low end of the speed range. After NGSSQuirreL finishes the scan, it displays an easily navigated treeview containing a lot of information about the SQL Server instance as well as the problems the tool found. When I ran an NGSSQuirreL scan on a remote server, I was distressed to see how many vulnerabilities it found—on a production server! Each item in the scan results list has plenty of information about the problem and what to do about it, along with lists of affected database or server objects, as needed. Not every problem that NGSSQuirreL finds means you have a serious security vulnerability, but taken together, they can indicate a server’s potential vulnerability.

The No-Brainer Security Tool
Finally we come to the very best SQL Server security tool of all, one that’s essential to run regularly to ensure secure database servers. But the tool—Microsoft Update—isn’t exactly a hacker tool. A fully patched machine is one of your best defenses against new attacks. It’s gotten so bad that Microsoft’s second Tuesday of the month—Patch Tuesday—is often followed by Black Wednesday as attackers develop new attacks overnight after Microsoft releases the details of newly patched vulnerabilities. Of course, you need to test all SQL Server updates before deploying them to production servers. And don’t use Windows Update, which doesn’t have nearly the reach of Microsoft Update. Third-party tools that perform similar functions to Microsoft Update are available as well.

One Step Ahead of Hackers
In this age of increasingly clever attacks on our database servers, administrators have to be diligent about monitoring and testing the security of their SQL Server machines. You can strengthen your database defenses by using the tools I’ve described or similar ones to find out what hackers already know about your databases and servers.