SQL Server Security The Basics by ifm13548



SQL Server Security:
         The Basics
                      IN THIS CHAPTER:
                         SQL Server History
                      Editions of SQL Server
                 General Database Security
          SQL Server Security Vulnerabilities

2   SQL Server Security

    I     n the early days of personal computing, security was often an afterthought, if it
          was ever thought of at all. When IBM-compatible systems began to become the
          popular computing platform for businesses, there was no Internet and the term
    “hacker” still had a positive connotation. Database systems in these early times were
    either confined to very large-scale systems hidden away in the “glass house” of the
    data processing department, or were completely contained within a single floppy disk
    and transferred from one system to another by way of the only means possible, by
    foot (“sneaker net”). Security in those times was pretty simple: don’t let unauthorized
    persons into the glass house, and lock your floppy disks in your desk when you’re not
    using them. While still good advice, these simple measures are no longer effective in
    today’s highly connected and data-critical world.
       To fully understand the problems we face in today’s world, it is very important
    to look at the past and see how we arrived where we are today. This is very true for
    computing in general, but a good lesson in the history of SQL Server tends to really
    open the eyes of SQL Server database administrators (DBAs) to the security challenges
    they will inevitably face.

    SQL Server History
    Few realize that development of Microsoft SQL Server actually began even
    before development of Windows NT. Most know that SQL Server was a joint
    development effort of Sybase and Microsoft, but it’s not so widely known that
    the first release of what we call Microsoft SQL Server was actually on the OS/2
    platform and was known as Ashton Tate/Microsoft SQL Server. This product was
    announced in the latter half of 1988, and was to become a critical component in the
    new Microsoft BackOffice platform. Although Sybase was not mentioned in the title,
    it was responsible for most of the kernel development of this new database system.
       In December 1992, Microsoft announced its new SQL Server for Windows NT.
    A quote from the original press release shows Microsoft’s intent to target the enterprise
    data-center right from the start: “The SQL Server for Windows NT beta release
    allows large accounts to begin prototyping powerful client-server enterprise computing
    applications today on Windows NT platforms,” said Paul Maritz, senior vice president
    of systems at Microsoft. “We are working very closely with these customers to ensure
    that a very high-quality version of SQL Server and a full range of other advanced
    services is available for Windows NT.” It’s interesting to note that Windows NT
    didn’t ship until March 1993, so it should be obvious that SQL Server was a very
    important part of Microsoft’s strategy to gain acceptance in the enterprise.
                                  Chapter 1: SQL Server Security: The Basics             3

   On September 14, 1993, Microsoft announced the release of SQL Server for
Windows NT. Instead of a 1.0 release, Microsoft chose to stick with Sybase’s version
number, which at that time was 4.2. With the release, Microsoft was touting the ease
with which a DBA could create and manage objects within the database. It is worth
noting that at this point Microsoft had not yet acknowledged the usefulness of the
Internet, and security concerns were still not as heightened as they are today. The
primary focus appeared to be usability, scalability, and performance.
   In September 1994, Microsoft announced the availability of SQL Server 4.21a,
which was a huge leap forward in terms of scalability and operability for databases
on the Windows platform. Microsoft won several awards with this release, including
the coveted DB/Expo award for Database Innovation. Also in 1994, Checkpoint
Software Technologies Ltd. released its first Internet firewall, bringing attention
to the whole concept of “Internet security.” Incidentally, Bill Gates dismissed the
Internet as a passing fad early in 1994. A statement he would later come to regret.
   In June 1995, Microsoft announced the availability of SQL Server 6.0. The version
number increased to keep up with the Sybase version number, but the main addition
to this version of SQL Server was a new graphical management tool called Enterprise
Manager. The original press release quotes Paul Maritz, “Microsoft SQL Server 6.0
incorporates all-new technology to meet customer needs for a new generation of
distributed business solutions. Microsoft SQL Server 6.0 gives customers a scalable
database platform that is extremely fast and powerful, yet remarkably easy to install,
manage and use.” The last sentence in the quote sums up the idea, “Make it fast, yet
simple to use.” A few months later, Microsoft did an about-face on its Internet policy
and announced that it was fully embracing the Internet and viewed it as the path to
the future.
   In April 1996, Microsoft announced the release of SQL Server 6.5. This was the
first time since SQL Server’s introduction that Microsoft engineers had complete
access to the kernel code. Sybase was no longer involved in the development of
SQL Server. Among the items added to the kernel code were dynamic locking and
heterogeneous replication of data. In the original press release, Jim Allchin, senior
vice president of the desktop and business systems division at Microsoft, states:

       Microsoft SQL Server 6.5 is a breakthrough for client-server databases, even
       as the Internet transforms business computing. Beginning with version 6.0
       and now with 6.5, Microsoft SQL Server has emerged as one of the pillars
       of the Microsoft BackOffice™ family of server applications. Microsoft SQL
       Server delivers the performance, security and interoperability required across
       the enterprise and the Internet, yet it is cost-effective and manageable for
       businesses of all sizes.
4   SQL Server Security

        It became very apparent to the product management team at Microsoft that in
    order to compete in the highly volatile world that database systems had become, the
    core database engine in SQL Server would have to be rewritten. SQL Server 6.5
    would become the last version of SQL Server that depended heavily on the Sybase
    code. From a security perspective, this was also Microsoft’s chance to more closely
    integrate their own security models into the SQL Server product. Until this point,
    support for an integrated security framework between SQL Server and Windows
    was clumsy and difficult to maintain.
        In November 1998, SQL Server 7.0 was released. This was the first version of
    SQL Server that was written almost completely from the ground up by Microsoft.
    With SQL Server 7.0 came many of the more interesting features from a security
    perspective, including native support for Windows authenticated accounts and groups
    instead of the clumsy user-mapping methods of previous versions. In addition, the
    concept of fixed server and database roles gave SQL Server administrators greater
    delegation ability than previously available.
        In December 1999, Microsoft announced the development of SQL Server 2000,
    which was officially released in August 2000. It has since grown to become the
    best-selling database system on the Windows platform, and has shown incredible
    scalability and performance. New security-related features included SSL encryption
    for SQL Server network libraries, Active Directory support, support for user context
    delegation using Kerberos, and multiple instance support. However, as you’ll find
    out later, sometimes all these new security “features” bring a whole new set of
    vulnerabilities with them.
        Looking forward, rather than build smaller “point releases,” Microsoft has chosen
    to introduce several “web releases” for SQL Server 2000 that introduce specific new
    technologies, such as the XML web services toolkit (see http://msdn.microsoft.com/
    sqlxml for more information). Each of these releases, unfortunately, also comes with
    its own security issues so be sure to fully research the security implications of any
    additional SQL Server pieces you incorporate.
        Microsoft has also introduced new “family” members, such as SQL Server
    Notification Services. Notification Services is meant for businesses looking to send
    large volumes of notifications to e-mail addresses, cell phone numbers, or Microsoft
    Windows Messenger clients. Like many technologies, if not properly secured, these
    capabilities are ripe for abuse. A compromised SQL Server with access to contact
    information of all employees or customers could be a juicy target for potential attackers.
        In addition to SQL Server Notification Services, Microsoft has introduced SQL
    Server CE Edition, which scales SQL Server down to handheld devices. Some
    targeted applications for such a technology are for distributed sales force and remote
    data collection systems. Of course, when considering deploying these technologies,
                                   Chapter 1: SQL Server Security: The Basics               5

physical security is critical. Should one of those terminals fall into the wrong hands,
a potential attacker could re-sync a poisoned database with the corporate server and
wreak havoc. Remember, you must always look at new technologies with a mixture
of excitement (to the new features) and skepticism as to the security implications.
   All of these things work together to make SQL Server a very pervasive database
platform that, unlike its competition, has made data management easy, even for
the novice. Scaling from small handheld devices all the way up to powerful 64-bit
multiprocessor systems, SQL Server is rapidly gaining acceptance from technology
managers throughout the world. Our job is to understand completely the security
features and weaknesses of this powerful platform in order to secure it for our purposes.
With great power comes great responsibility.

Editions of SQL Server
In keeping with its mission to provide a database platform that will fit the needs of
every organization, Microsoft has released several editions of SQL Server 2000, listed
next. Each has a defined purpose. Because of the many editions of SQL Server available,
organizations do face the problem of deciding exactly which edition to use.

     SQL Server 2000 Enterprise Edition This all-encompassing edition contains
     enhancements that allow for enterprise-class scalability and performance. It
     actually has two versions: a 32-bit version and a 64-bit version. Obviously, the
     64-bit version requires 64-bit hardware. Enterprise Edition supports 64GB of
     RAM (512GB in the 64-bit version) and up to 32 processors (64 in the 64-bit
     version). It also supports network attached storage (NAS) disks and fail-over
     SQL Server 2000 Standard Edition This edition includes all the core
     functionality contained in Enterprise Edition, but is limited to 2GB of RAM
     and up to four CPUs. It is also limited to 32-bit platforms.
     SQL Server 2000 Personal Edition This edition is intended for mobile
     users who run applications that use replication to ensure that the data they
     need is locally available to them, even when they are disconnected from the
     network. This edition is identical to Standard Edition, with the exception that
     it can process only five simultaneous queries and can utilize a maximum of
     only two processors.
     SQL Server 2000 Developer Edition This edition is intended for use by
     application developers and is functionally equivalent to Enterprise Edition
6   SQL Server Security

         with the exception that it can process only five simultaneous queries. This
         edition also grants the user a license to download CE Edition and deploy an
         unlimited number of CE devices.
         SQL Server 2000 Windows CE Edition This edition is intended to run
         on devices running Windows CE or the Pocket PC operating system. In reality,
         CE Edition is a simple database engine that uses replication to interoperate
         with either the Standard or Enterprise edition.
         SQL Server 2000 Desktop Engine Desktop Engine is the successor to
         Microsoft Data Engine (MSDE) and is a slightly scaled-down (tuned for less than
         five simultaneous queries and has a 2GB per-database size limit) version of MSDE
         that is shipped without any of the management tools. MSDE is completely
         redistributable by software vendors as stated in the license agreement that
         accompanies. The Desktop Engine is a very popular database storage mechanism
         for many applications, including many in the Microsoft Office suite.

       With so many editions of SQL Server out there, it becomes very difficult for network
    managers to keep track of exactly what database systems are deployed. Products such as
    Configuresoft’s ECM (www.configuresoft.com) can help network managers keep track
    of the various SQL Server editions deployed throughout their network.

             For more information on the editions of SQL Server 2000, see the whitepaper located
             at www.microsoft.com/sql/techinfo/planning/SQLResKChooseEd.asp.

    General Database Security
    Although the database server generally contains some of the most important data
    housed within an organization, security surrounding the server itself tends to be
    somewhat lax. Many DBAs assume either that the “network people” will take care
    of the security, or that the SQL server is behind the firewall and therefore not subject
    to attack (a potentially fatal assumption). Compounding the problem is the fact that
    many database servers exist outside the control of the information technology group in
    the form of workgroup servers, or even database systems that run on desktop computers,
    such as Microsoft Data Engine (MSDE).
       In the early days of PC-based database systems, security wasn’t much of a concern.
    When the database was confined to a single PC, the end user was responsible for
    ensuring that the data they worked with was kept secure (usually by locking the
                                   Chapter 1: SQL Server Security: The Basics                7

cabinet containing the floppy disks). As database systems grew, and network file
systems became more popular, the security focus shifted to the network administrator,
who used a rather simple system of directory permissions to ensure only very specific
people had access to the files that made up the database. As client/server computing
became more popular and database systems migrated to that methodology, network
administrators generally ignored the server-side security settings and left the security
of the database to the application developer. This, of course, posed a whole new set of
problems, because developers didn’t really understand the internal security mechanisms
and relied on their application to secure the data.

SQL Server Security Vulnerabilities
Microsoft has worked very closely with application and network security vendors to
ensure that SQL Server 2000 contains the necessary security framework components
such as transport encryption, auditing, authentication, and authorization. Despite
Microsoft’s attempts to improve security through adding new features, a steady stream
of buffer overflow exploits in Microsoft’s code have plagued even well-configured
SQL Server installations.
   Outside of SQL Server itself, there are many applications out there that still require
an administrative-level login to SQL Server, effectively bypassing the built-in security
mechanisms. There are also many applications that are subject to SQL injection attacks
(discussed later in this book) and other types of application-level failures. While many
of these issues aren’t directly related to failures in SQL Server, it is important for you
to understand exactly how these failures work and can be exploited. As always, the
best defense is a good understanding of everything that your database server is doing.
   When Bill Gates announced in the now famous “Trustworthy Computing” memo
(July 2002) that Microsoft would halt development in certain products until a
full code review could be performed to seek out and remove security issues (see
www.microsoft.com/mscorp/execmail/2002/07-18twc.asp for more information),
Microsoft developers went on the hunt for security issues in SQL Server and other
products. Despite these attempts, SQL Server vulnerabilities continue to be discovered
to this day. It appears the same humans who performed the code reviews are just as
prone to failure as the humans who coded those vulnerabilities in the first place.
   Unfortunately, many SQL administrators either did not know about the security
bulletins and their associated patches or found that the patches were extremely difficult
to apply to production systems, leaving a large number of systems unpatched. This
led to a couple of the most widely publicized SQL Server–related Internet worms to
date: “Spida,” which searched for SQL servers that were connected to the Internet
8   SQL Server Security

    and had blank sa passwords, which is the installation default (see www.symantec.com/
    avcenter/venc/data/digispid.b.worm.html or www.cert.org/incident_notes/IN-2002-04.html
    for more information), and the more well-known “Slammer,” which exploited a buffer
    overrun to run code that infected the local machine and propagated itself to other
    machines running SQL Server (see http://securityresponse1.symantec.com/sarc/
    sarc.nsf/html/w32.sqlexp.worm.html or www.cert.org/advisories/CA-2003-04.html for
    more information).
       The SQL Slammer worm hit Microsoft very hard, creating a public relations
    nightmare. On one hand, Microsoft had already identified and fixed the vulnerability
    that Slammer exploited. Slammer was first detected in February 2003, but the
    fix for the vulnerability that Slammer exploited was posted in MS02-039 back in
    July 2002. Yet, Microsoft still had customers who felt that the code shouldn’t have
    been vulnerable in the first place and continued to blame Microsoft for the incident.
    Microsoft felt that it had given customers the information that they needed in order
    to mitigate the effects of the Slammer worm, but also agreed that it could do more
    to ensure this didn’t happen again. Compounding this problem was the fact that
    the SQL Server engine was deployed in many places outside of typical tightly
    controlled IT environments, in the form of the MSDE engine that ships with
    several Microsoft Office products, Visual Studio 2002, and a multitude of third-
    party applications. These products were just as vulnerable as the production-class
    versions of SQL Server, which led to machines being affected that many DBAs
    knew nothing about.

    Anatomy of a Worm: Why Slammer Was So Successful
    The Slammer worm was actually a very small amount of code using the UDP
    connectionless transport protocol for transmission, which contributed to its rapid
    spread. It used a buffer overrun exploit in a built-in method to locate SQL servers
    running on the network. The worm required that network administrators made mistakes
    in configuration of SQL Server and network firewalls by exposing a sensitive UDP
    port (1434) to the Internet. From a high level, the following is the sequence of actions
    performed by the worm:

     1. Locate an available SQL server by sending a 376-byte packet of information
         on UDP port 1434 to randomly generated IP addresses. (Port 1434 is the SQL
         Server resolution service port and is enabled by default on all SQL Server 2000
                                  Chapter 1: SQL Server Security: The Basics             9

 2. Attempt to exploit a buffer overrun in the resolution service, and attach code
     to the affected system memory. The buffer overrun vulnerability allowed
     code to be run in the security context of the SQL Server service, which in
     many cases has Administrative rights on the computer—especially on MSDE
 3. If the exploit was successful, attach code that sets up handles to ws2_32.dll
     and kernel32.dll, which gives access to system-level functions. (Both of these
     modules are loaded using the security context of the SQL Server service account.)
 4. Utilize the kernel function GetTickCount as a seed to generate a random IP
 5. Use the randomly generated IP address to send a copy of the worm and then
     repeat Step 4. (If a vulnerable system is found, repeat Steps 1 to 5 on the
     vulnerable system.)

   What made this worm especially interesting is that it resided completely in the
memory of the infected system and did not modify any files. This made it almost
impossible to detect by most virus scanners (those that performed memory scans
were eventually able to detect it). However, due to its rapid spread, it showcased the
weakness of the signature-recognition antivirus products that so many people had
come to depend on for protection.
   The Slammer worm was indiscriminate in that it simply sent itself to random IP
addresses, knowing that eventually some of the addresses would be valid targets for
a new attack. Fortunately, the author of the worm only attempted to propagate the
code instead of using the code for more malicious purposes. The worm easily could
have run code to delete files on infected systems, or worse. Several things worked
together to ensure that both Spida and Slammer were a success; if any one of the
following things had been corrected, neither would have been more than a blip on
the virus radar screens:

     UDP port 1434 need not be allowed to either enter or exit a company’s
     perimeter network (or even a home user’s network for that matter). Slammer
     specifically targeted port 1434 and was able to find many situations where
     port 1434 was open at the firewall in both directions. Inbound it was able to
     connect to a SQL server and then turn around and send data back out that
     same port. Many firewalls are configured to block the inbound port, but do
     not block outbound traffic.
10   SQL Server Security

          Network administrators need to know exactly what software is running on their
          networks. Many attacks against SQL Server were successful because of the
          amount of MSDE instances “laying around” on the network. Slammer didn’t
          care if it was a full-blown copy of SQL Server or a simple MSDE instance,
          only that the SQL Resolution listener service was enabled.
          SQL Server administrators, developers, and users need to understand what
          vulnerabilities exist and take steps to ensure their servers and workstations are
          patched. The Slammer worm exploited a vulnerability that was published and
          addressed by Microsoft more than six months before the attacks occurred.
          Microsoft should have done a better job of identifying the ubiquity of SQL
          Server in its various forms and integrated the detection and patching process
          into existing mechanisms such as Windows Update, which end users and
          administrators have come to reply upon.
          SQL Server Books Online (the help documentation that comes with SQL
          Server) should do a better job of educating users and administrators about the
          importance of patches, secure installation procedures, secure deployments,
          and automating patch-level detection/installation processes.

     Preventing Another Slammer
     When discussing SQL Server security, there are really five things that you should keep
     in mind:

          SQL Server is a powerful, network-centric application and must be treated as
          such. You need to take specific steps to secure both the client and the server.
          SQL Server is built on Windows technologies. By far the most important aspect
          of securing SQL Server is to realize that you are only as secure as the underlying
          operating system. A good DBA will take the time to fully understand the
          underlying OS security implications.
          SQL servers contain valuable business data. As such, they deserve as much as
          or more security focus than the rest of the enterprise.
          It is imperative to understand where the SQL Server installations exist in your
          organization and keep them patched and secured.
          Lock down any new installations of SQL Server as tightly as possible and
          only loosen the security configuration settings when applications demand the
          functionality. In other words: shrink the surface area for attack.
                                   Chapter 1: SQL Server Security: The Basics            11

    This book is written with the goal of educating readers so that the events that
conspired to allow the Spida and Slammer worms to propagate with such success
will (hopefully) never happen again. As you read through the book, keep in mind
that the focus here is not to instill the feeling that SQL Server is insecure. Rather,
it is to ensure that you are fully aware of what needs to be done so that you don’t
become another victim.

To top