Dangers Associated with a Stored Procedure Attack
The question you may be thinking right now is, what is the point of using a stored procedure attack if you already require sysadmin-level privileges prior to executing it? This is a valid question because if you already have sysadmin-level privileges, then you have the ability to create and manage privileges within the database, the ability to manipulate any part of the databases stored within SQL, and access to all of the data. Therefore, the point of the attack cannot be to gain administrative privileges within the database itself. If you already have everything you need to walk in through the front door of a building, the question becomes, what do you get by using the service entrance?
In this case, the service entrance gives you the authority to roam the whole building instead of just the common areas that visitors see. The combination of stored procedures and your sysadmin role access allows you to utilize SQL Server as your attack platform to defeat the server and any additional applications running on a shared server (this could mean owning the domain, if the SQL Server application is installed on a Domain Controller). In addition, stored procedures attacks can be used in conjunction with other SQL Server attacks, such as SQL injection, to gain this same authority without requiring sysadmin-level access prior to the beginning of the attack.
Understanding Stored Procedure Vulnerabilities
Historically, there have been numerous vulnerabilities identified in Microsoft SQL Server stored procedures. Some of the vulnerabilities are directly related to the code implemented to support the stored procedures, while other vulnerabilities stem from the functionality some of the stored procedures provide. A few of the categories for attacks against stored procedures experience over time include excessive privileges, buffer overflows, and trojaned stored procedures.
- Excessive privileges Some of the stored procedures preinstalled on SQL Server allow the execution of commands on the underlying operating system. This type of relationship between the SQL Server and the operating system allows attackers to leverage system commands that can cause an immediate impact on the security of the SQL Server and the supporting operating system.
- Buffer overflows In the past, several stored procedures have experienced issues with exception handling for receiving parameters in the context of a stored procedure causing the return address of the call to be overwritten. A buffer overflow condition can allow attackers to take control of the next instruction performed on the system and subsequently allow for arbitrary commands to be executed. These conditions may allow for attackers to interact with the core operating system and may also include causing denial of service conditions.
- Trojans Attackers who are able to gain access to the underlying operating system have been able to replace legitimate Dynamic-Link Libraries (DLLs), applications, and executable files with files that appear to be the legitimate but have been modified. Stored procedures are sourced from a series of DLLs and modification of the stored procedure functions within the DLLs can allow execution of code that runs under the context of the SQL Server.
Microsoft has done a fairly good job at documenting stored procedures and the capabilities they provide. Not all of the stored procedures available, however, are documented by Microsoft and administrators may not fully understand some of the security issues implementing stored procedures could cause.
Some of the notable stored procedures that allow attackers to interact with and glean information from the SQL Server include:
- xp_cmdshell This extended stored procedure allows members of the sysadmin fixed server role to execute commands in the context of the permissions associated with that of what account the SQL Server service is running under.
- xp_enumgroups As the name of the stored procedure indicates, this extended stored procedure allows members of the sysadmin and db_owner fixed server roles to enumerate group membership information from the local or domain groups specified in the stored procedure call.
- sp_addlogin This is a system stored procedure that creates a new user account that can be used for authentication to the SQL Server. However, Microsoft documentation indicates that this stored procedure will be removed in a future version of SQL Server. In addition, Microsoft recommends using Windows authentication as an alternative to this method.
- sp_addsrvrolemember This adds an existing account to a specified group within the SQL Server instance.
- xp_grantlogin This stored procedure assigns the appropriate permissions that allow the defined Windows security group or account to connect to the SQL Server.
- xp_logininfo This provides information about a specific account or a group of accounts and the level of access the account has. The stored procedure can also return information about accounts and group membership.
- xp_regread This stored procedure returns the values associated with registry keys found on the SQL Server.
- xp_regenumvalues This provides a list of all the values located under a specific registry key.
- xp_regwrite This stored procedure is used to write entries to the system registry.
- xp_msver This provides information about the version of the SQL Server instance, as well as the underlying operating system.
- xp_servicecontrol This controls the state of the operating system services. This stored procedure can be used to start, stop, pause, continue, and querystate any service the sa or sysadmin fixed server role has permissions for.
Examples of some of the common attacks against stored procedure implementations are provided to help illustrate some of the concepts discussed. Although a few examples are provided for clarity of what an attacker may do, the sky is the limit if you have a good imagination and think like an attacker. The following scenarios assume that the stored procedures have already been enabled as previously discussed.
Scenario 1: Adding a Local Administrator
One of the most common attack scenarios leveraged today involves using stored procedures to add user accounts to the SQL Server host operating system. This scenario involves an attacker successfully authenticating and connecting to an SQL Server using the sa account with a weak password. Unfortunately, in the field, it is fairly common to find SQL Server databases using SQL Server authentication and allowing access via the sa or other application accounts assigned to the sysadmin fixed server role.
alt1 Warning
Although this chapter focuses on the risks stored procedures can create, it should also be obvious to readers that poorly implemented passwords for databases will allow access to the contents of the database. This may include viewing contents of the database or dropping tables of the database as well. Always ensure strong passwords are used to protect critical assets.
Once an attacker authenticates successfully, stored procedures can be leveraged to execute further attacks against the SQL Server and the underlying operating system.
Figure 3.2 illustrates an attacker connecting to the SQL Server using the sqlcmd utility and authenticating with valid credentials. Upon successful connection, the attacker can leverage the use of the xp_cmdshell stored procedure to add a user account to the local system.
FIGURE 3.2. Adding a User to the Local Administrator Group
DBAs and attackers can utilize the xp_cmdshell stored procedure to interact with the operating system to perform administrative duties usually reserved for administrators of Windows itself. As seen in Figure 3.2, the attacker executes a few simple commands to add a user to the operating system hosting the SQL Server. In our target farm, the attacker has connected to an SQL 2008 Server that is running on Windows Server 2008. After connecting, the attacker issues a net user command to add a new user to the server's local Security Accounts Manager (SAM) database. Once the attacker has created the new account, “t800” in our example, he then uses the xp_cmdshell stored procedure to execute the net localgroup command to add the new account to the Administrators group on the server. It does not take much imagination to think of what types of malicious activities can be performed when an attacker has access to a local account that is part of the administrators group.
Scenario 2: Keeping Sysadmin-Level Access
In some cases, attackers may consider adding an additional account to maintain access in the event the primary sysadmin account password is changed or the account used for access by the attacker is disabled. Shamefully, DBAs may not actually notice the additional account unless auditing for the account creation is enabled and there is monitoring and alerting for this type of activity.
While working in the field doing penetration tests, we have added an administrator-level account once we compromised a system in order to maintain access during the assessment process. At the end of the assessment, accounts are usually removed to as part of the cleanup process. Prior to cleanup, this administrator-level account may have resided on the system for days or weeks, depending on the scope of the assessment, without the true administrators identifying the new account. Where are we going with this? Well, since our real-world experience shows this occurs regularly during these controlled tests, it is only natural to assume that attackers could use the same methods to insure extended access to the system.
Figure 3.3 shows our attacker connecting to the SQL Server and using the sp_addlogin stored procedure through the sqlcmd utility to create a new account named “backdoor” with a password “1337P@ss.” For the sake of clarity, we are using an account named backdoor in this example to place some emphasis what we are doing. However, it is likely that an attacker would try to choose an account name that blends in. Naming the account “backup,” “service_account,” or “admin” are good choices because they seem like the kind of accounts that could possibly be in an administrator group. After the attacker has added the account to the SQL Server, the account is then added to the sysadmin fixed server role by invoking the sp_addsrvrolemember stored procedure, and our backdoor account now has the same level of access the default sa account.
FIGURE 3.3. Adding a Backdoor Account
Figure 3.4 shows the outcome of the particular attacks perpetrated in Figure 3.3. The Server Role Properties window on our SQL Server 2008 target shows the backdoor account as one of the accounts belonging to the sysadmin fixed server role. Access is verified by connecting to the SQL Server with the sqlcmd utility and using the xp_msver extended stored procedure.
FIGURE 3.4. Backdoor Account Using Stored Procedures
Scenario 3: Attacking with SQL Injection
This chapter has mainly focused on security issues related to the implementation and availability of stored procedures on Microsoft SQL Server. Many of the examples provided thus far have assumed that the sa or another sysadmin fixed admin role had been previously compromised. This example describes leveraging stored procedures by using SQL injection attacks. Before we jump into how SQL injection can be used to leverage stored procedures, let's spend a few paragraphs going over the basics of how SQL injection works.
SQL injection provides attackers a method for interacting with a Web application and its back-end database. These attacks are based on the manipulation of form fields, URLs, or cookies and posting a request to the Web server. The Web server logic evaluates the submission and returns the results based on the interpretation of the request. By modifying a legitimate request, an attacker may be able to cause unexpected results resulting in an SQL error or successful execution of the request.
Depending upon what account and context the SQL Server backend is provisioned with, an attacker may be able to perform a wide range of tasks. A classic example of an SQL injection attack consists of an attacker taking advantage of a Web site login page that contains user name and password fields as well as a submit button. Legitimate users will most likely have a user name and password that allows access to the Web site based on the permissions assigned to their account. However, an attacker can bypass authentication by entering specially crafted SQL statements into the user name and password fields.
For instance, if an attacker entered the following SQL statement into the user name field on the login form and clicked the Login button, the attacker may be able to trick the application logic into allowing access to the application even though no authentication with a legitimate account actually occurred.
The query when processed will use an SQL statement to verify the submitted credentials. In the example provided, the final query sent to the Web and SQL Server may look similar to the following statement.
SELECT * FROM users WHERE userID = 'pwned' OR 1=1—
This previous statement will always return “true” based on the condition that 1 is equal to 1 (the “—” is an SQL comment delimiter that tells the server to ignore code or values that follows the evaluation of 1 = 1). Since a reply of “true” usually means that the username/password combination has been authenticated, this may trick the application into believing that the user has valid credentials and allow access.
In addition, an attacker may be able to enumerate table and column names, allowing the attacker to construct a query to INSERT or DELETE records from a database table. The attacker may also be able to DROP entire tables from the database, which could cause denial of service to legitimate users. Microsoft provides some additional information on the general mechanics behind SQL injection attacks and mitigation measures on the MSDN site,[E] and these attacks are discussed in detail in Mike Shema's Seven Deadliest Web Application Attacks (Syngress, ISBN: 978-1-59749-543-1). Now that a quick overview of SQL injection basics has been provided, let's expand the topic to include how we can use SQL injection to leverage the stored procedures this chapter has been focusing on.
Ehttp://msdn.microsoft.com/en-us/library/ms161953.aspx
By slightly modifying the approach, we showed for attacking the Web application, the attacker can try to pass SQL commands that call on stored procedures. Using the following SQL stored procedure call may result in the SQL Server's host operating system sending an Internet Control Message Protocol (ICMP) ping packet to the IP address identified in the ping command (which should be the address of the attacker's computer).
'; exec master..xp_cmdshell 'ping 192.168.204.128'--
Access to the stored procedure would be validated by starting a packet capture using tcpdump or Wireshark on the attacker's computer, then listening for ICMP packets to be returned from the source address of the SQL Server where the stored procedure was executed. If the SQL Server's host operating system replies, then access to the stored procedure is verified and the attacker may move on to further attacks using stored procedures.
A similar attack involves the attacker again using the xp_cmdshell stored procedure, however, using the appropriate commands to add a user to the local system. This is similar to what was illustrated in our first scenario; however, this time, the attacker is executing the command from a Web form.
'; exec master..xp_cmdshell 'net user attacker P@ssw0rd /add'--
Some of these attacks have been around for quite some time and will most likely be relevant for years to come. It is important to remember that applications that interact with SQL Server should be closely scrutinized and follow best practices for ensuring applications as secure as possible before deployment.
The Future of Stored Procedure Attacks
The good news about SQL Server is that Microsoft has started taking steps to reduce the attack surface of the default installation and has turned its focus onto ensuring a secure development environment that should limit the amount, impact, and scope of vulnerabilities in the future. The bad news is that this really doesn't have anything to do with disallowing the abuse of code or leveraging SQL's authority to escalate your privilege beyond the application itself.
Microsoft may cut off the attack vectors shown or even remove the particular pieces of code that were presented as valid attacks, but others will certainly take their place since this powerful flexibility is one of the core features of the product and the Microsoft philosophy. Even if you could somehow convince Microsoft to remove the raw convenience of stored procedures (or whatever they might choose to rename it for marketing reasons), Microsoft would still have to deal with the heavy bondage that is “backwards compatibility.”
SQL Server's success and use in the field today is really based on what DBAs and developers have created on top of the SQL Server database application itself. This means that Microsoft must keep in mind that major changes to the functionality of the product will have a severe impact on the applications that run on it. As we stated in the section “How Stored Procedure Attacks Work,” stored procedures are not available for attackers to utilize right out of the box. That statement, however, is only referring to the newer versions of SQL Server.
In versions of SQL Server before SQL Server 2005, the stored procedures we are concerned with were installed by default; therefore, Microsoft developers must assume that somebody actually utilizes these stored procedures as part of the applications they have created. Microsoft was willing to pull these procedures from the default install, but that doesn't mean that they are willing to permanently break applications developed on top of SQL Server.
For this reason, the features that drive the sales of SQL Server are those that serve to make development of applications that run on the platform as easy as possible. Since stored procedures are one of those features, and they need to continue to be available for reasons of backwards compatibility, don't expect these attacks to change very much in the near future.