Securing SQL Server

In late January, a worm called SQL Slammer shut down a Bank of America ATM network, Continental Airlines' online ticketing system, and an emergency call center in Seattle as well as cutting off Internet access for millions of PC users worldwide. Slammer also revealed a hidden problem in the SQL Server community: Many customers aren't promptly applying service packs and hotfixes.

Related: Securing Your SQL Server Environment

Slammer was a devastating worm. But it wasn’t the first and won’t be the last virus to hit the Internet. Attackers will continue to find holes in software, and vendors will have to patch those holes as they’re discovered. Still, patches are useless unless customers install them. The same goes for service packs. SQL Server Magazine heard from hundreds of readers who consciously decided not to apply the patch for the buffer-overflow/escalation-of-privilege vulnerability that Slammer took advantage of.

In this interview with Microsoft Vice President of SQL Server Gordon Mangione, SQL Server MVP Brian Moran explores why customers aren’t applying patches, Microsoft’s plans to address these problems, and the future of security for SQL Server.

One of the most common reasons users gave for not applying the original hotfix, the cumulative patch, or SQL Server 2000 Service Pack 3 (SP3) was that their ISVs didn’t support the latest patch or service pack. So in a Catch-22 situation, customers know that a patch exists and that they should apply it, but they also know that doing so might invalidate their ISV service contract or break an application. How does Microsoft currently work with the ISV community to roll out patches and service packs, and how do you plan to improve this process?

We learned through this latest service-pack process that we have to make it much easier for ISVs and customers to upgrade to the latest service packs. Our product team is committed to frictionless installs. ISVs generally test, support, and certify at the service-pack level, not at the individual-fix or cumulative-fix level. So, most customers could install the cumulative hotfix and maintain ISV support while waiting for formal certification of a new service pack.

For our service packs, we’ve also started beta programs and joint-development programs to give ISVs early drops of the code before releasing it to the public. We encourage ISVs to test their applications with the service packs not only to give us feedback about the quality of the service packs but also to pre-certify their applications running against the service packs. We’re also working with ISVs to get playbacks of their applications so that we can test the database with their applications even before we release the code to them.

Another common reason that users gave for not installing SP3 was that they can’t uninstall service packs. Readers said they have time to roll out the service pack and deal with a few minutes of downtime to apply the patch and take it off if something breaks. But they don’t have time to completely rebuild their servers if there’s an issue with the service pack. Does Microsoft plan to enable users to roll back service packs?

Customers tell us they want to be able to uninstall service packs, security patches, and Quick Fix Engineering (QFE) updates. We’re focused in the short term on providing the capability to roll back security fixes. We also absolutely have the goal of allowing rollbacks of service packs, although that’s a much more complicated process and will take longer to implement in the code.

Many customers said they probably would have installed the patch sooner if it had come with an installation program. Manually copying files might seem like a simple task, but it opens the door to manual errors, which would be difficult to trace if an incorrect file was accidentally overwritten. Will Microsoft issue all new security hotfixes with an installer?

The weekend Slammer hit, we re-released patch MS02-061 along with a hotfix installer to make installation easier. The installer will be available with any future security hotfixes.

You and many other Microsoft SQL Server officials are visiting customers who were hit by Slammer. What do your visits and Microsoft’s research say about other reasons why SQL Server customers aren’t applying service packs and patches in a timely manner?

The key issues we’ve heard include difficulty installing patches, lack of time and resources to update systems, uncertainty about which patches are critical, and lack of awareness of unmanaged SQL Server or MSDE systems. And in the specific case of SQL Server 2000 SP3, users noted the lack of time to adequately test SP3; we released it on a Monday, and Slammer hit the following Friday.

What are the differences between a hotfix patch and a service pack? Should users always apply all hotfixes? If not, how do users know for sure which hotfixes to apply? Should they apply all service packs? If a user applied all hotfixes consecutively as Microsoft released them, is that the same as if the user applied a cumulative patch or a service pack—in other words, could the user just skip the cumulative patch or service pack?

The Microsoft Security Response Center issues a bulletin for any product vulnerability that could, in our judgment, result in multiple customers' systems being impacted, no matter how unlikely or limited the impact. However, this approach to identifying vulnerabilities has made it difficult for some customers to identify vulnerabilities that represent especially significant risks. So Microsoft recently adopted a new rating system for security patches to help customers understand the severity.

The system rates each vulnerability as Critical, Important, Moderate, or Low. \[Editor’s note: See Table 1 for a definition of each rating.\] We believe that customers who use an affected product should almost always apply patches that address vulnerabilities rated Critical or Important. Customers should apply Critical patches in an especially timely manner. Customers should also read the security bulletin associated with any vulnerability rated Moderate or Low to determine whether the vulnerability is likely to affect their particular configuration. For more information about the rating system, customers can see

Security patches are cumulative patches, so the customer just needs to apply the latest patch. They also need to apply the latest service pack. Service packs are broader than security patches: They include bug fixes and, sometimes, administrative settings and other security-related functionality, such as the Watson reporting functionality in SP3. It’s important for customers to be on both the latest service pack and the latest bulletin.

SP3 contains numerous security fixes for SQL Server 2000. Can you detail just how SP3 addresses security?

SP3 is a cumulative service pack that includes everything we’ve done in SP1 and SP2 as well as all security releases and public QFEs that we’ve announced. SP3 was the result of a 3-month code review that also resulted in several security fixes, a new chapter on security best practices in SQL Server Books Online (under "Administering SQL Server, Managing Security"), a valuable Security Checklist (available at, and a security white paper, which we’ll post in May.

We did an exhaustive code review, developed security threats, and devised plans to test a number of other functions such as cross-database ownership chaining. Customers will see more evidence of this work in Yukon.

So SP3 addresses different potential security vulnerabilities for which Microsoft might not have released a prior hotfix or QFE? If customers’ primary concern is security patches and they were up-to-date with all the most recent security hotfixes, they’d still need to install SP3 because it addresses other vulnerabilities?

Correct. Customers first need to get MS02-061 applied on their systems as quickly as possible to protect themselves from possible Slammer variants. Then, I would highly recommend customers develop test and deployment plans for SP3, although we realize that’s a more complex process and customers have to do internal testing before they deploy the service pack.

Many companies still use SQL Server 7.0 as their primary platform. If SP3 is the culmination of the security fixes for SQL Server 2000, what is your intended release time frame and delivery mechanism for a comparable service pack for SQL Server 7.0?

When we find a major vulnerability in SQL Server 2000, we go back and evaluate whether SQL Server 7.0 is also vulnerable. If so, we issue a security bulletin for both products. MS02-061, for example, addresses both versions. We’ll continue to monitor the QFE trends and customer feedback for SQL Server 7.0 and determine whether an additional service pack is required.

At a recent SQL Server conference, you said, "Success can't be measured by whether or not a patch had been released and was available to our customers. Success needs to be measured by whether or not our customers were affected." How, specifically, can Microsoft help protect its customers, and how will you measure whether you’re successful in doing that?

We’re working on patch-management improvements in the following areas: thoroughly testing patches with customers and ISVs, packaging patches for convenient installation, making patches automatically and manually deployable, reducing reboots required when patching, letting users uninstall patches, providing patches in local languages, continuing to ensure that patches are cumulative, continuing to include all patches in the next service pack, and investing in detection tools.

The feedback we received is that we have to measure ourselves not by when we make security patches available but by when customers deploy them. To that end, we developed a set of tools—SQL Scan and SQL Check—that customers can run to identify systems vulnerable to Slammer. We also developed SQL Critical Update as an easy-to-use tool to deploy the corresponding security fix for whichever service pack customers have on their system.

Going forward, we intend to provide this functionality through tools such as Microsoft Baseline Security Analyzer (MBSA) and automatic update services. For example, we’re investigating taking technologies such as Windows Update (WU) and broadening the scope beyond the Windows OS. Patch-management tools are a focus not only of the SQL Server team, but also of Microsoft as a whole because we’ve received clear customer feedback: Don’t create solutions that are product specific. Customers need tools that help them manage their entire environment and all their applications. We’re investigating how to support deployment of security patches by using the Software Update Services (SUS) and WU infrastructures, and we’re confident that this integration will make it a lot easier for customers to deploy our patches. Right now, we’re making sure we have all the architectures and pieces in place and talking to customers about their exact requirements for a broader tool set. But it’s too early to speculate about exactly when those tools will be available.

Why isn’t SQL Server included in Windows Critical Update notifications? Many of our readers noted that they are part-time DBAs and simply don’t have the time to stay up-to-date on what patches and service packs should be applied to all the software that runs on their servers. What are the technical and practical implications of including SQL Server in Windows Critical Updates, and are there plans to do so?

The Microsoft Trustworthy Computing team, led by Mike Nash, has patch management as a top priority. WU is a great method for Windows, but there may be better ways to deliver updates for multiple products. The Trustworthy Computing team is looking at the best way to implement updates through a single system.

We certainly want to give customers more-effective tools to monitor and lock down their servers from a security perspective. We see the latest version of the tools we released as part of our SQL Critical Update Kit to be a first step toward achieving this goal. Currently, we’re gathering customer feedback about how we can improve these tools going forward and plan to add functionality to these tools to make them as effective as possible.

Part of this process is also education, and we’re updating existing white papers to focus more on security best practices. We’ll also continue to invest in and grow MBSA to include more SQL Server-specific vulnerability checks.

How does MBSA and the SQL Critical Update Kit tools work together?

We recommend running MBSA 1.1 to analyze potential vulnerabilities in your systems, then use SQL Scan to look for the particular vulnerabilities inside your environment. Version 1.1 is the second release of MBSA and includes a graphical and a command-line interface that can perform local or remote scans of Windows systems. MBSA runs on Windows 2000 and Windows XP systems and will scan for common system misconfigurations and missing security updates in SQL Server 2000 and 7.0 and other Microsoft products. If MBSA finds a vulnerability, it points you to the proper patch to download. In contrast, the SQL Update tools actually go and apply the patch that protects against Slammer. Customers interested in finding out more about MBSA can visit

We developed the SQL Critical Update Kit tools because we needed to quickly develop specific tools to help SQL Server customers understand what’s going on in their environment. But as I noted earlier, our long-term goal is to bring all these tools together under the MBSA umbrella and offer customers a single tool that scans for all products in their infrastructure.

Slammer affected a large number of MSDE installations. How can Microsoft help people stay up-to-date with patches when many applications install MSDE by default and administrators might not even be aware that MSDE is on the box? What has Slammer taught Microsoft about the advisability of installing a product like MSDE by default without notification?

We’re working on a more simple patch application specifically for MSDE that would let users patch MSDE from a GUI application. We still believe that MSDE is important as a light data store, and we’ll continue to make it better, more secure, and easier to patch and administer.

As I mentioned earlier, we’re looking to improve our SQL Scan and SQL Check tools as well as other tools to detect SQL Server instances including MSDE. We’re also working closely with MSDE partners to ensure that they follow recommended procedures of minimalist installation and secure defaults and to make sure that MSDE is installed only when absolutely needed and through customer choice. In addition, we’re working to better document applications that install MSDE and working to better highlight in those applications’ documentation that they install MSDE. Our goal is to be very explicit with the installation of MSDE going forward, not only in posting on our Web site which applications use MSDE but making end users aware that they are about to install MSDE as part of their application.

MSDE patches and service packs aren’t easy to deal with. Installs are often different from SQL Server and might even be different based on how the MSDE bits were deployed to the server initially. Microsoft describes MSDE and SQL Server as sharing essentially the same code base, so why can’t Microsoft release one service pack that can be applied to any instance of SQL Server, whether it’s Enterprise Edition or MSDE? Do you have any plans to simplify the process of working with MSDE patches and service packs?

We realize that keeping MSDE up-to-date with patches and service packs is difficult. Although MSDE and SQL Server use the same code base, the way they are used and deployed can be fundamentally different. We’re working hard to devise a specific solution for patching MSDE and hope to have a solution that will let MSDE be patched automatically.

Customers told us that their SQL Server deployments and how they patch them were more straightforward than their MSDE implementations. They needed better tools for identifying MSDE in their environment, and they needed graphical tools that end users could use to deploy patches. With Version 3 of the security tools, rolled into the Critical Update Kit, we incorporate an easy-to-use GUI for these tools so that end users can patch their MSDE systems in a straightforward way. Customers can just run this one tool, and it will upgrade their MSDE instance, independent of how it was installed on their machine. In fact, we’re going to the level, based on customer feedback, of disabling network access by default in MSDE because the vast majority of MSDE applications don’t require network access. This will reduce the surface area of attack going forward. Microsoft products that redistribute MSDE also will disable network access by default and will use only integrated security, significantly reducing their exposure to network-based attack.

Buffer overflows of different types seem to account for many of the SQL Server security vulnerabilities discovered recently. Many people say that buffer-overflow errors should be easier to detect than logic-related programming errors and should be easier to prevent in the first place. How is Microsoft addressing this area of vulnerability in SQL Server?

Our line-by-line security review of SQL Server looked at buffer-overflow issues carefully. We also invested in training so that, going forward, developers and testers would have the relevant information and knowledge to ensure that we don’t repeat these mistakes. In addition, we’ve worked closely with the Microsoft research team to build tools that will help identify these issues in an automated fashion during the development process. The tools generally analyze our code, integrate with our compiler technology, create ways for us to better target tests at the code, and help us to be introspective about what’s going on inside the software.

Buffer overruns are one of many types of ways that attackers can take over a system. But security is much bigger than buffer overflows. It’s about what you’re doing to protect yourself against elevation of privileges and what you’re doing to find other vulnerabilities in the code. Since introducing the Trustworthy Computing initiative, we’ve elevated security to our No. 1 concern. Every Microsoft developer, tester, program manager, and vice president in the company—including myself—has gone through security training. It’s important that we instill this philosophy throughout the organization—it’s really about changing the way we do software development.

What is Microsoft is doing to security-harden SQL Server now?

We’ve re-released MSDE with SP3 included, and we’ve re-released SQL Server 2000—all editions—with the new Super Socket Network Library that eliminates the buffer-overflow vulnerability and copackaged it with SP3. If customers have already patched their systems to protect against Slammer, they don’t need to reinstall these versions of SQL Server. We decided to re-release with SP3 included and to re-release SQL Server 2000 with the vulnerabilities for Slammer addressed out of the box so that customers could confidently deploy new installations of SQL Server and know that they already had these patches installed. In addition, we’ve visited customers to understand requirements better, invested in patch-management solutions and vulnerability-detection tools, and created the security best-practices document that I mentioned earlier.

So new customers still need to apply SP3 as a separate installation, but you’re making it readily available on the same distribution media?

Yes. In addition, the release-to-manufacturing (RTM) install will remove the Slammer vulnerability so that, out of the box, you can deploy and know that your system won’t get hit by Slammer before you apply the service pack or any of the security bulletins. But the install will include only that additional fix. Again, we highly recommend that customers test and deploy SP3 on all their SQL Servers.

In terms of dollars spent, we’ve heard that 25 percent of Yukon's cost relates in some way to enhancing security. What are you doing in Yukon to improve SQL Server’s security?

We’re disabling network access by default and disabling the User Datagram Protocol (UDP) instance resolution service that listens to port 1434 by default. Multiple instances will still work; they just won't be visible to strangers scanning your network. We’re also instituting the principle of least privilege, role-based security, and secure defaults.

We’ll help administrators use best practices by defaulting to the highest level of security possible and guiding them to grant additional privileges as needed. Role-based security makes administering security privileges easier than assigning specific privileges to each user. Secure defaults, such as turning off network access and defaulting to Windows Security rather than mixed mode, also help encourage best practices.

Why does SQL Server use its own encryption algorithms? Shouldn’t you be leveraging the services offered by Windows? Will Yukon do this?

SQL Server, in general, uses encryption algorithms supported by the underlying operating system, and all new features depend on OS-supplied encryption. For historical reasons, some features have depended on weaker mechanisms, but we’re committed to moving these to OS-supplied mechanisms as aggressively as possible.

Microsoft has consistently argued that with SQL Server’s ease of use and management, companies can and should have their DBAs focus more time on business issues than on traditional DBA activities. Then comes a security problem like Slammer, and most organizations complain that they just don't have the staff to keep up with the constant flow of software patches. How does Microsoft reconcile these two scenarios?

You’re right. Microsoft’s goal is to automate the routine, tactical tasks that DBAs must accomplish in an effort to free them up for more strategic work. Although manageability tools are a strength of SQL Server, we know that we have to do a better job of patch management. Microsoft’s principle is to be proactive in issuing patches as we discover vulnerabilities. The key will be to make these patches easier for customers to understand and deploy while minimizing downtime.

We’ll continue to build on the security tools and the installer we released, and we want to continue the dialog with customers about how to help them with patch management, which is an issue across the industry. This is a huge priority for us, and you have my commitment that we will make it better.

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.