Hardening SQL Server 2000
| Before we continue, there is something you must know: If you follow the steps outlined in this section, your SQL Server is in an unsupported configuration. If you call Microsoft's support services, you will get only "best efforts" support. If you follow all the steps outlined below, your SQL Server will most certainly be incapable of performing certain tasks that you may or may not need. It is highly advisable that you try these steps in a virtual machine before you start modifying a production server. It is quite possible that not all of these steps will work in your environment. Use caution when applying them. This section covers how to lock down SQL Server in a " high-security " configuration, above and beyond the recommendations in the best practices paper. However, "high security" also means "limited functionality." There is a reason for most of the permissions and privileges granted to the SQL Server service account. Without them, certain features will not work. Although we cannot predict all of the features that will break, we will do our best to outline which they are. Think of it this way: "High security" is a bit like the Berlin Wall. It does not stop all attacks, but it does stop many of them. It also gets in the way a lot, is very cumbersome to tear down or work around, and is surrounded by minefields. High security is the same. High security is for systems whose compromise would result in loss of life, state secrets, or significant amounts of money. High security is not for general-purpose use, which is why SQL Server does not come installed that way by default. Now, after you have decided what you want to do with SQL Server, you should take a step that all installations should take, regardless of their security requirements: move the data and log files to a separate directory. As a general rule, data should reside where it can grow freely without disrupting operating system or application binaries and temp files. We will not tell you how to accomplish this. Think of it as a prerequisite. If you do not know how, the rest of these steps are probably too advanced for you! It is important that you are a relative expert at SQL Server before starting what comes next ; otherwise , you will not be able to troubleshoot and undo the changes to solve any problems. If you return to Chapter 2 briefly , you will remember that the SQL Server was used as the initial entry point into the network. This worked because the service account could call tftp.exe, upload tools to the server, and then execute those tools with high privilege. To stop these, we will therefore first turn to how to secure the service account. Securing the Service AccountTo secure the service account, we start the same way we did before, by creating a new user account; call it _sql for the sake of discussion. (For some reason, we got used to denoting service accounts with a leading underscore years ago.) Use a very strong pass phrase for the account, as per Chapter 11, "Passwords and Other Authentication MechanismsThe Last Line of Defense." We also revoke all log on rights for the account, rendering it unusable locally as well as from the network. If you use the passgen tool discussed in Chapter 11 to configure the password, it will actually revoke these logon rights for you. After you create the account, perform a couple of additional modifications:
We now have a completely worthless account. It really cannot do anything. Now open up the SCM and change the SQL Server logon account. You must not do this in Enterprise Manager ; that is how we get all the permissions listed earlier. This is shown in Figure 14-2. Figure 14-2. Configure the new service account in SCM. SCM will automatically grant the account the "Log on as a service" (SeServiceLogonRight) privilege. However, if you now try to stop and restart the service, you will get an error message that tells you "Access Denied ." As descriptive as that error message is, it does not have much information to help resolve the problem. To do that, we turn to some third-party tools. Now go to http://www.systeminternals.com and download File Monitor and Registry Monitor. These tools are your new best friend! They monitor all access to the file system and the Registry, respectively. That is a lot of accesses , however! To make the process more manageable, we set up some filters. Open each application, press Ctrl+L, and set up filters as shown in Figure 14-3. Note that although Registry Monitor enables us to monitor only errors, which is what we are looking for, File Monitor unfortunately does not have such an option yet. Figure 14-3. File Monitor and Registry Monitor configuration settings. At this point, we are ready to try to start the service again. The first of our monitors to give us any feedback is File Monitor, as shown in Figure14-4. The problem shown in Figure 14-4 has a very simple explanation. Services.exe, in the process of attempting to launch SQL Server, is impersonating _sql to read the SQL Server binaries. Well, we forgot to grant the service account permission to the binaries at %ProgramFiles%\Microsoft SQL Server\<InstanceName>. Oops. That is easily rectified. Go to that directory and grant _sql read access to the binaries. Figure 14-4. File Monitor shows us our first problem. TIP: Sometimes it is really hard to find the problems, particularly in File Monitor. To make it easier, press Ctrl+E (to stop logging) and then Ctrl+F to find. Type Access Denied in the Find dialog and press Enter. Just cycle through the output that way until you find something that appears to be a problem. Just do not forget to press Ctrl+E to start logging again when you have finished. Having now given the service account the right to read its own binaries, we will try again. Press Ctrl+X in File Monitor to clear the log window and try starting SQL Server again. This time you get a different error message: "The application failed to initialize properly (0xc0000022). Click on OK to terminate the application." Going back into File Monitor, we find that the problem is that sqlservr .exe is trying to open %ProgramFiles%\Microsoft SQL Server\<InstanceName>\binn\opends60.dll. What happened here is that during SQL Server installation, the installer removes the inheritance bit on all the SQL Server directories. Hence, no matter how we set permissions on %ProgramFiles%\Microsoft SQL Server\<InstanceName>, none of them have propagated below that point. To rectify the situation, go back into the permissions settings for %ProgramFiles%\Microsoft SQL Server\<InstanceName> and set up propagating permissions, as shown in Figure 14-5. Figure 14-5. The SQL Server installer resets all inheritable permissions. Back to SCM and try again. This time it gets really interesting. See Figure 14-6 for one of the stranger errors in Windows. Figure 14-6. The strangest error in recent memory. The error is actually explained by the Registry Monitor output. Go to Registry Monitor, press Ctrl+F and type ACCDENIED . Note that for some reason Registry Monitor does not use the same string to denote an access denied as File Monitor (Mark, when are you ever going to fix that?). The problem occurs when we try to read HKLM\SOFTWARE\Microsoft\MSSQLSERVER\<instance name>\CurrentVersion. The code 0x20019 in the Other column denotes a request using a KEY_READ access mask; in other words, just a read access. We need to put a read access control list entry (ACE) on HKLM\SOFTWARE\Microsoft\MSSQLSERVER\<instance name >. Back to SCM and try again. Now we get an error message that says basically, that "something went really wrong, but I have no clue what it is. Why don't you ask your system administrator for help?" Because we are the system administrator, we have pretty much exhausted the escalation chain at this point. Luckily, both Registry Monitor and File Monitor know what happened. The first problem is in Registry Monitor. We tried to access HKLM\SOFTWARE\Microsoft\MSSQLSERVER\Setup with 0x1 (KEY_QUERY_VALUE) access. We did not grant any permissions there, better do that. Grant it read permissions, that is all we need. That is not the only error we have, however. For instance, we tried to access HKLM\SOFTWARE\Microsoft\MSSQLSERVER\<instance name>with 0x20006 access. That is KEY_WRITE access; in other words, we tried to write that key. Why is that? It turns out that key holds all kinds of volatile configuration information such as the network libraries that clients can use to access this server. You really do need write access to this key. Grant the service account KEY_CREATE_SUB_KEY and KEY_SET_VALUEthe difference between KEY_READ and KEY_WRITE. After you have done that, notice that File Monitor is also throwing several errors. The problem there explains the strange (useless) error message. SQL Server is trying to write to its log file. This does not work because we did not give the account any write permissions. Although we add a "modify" ACE to the <instancename>\LOG directory (which should not be on the boot partition by the way), we would also do well to do so on the <instancename>\DATA directory. SQL Server tends to work much better if it is allowed to write to its databases. If you now try to start SQL Server again, something astonishing will happen: it starts! Believe it or not, we have just created the minimum set of permissions to start SQL Server. Note that this does not mean that it will be able to do everything we expect. The fact that we can launch a process does not mean that the process will do anything useful. The only way to know for sure is to try. If we try the www.victimsrus.com site used in Chapter 2, we find that it does actually still do something useful! We have finally arrived at the minimum set of permissions needed for our site. They are as follows :
Note that these may not be sufficient minimum permissions for your installation and that you may still encounter problems with only these permissions set. For instance, in our tests, we were getting access-denied entries on the perflibs, both in the Registry and in the file system. If we do not care to performance monitor this SQL Server, we will leave those alone. If we care about performance monitoring, which many sites do, we must continue the above process until we have the permissions needed for that to work, too. The set of permissions shown above has been used successfully on several nonclustered SQL Servers that feed data to a Web site over both trusted and untrusted connections. It will not work on servers that participate in SQL replication. In your installation, you may need to keep iterating through the tools to add additional permissions. It is also worth pointing out here that you do not need Enterprise Manager on many production SQL Servers. Enterprise Manager is used only to administer the system. You can do that through isql.exe or osql.exe as well as through Enterprise Manager remotely. You may want to consider removing Enterprise Manager from the production servers, lest you accidentally change configuration on a production serveror someone else does. This same process to discover the minimum set of permissions required to make an application work can be applied to almost any situation where you need to have an application run with reduced permissions. For instance, you may be able to use it to make your favorite game run as a nonadmin. It is also worth noting that the initial steps of the attack demonstrated in Chapter 2, the part up to and including getting a remote command shell on the SQL Server, will still work even with these minimum permissions. However, after you have that shell, it will be very difficult to go any further since the account you are running as is extremely limited. The reason the shell still works is because we still have writable directories to upload the file into. We of course have write access to the SQL Server directories, but we also have write access to the C:\ since Everyone has been granted that. Even though our _sql user has been removed from the Users group, it is still granted all the permissions that Everyone has. Note that you should not try to go through the default ACLs and replace Everyone with something else, particularly not Authenticated Users, as many people attempt. First, _sql is an authenticated user; second, if you try to perform that kind of wholesale ACL change, you will almost certainly end up with a configuration with very strange problems. For instance, we have seen people destroying the recycle bin and making the administrator's profile world readable doing this. It is not really worth doing. There are other ways to block this attack that are more meaningful and less likely to cause strange side-effects. SQL Authentication OptionsWhen users and applications connect to SQL Server, they authenticate to the database somehow. SQL Server supports two authentication options. One is Windows-only mode, meaning that all access is authenticated using Windows (domain or local) accounts. The other option is SQL Server and Windows mode. (These at one point were called "native" for Windows-only, SQL Server, and "mixed mode" for both.) In mixed mode, SQL Server can use either Windows accounts or its own accounts, defined and usable only within the SQL Server. Using mixed mode, you lose the granular control you get with Windows accounts, and you lose much auditing capability and the stronger protocols used for authentication in Windows. Many developers write applications that use SQL Server login because they think they will be easier to use when there is a firewall between the SQL Server and the front-end application. However, because SQL Server can tunnel Windows authentication through its own protocols, that is not necessarily the case. On the other hand, it works properly with older applications written for SQL 6.x where SQL Server authentication was the default. Therefore, for application compatibility, SQL Server authentication may still be needed, but applications that require it should be rewritten with all possible speed to use "trusted connections," another name for native mode. After you have removed all those applications, or decided that you do not care about breaking the ones that are left, set the authentication to native mode. In native mode, Windows accounts and groups are are mapped to SQL logins. This makes things much easier to manage. You do not need to maintain passwords within applications, and you can configure data accesspermissions to Windows users much more easily. In addition, you get the benefit of the reasonably strong authentication protocols used in Windows. Finally, some vulnerabilities have surfaced that apply only to mixed mode, such as accidentally getting the SA password stored in a log file. Securing Stored ProceduresOne of the most powerful methods for securing SQL Server is to drop some of the built-in stored procedures. For instance, the exploit in Chapter 2 relied heavily on xp_cmdshell. Is it really necessary? If you are running replication, yes. If not, probably not. You can drop it easily enough using this command: sp_dropextendedproc xp_cmdshell However, to get the full effect out of this, you also need to drop the remaining procedures defined in the DLL that contains xp_cmdshell. The DLL involved is xplog70.dll. The following procedures are defined inthat DLL:
To drop all of them, run this command: sp_dropextendedproc xp_cmdshell sp_dropextendedproc xp_enumgroups sp_dropextendedproc xp_logevent sp_dropextendedproc xp_loginconfig sp_dropextendedproc xp_msver sp_dropextendedproc xp_sprintf sp_dropextendedproc xp_sscanf Removing all those will of course break things. For instance, Document Tracking and Administration (DTA) in Microsoft Biztalk uses these extended stored procedures as does the SQL Server Distributed Management Objects (DMO). If you are not using these features you may be able to drop all these extended stored procedures and in that case you can remove xplog70.dll. The reason you need to do that is because otherwise an attacker executing code as the sa or as a sysadmin (including the service account) can add the xprocs back. You want to ensure that you are not using any of them, however. This means you have to analyze the dependencies on them. For reference, the built-in dependencies on xp_cmdshell are listed in Table 14-1. You can get the dependencies on any stored procedure or extended stored procedure by right-clicking it in Enterprise Manager, selecting All Tasks, and then selecting Display Dependencies. Of the extended stored procedures shown above, other than xp_cmdshell, only xp_msver has dependencies in a default install. Sp_addqueued_artinfo and sp_MSInstance_qv both depend on it. Table 14-1. Dependencies on xp_cmdshell
You may want to think about whether you want to get rid of several other extended stored procedures, too. There are 170 extended stored procedures in all. Take a look at all of them. Should you decide later that you need them, you can use sp_addextendedproc to add them back in. In addition, the SQL Server Agent contains all the same functionality with different names . It is used to schedule jobs, such as maintenance operations. In environments that do not use this functionality, it can be disabled. Realize, however, that scheduled jobs will break if you do so. The stored and extended stored procedures we just discussed do provide useful functionality, and, by default, they are available only to sysadmins . If you do need them, consider evaluating the permissions on them instead and seeing whether they should be tightened up. The defaults are not bad as long as no applications are accessing the database as a sysadmin, but you can consider changing them in your environment. If your applications are accessing the database as a sysadmin you should either modify the applications, or try to remove the functionality from SQL Server as a defense- in-depth measure. Speaking of permissions, do you really need any for Public? Public is essentially equivalent to Authenticated Users in the operating systemit contains all users who have authenticated to the system. A user who does not have a login for a particular database would still be able to access a lot of resources that are available to Public. To be precise, such a user would be able to access 1,015 objects by default. If you want to allow access only to specifically defined objects, you should revoke these. The easiest way to do that is to turn to Appendix B, "Script to Revoke SQL Server Public Permissions," and run the script in there. That will generate another T-SQL script that you can paste into Query Analyzer and run. That latter script will revoke all public permissions that can be removed and still have the system work. You will get some errors; that is to be expected. On certain objects, you cannot revoke the permissions. We are now left with an SQL Server installation that is very hard to exploit. At this stage, the exploit in Chapter 2 will no longer succeed because xp_cmdshell has been dropped. Even if the bad guy should happen to get a command shell on the server, he will have one running as an extremely limited user that will be very hard to elevate to a higher context unless he can get additional tools on the system. We have seen attackers who add the stored procedures back in, for instance. To block that, you need to prevent them from getting their tools up onto the server and executing them there, as discussed in Chapter 10, "Preventing Rogue Access Inside the Network," and Chapter 12, "Server and Client Hardening." Before you leave your SQL Server, you should write some new stored procedures. Write one instead of each of the hard-coded queries in all the apps you have accessing the database. It is beyond the scope of this book to address this in detail; but in general, you should avoid ad-hoc queries at all cost. Stored procedures will not necessarily prevent problems such as SQL injection, but you will often have more control over what gets executed on the database server when you use a parameterized stored procedure. Howard and LeBlanc address this at length in Chapter 12 of Writing Secure Code , 2nd Edition. In the end, however, bad code is bad code, and it makes no difference whether it is in a stored procedure, C in a native application, or VBScript in a Web page. Everything we have discussed in this chapter so far is about making you more resilient to bad code, but nothing can make you immune to it. The things we have talked about here are, as mentioned previously, not entirely supported. Microsoft has created Knowledge Base article 891984 to discuss these issues and the supportability surrounding them. Referto that article for more information on exactly what support you can expect for which of these issues. It is available at http://support.microsoft.com/?id=891984. |
EAN: 2147483647
Pages: 219