CHAPTER SQL Server Security: 1 The Basics IN THIS CHAPTER: SQL Server History Editions of SQL Server General Database Security SQL Server Security Vulnerabilities 1 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 clustering. 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. NOTE 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 installations.) 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 installations. 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 address. 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.
Pages to are hidden for
"SQL Server Security The Basics"Please download to view full document