Second Defensive Layer: Reduce the First-Layer Attack Surface
Reducing the attack surface for Windows means following the basic Windows security precautions that you will find in any security best practice guide. Eliminate or disable all unnecessary applications, services, and network protocols (Minesweeper is not a necessary application on an SQL Server no matter how bored you get waiting for a data-mining query to complete). Rename, disable, and/or delete unnecessary accounts (including the built-in administrator account once you have created an alternative account and assigned it administrator group membership). Limit the user rights, privileges, and group membership of accounts to only what they need to perform the function they are designed for.
From an SQL Server perspective, reducing the first-layer attack surface means removing any unnecessary accounts from the sysadmin server role and locking down the sa account. Assuming you chose Windows authentication mode during setup (or have switched over to that mode since then), your first step is to create a local account with a strong password within Windows and then add that account to the sysadmin role within the SQL Server security. Once this is done, you would need to log in to Windows as that account and delete the local administrator account or group (depending upon the version of SQL Server you are using) from the sysadmin role.
Locking down the sa account is also a multistep process, you need to start by setting an extremely strong password then disabling the account. If you are running SQL 2005 Server or higher, then you should also rename the sa account to something unique.
ALTER LOGIN sa WITH NAME = [ZeroCool];
The “ALTER LOGIN” statements shown above will first disable the “sa” account and then rename it to “ZeroCool.”
Leverage Microsoft Knowledge
Microsoft deserves a lot of credit for providing in-depth technical documentation, tools, and recommendations at no charge to allow you to tighten up the security to the level you want. Microsoft's “Threats and Countermeasures” guide for Windows 2008[1] lists every security item that can be managed by group policy and includes information about the vulnerability, countermeasures, and potential impact of each particular setting. There are other earlier guides available, but each guide is completely backwards compatible and includes information about what versions each setting is applicable to, so there is no reason not to download the newest one.
In addition to the “Threats and Countermeasures” guides, both Windows 2003 and 2008 have Security Compliance Management Toolkits[2] that include preconfigured security baselines that you can apply to a Windows server utilizing the tools provided in the toolkit. Besides the tools for implementing preconfigured security baselines, each toolkit includes a security guide and some settings guides that explain what each baseline does and its impacts, as well as links to much more documentation on that particular subject.
Beyond these particular items, Microsoft actually provides its entire knowledge base to the public (the only difference between what is available to you online and Microsoft support personnel is some extra tagging) along with an incredible amount of information about the inner workings of the operating system and SQL Server on the msdn.microsoft.com site. They also have resources dedicated to basic SQL Server security and many of the basic security provisions of Windows (eliminating unnecessary accounts from the SQL Server application database, like the built-in administrator, and having strong authentication policies) also apply to securing the application.
Finally, many security organizations, books, and magazines provide publicly available recommendations to help you secure both your Windows and SQL Servers. The point we are trying to get across here is that you should actively leverage all of this information to determine the best way to secure both the Windows and SQL Server against the initial compromise that will provide an attacker with sysadmin-level authority and thus the ability to use stored procedure attacks.
Third Defensive Layer: Reducing Second-Layer Attacks
Unless there is a specific reason that you need a stored procedure (especially all of the “xp_” procedures), these should all be completely removed from the server. If there is some circumstance where you do need these procedures, but don't need them to always be active, then you should disable the procedures (if they are not already disabled by default).
Fourth Defensive Layer: Logging, Monitoring, and Alerting
Throughout this chapter, we have shown many different ways that SQL, and by extension Windows, security can be compromised by different attacks. Stopping these attacks is an ongoing battle that unfortunately will never end, but the best way to mitigate the impact of these attacks is by responding as effectively as possible. The crucial element involved in responding to any attack is to first recognize that something is going on.
The purpose of all of the stored procedure attacks described in the section “Dangers Associated with a Stored Procedure Attack” is to actually create accounts and gain membership in groups that provide privileged access to either SQL Server or the Windows operating system. In both cases, audits can be defined that will capture information about these events when they occur, and these will be stored in either the SQL Server or Windows event logs. Once the events are created, they can be actively monitored by a Microsoft solution such as System Center Operations Manager (SCOM) or a third-party service management system such as Tivoli, or moved across the network to specialized logging servers among many other choices for a monitoring infrastructure. Once this infrastructure is created, any solution you utilize should be configured to send alerts to administrators if different events set off the triggers you define and they should have policies and procedures surrounding the investigation of the alert and responses.
Identifying Vital Attack Events
The problem with auditing is that so much information gets put into event logs that it is difficult to sort out what is significant and what isn't. This gets even more difficult if you are trying to set up alerting policies because although you need certain information, too many false-positives means that the alerts will actually get ignored by your security personnel. If you understand the way an attack is perpetrated, however, you should be able to identify either a single vital element, or a series of vital elements, that must occur as part of the attack. By identifying these elements, you can do some security testing with the attack and understand what information will only be entered into an event log when this vital attack element occurs.
alt2 Epic Fail
In 2008, Countrywide Home Loans reported the loss of over 2.4 million customer records including social security and mortgage loan information.F The insider who performed the attacks confessed to downloading approximately 20,000 files per week over a 2-year period and selling them for a total of approximately $70,000.
Fhttp://articles.latimes.com/2008/aug/02/business/fi-arrest2
Implementing controls to audit data access may be able to detect large queries and provide early warning about potential data loss. Insider threats are just as dangerous as external threats, in many cases, more dangerous due to the access already provided to employees.
If you have followed the recommendation to ensure that the xp_cmdshell stored procedure is disabled, you have set yourself to catch the vital element of the deadly attacks we have described in this chapter because they all require this single action. When we used the sp_configure command to enable the xp_cmdshell stored procedure in the section “Accessing Stored Procedures” (Figure 3.1 shows this action), SQL 2008 actually logged the event shown in Figure 3.5 (this type of event is logged within SQL 2008 by default). This event provides a message that partially states, “Configuration option 'xp_cmdshell' changed from 0 to 1.” Because this message is so specific to this particular event, it makes it simple to set up an alert to security personnel if an attacker actually has enabled this stored procedure in order to try to carry out the stored procedures attacks discussed in each of the scenarios presented in the section “Dangers Associated with a Stored Procedure Attack.”
FIGURE 3.5. Stored Procedures Enabled Event Message
In this case, we got lucky because logging for this type of event was enabled by default in our test environment, and the message was so specific to the action we were protecting against that all we have to do is define the alert in whatever service we are using to actively monitor the logs. In most cases, making sure that an event is generated in your logs that is specific to your vital attack element and is precise enough to only occur in conjunction with that element may take some work; however, the added level of security you get from taking the time to do this is well worth the effort.
Fifth Defensive Layer: Limiting the Impacts of Attacks
The approach here is to look at what barriers you can put in place to stop an attacker from escalating their privilege at each point of a successful attack. One area to look at is limiting the access of the service accounts that SQL utilizes. Where possible, you should use named accounts rather than system, and these should be created as local service accounts rather than normal user accounts. If you take a look at Figure 3.4 from Scenario 2, “Keeping Sysadmin-Level Access,” you will see that in SQL 2008 these security precautions are there by default. However, that is not the case in all earlier versions.
In addition, you need to run SQL Server as its own server rather than sharing it with other applications. If this is an issue because of limited server resources within your environment, then you should utilize virtualization to separate the applications as different server instances running on the same physical device. Finally, you should never allow SQL to run on the same server as a domain controller. This is probably self-evident to you, but think about a backoffice server that may run SQL, Exchange, and a Domain Controller on the same server. Although this may seem like a more efficient use of resources, the impact of any of the successful stored procedure attacks we have shown here means that the attacker now owns your domain.
Summary
As part of the SQL Server code base, Microsoft has provided a way for prebuilt pieces of code to be stored within SQL Server itself and leveraged over and over again by DBAs and developers to perform many functions through a simple call to these procedures. Many of the functions that come with SQL Server from Microsoft are procedures that are meant to provide hooks into many of the administrative tasks that DBAs have to perform, but that also makes them prime targets for attacks. Microsoft has recognized this vulnerability and deploys its newest versions of SQL Server with these procedures disabled by default; however, they also provide very simple ways to enable them.
This chapter was able to explain how Microsoft SQL Server utilizes stored procedures and the purpose of each of the default system stored procedures. It should also have given you an understanding of how attackers can utilize these stored procedures and how dangerous they can be. Finally, you should now be able to grasp how to build the strongest possible defenses against SQL stored procedures attacks.