A Secure SQL Server
Microsoft SQL servers are one of the favorite targets for Internet hackers , primarily because of the activity of worms (e.g. SQL Spida, Slammer) spreading through this service, secondly because the access to unsecured, however Internet-connected SQL servers is quite easy. In this article I would like to describe the rules for safeguarding the Microsoft SQL Server service to help you, dear readers, prevent yourselves from the consequences of possible attacks.
One should start off by mentioning which kinds of attacks you are going to prevent yourselves from. To make this easier, these attacks should be divided into two categories: the first category is server-targeted attacks, which may be also grouped in subcategories, depending on what the purpose of the attack was:
- The operating system, i.e. the computer on which the SQL Server service is being run. The aim of such an attack is to gain the privileges of the administrator of the system on which the service is being run. A successful hack-in would allow the attacker to take control of the SQL Server service and give full access to the data.
- The SQL Server service: the attacker's aim is to gain the same access that the SQL services' administrator has. This attack may or may not be a prelude to a more complex operation, leading to an attack on the operating system itself. Of course, the assailant may take control of the data too.
The second category of attacks is the attempt to gain access to some data that needs to be inaccessible to a specific user. Unfortunately, a rather frequent situation occurs when the database doesn't have any means of limiting access, in which case such an action cannot be called an attack at all. It is often due to the curiosity of the users, which is not limited by database security policies since these would never have been defined, as the 'If security is set to none, everything just works' rule says .
Fig. 1. Classification of SQL server attacks.
Safeguard your operating system!
The fact that an SQL server needs to be safeguarded comes as a surprise to many administrators because the service is believed to be unable to cause any harm. This is however a fallacious argument. The SQL server administrator is authorized to run any code chosen by himself inside the SQLSERVR.EXE with the use of the xp_cmdshell, the xp_regwrite or self-created procedures. This allows him to communicate with the server's operating system without any limits. This possibility is built into the SQL Server service and it is not possible to take it off away from the 'sa' user, i.e. the server administrator. However, at any time the privileges of the 'sa' are limited by the SQL service policies. This implies that the lower the level of the privileges granted by the SQL server service, the less risky is its interaction with the operating system. The service's documentation informs you that the service does not need the administrator's authorization to perform ordinary tasks. A user, allowed to 'logon as service' is enough. However, some tasks require special allowances. These are, for example:
- Making the xp_cmdshell available to ordinary SQL server users. If the ordinary users of the SQL server service are to be allowed to run the commands of the operating system, then the xp_cmdshell extended procedure has to change the security token for one which is less privileged than the one inherited from the SQLSERVR.EXE service's process. This requires high authorization. Put differently, should you wish to make the xp_cmdshell available to ordinary users, you would have to increase the authorizations of the SQL server service in the operating system to the LocalSystem level. To be more precise, you would have to allow it to 'act as a part of an operating system' and to 'replace process level token'. I strongly dissuade you from using such a configuration for obvious reasons;
- Communicating with other servers via the network. A very simple rule will suffice in this case: the authorizations of the service's access via the network are equal to those of its account. The LocalSystem account has no such authorizations (there is one exception which won't be described here, i.e. the null session, because it violates basic security rules). This is why an account of a ordinary user will be enough for the SQL service to e.g. replicate data with other servers or create backups of the databases on network resources, etc.
- An SQL server working in a fail-over cluster, i.e. Microsoft Cluster Server. In such a situation the service has to have administrative rights on both cluster nodes. This is what Windows cluster services require.
Microsoft SQL Server is not just one service, the MSSQLServer. It is accompanied by the SQLServerAgent, used for performing tasks, which could be carried out by the administrator or by the users. This service may require administrative rights in the following situations:
- If you want to allow the ordinary users to run operating system tasks or scripts,
- To allow automatic restarts of the SQL Server in case of a breakdown,
- To allow for detection of the periods of low activity of the system in order to perform earlier-planned tasks in these periods.
If there is no need for these possibilities, the SQLServerAgent may be running on a common user account that has the right to be running as a service. Moreover, it is quite comfortable if both services (the SQLServer and the SQLServerAgent) use the same account. That simplifies administration without increasing the risks. The aim of this article is not to explain if or in what circumstances there is any reasonable use for those possibilities, which demand higher authorizations. This should be settled before the SQLServer is installed. In most cases the following principles may be a starting point:
- The MSSQLServer service is not run in the MSCS cluster,
- The users have no rights to perform the operating system commands, the xp_cmdshell as well as the SQLServerAgent-defined tasks,
- The SQLServerAgent service doesn't have to restart the SQLServer in case of a breakdown. Such a situation should cause the administrators' reaction and a manual intervention, not an automatic restart of the service. Monitoring the SQL server with the use of special tools would obviously make it easier to react in such situations,
- The SQLServerAgent service doesn't have to detect periods of low server activity,
- The SQLServer service may communicate with other servers in the local area network if it is overtly given the required rights.
When this occurs, the account on which the MSSQLServer and the SQLServerAgent services are to be run should be defined as a domain user with no special rights in the domain. Also, it should not have any rights to local logging in as a 'batch job' or a 'service' on other computers, and not even with the right of network communication with the servers when the GPO is defined inside the domain. Obviously, these authorizations are not enough for the service to run. An Organizational Unit (OU) should be prepared in the system and separated from it. The machine on which the SQL server is to be run should be placed in this OU, as well as other servers with which the service should be communicating within the use of the network. The GPO should be defined in the Organizational Unit. The chosen account should be allowed to log on as a service in the OU and may be accessed from the network. The question of the vulnerability of the password to attacks should also be considered. The password won't be used by a human, so it may reach any level of complexity. On the other hand, it should be subject to the rules that apply to password management within the organization. This means that the password has to be changed regularly, which involves the necessity of updating the services, which use the account. Another way of solving this problem is to create a password, which would be so complicated that it would make brute-force attacks impossible for long. Such a password would consist of at least 30 characters, half of which are special ones. Moreover, the SQLServer service should have at least read-only access to the directory in which it has been installed, e.g. C:\Program Files\Microsoft SQL Server\MSSQL, as well as full access to the data directories, to the event log and the authorization to read the whole root directory of the specified disk, which is often dedicated to data storage. The service should also be allowed to access the directory where the configuration information is stored:
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer. The SQL Server service should not be allowed to modify the data in the directory in which the data used for running the service is stored, i.e. HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSSQLServer. Otherwise, the 'sa' user will have the possibility of adding the "ObjectName=LocalSystem" value to this entry so that the service grants him LocalSystem authorizations as soon as it is run the next time. An example of such an attack, prevented thanks to a suitable definition of the authorizations placed in the registry, is presented in Figure 2.
Fig. 2. An unsuccessful "sa's" attack on an operating system.
This problem has been described in the MS02-034 security bulletin . When the process of configuring the systems comes to an end, one should ensure that there are no basic security bugs, like an "Everyone: Full Control" authorization in the C:/ root directory on the server. Another very important element is the plan of how the server should be communicating with the clients. Any communication from untrusted computers towards the SQLServer service and vice versa should be disallowed. The TCP 1433 (the service's default port) and the UDP 1434 (the identification of the SQL server, unnecessary if the user knows the service's TCP port number) ports should be blocked by the firewall. If users from remote networks are meant to use the SQL server, you should watch out for the security of the connection between them, for example with the use of the VPN technology. Setting up the SQLServer service on an unusual port is a totally inefficient safeguard. Any tool suitable for scanning ports would find your 'concealed' server in a limited (and usually short) amount of time.
The system administrators often delete the xp_cmdshell or other procedures, which allow use of the operating system, e.g. xp_regwrite, in order to secure their servers. Unfortunately such actions are completely futile. None of these procedures allows for a magical way of increasing the rights of the service to a level higher than the level configured by the system administrator, i.e. the service will have the same authorizations as the account it is being run on. The only efficient way of preventing the operating system from the SQLServer service are the system's safeguards such as registry and file system authorizations, required patches, hard-to-guess passwords, etc. If the mentioned procedures were somehow deleted, the 'sa' user may retrieve them with the use of the 'sp_addextendedproc' or by creating entries to the system tables of the 'master' database.
If there are multiple instances of the SQLServer service to work on one server, you have to bear in mind the fact that each one of them must not have any access to executable or data files and to registry entries used by other instances. This is how you'll make the instances' data and authorizations isolated. This type of isolation is obviously out of the question if all SQL instances had administrating rights on the server. If you install multiple instances of the SQLServer service on one computer, keep in mind its capacities and suitably share its resources - allocate dedicated processors and physical drives or RAID storage tables for each instance, also remember to limit the RAM usage. Another thing to remember is that whatever steps are taken, a huge burden to the server caused by one of the instances will slow down the others. This task of steps, which need to be taken, is to minimize the negative influence, as it is impossible to fully neutralize it. This is why running multiple SQLServer instances on one server is not a good idea.
Safeguard your SQL server!
The SQLServer service is equipped with advanced security management devices, which have been partly explained in the article that described the authentication mechanisms. A quick reminder: there are two ways of logging on to an SQL server:
- The one based on an own mechanism of SQLServer accounts, possible only in the mixed mode,
- Authentication based on the operating system (i.e. Windows NT Authentication), also called the trusted authentication.
The SQL service's most privileged account is the 'sa' or System Administrator account. It is always created during the installation and it uses its own SQLServer authenticating mechanism, which is available only in the mixed authentication mode. This means that setting the authentication mode to 'trusted' is a good way of blocking access to the 'sa' account. The 'sa' account has full control of the SQL service. You could say that the SQLSERVR.EXE process has the same privileges as the 'sa' account and vice versa: the 'sa' account may run any code with the privileges of the account used by the service. The server users who belong to the 'sysadmin' group have the same privileges as the 'sa' users do. The BUILTIN/Administrators account, i.e. the local group of the administrators of the system on which the service has been installed, belongs to that group by default. This account uses no password; it authenticates users using the trusted connection. From now on let us say that the 'sa' abbreviation will stand for everyone from the 'sysadmin' group. On account of such a configuration, the administrators of the server on which the SQLServer is installed, have the possibility of logging onto the 'sa' account and gaining unlimited access to the data and to the service management. A contradictory relation does not occur; the 'sa' account has the same privileges as the SQLSERVR.EXE has.
Fig. 3. Access to 'sysadmin' privileges
The account itself is the only one authorized to manage the 'master' database which is the main database, containing information on other databases, user accounts, files containing data, etc. An SQL server cannot be run without this database. An empty one may be created with the use of the 'rebuild master database' tool if such a necessity occurs, and subsequently the right database from the security backup should be recreated in its place. Every database has a list of local users. On this list the 'dbo' (which stands for 'Database Owner') account is fully authorized to manage the database, whereas 'guest' stands for every user of the SQL server. If you create a 'guest' account in any of the databases, it will be available from any account, which has access to the SQLServer service. The databases also have roles (i.e. user groups). A special 'public' group defined in each of the databases signifies all the users of this database. The 'master' database has only two users and that must not be changed. These are:
- The 'dbo' which is a local name of the 'sa' account in the 'master' database,
- The 'guest' which allows every user to perform basic operations in the 'master' database after having logged onto the SQL server.
The 'public' groups' and the 'guest' users' rights in the 'master' database are very limited as they are not allowed to establish databases or accounts, read passwords etc. The access rights of an ordinary user to other databases as well as the rights of access to the tables and procedures they contain have to be configured in an unclassified way. This will be explained later on in the article. Now, let us focus on securing access to the 'sa' account.
The main safeguard of the 'sa' account is the password, which must not be null. It is, however, set up like that by default after the MSDE installation is finished. This may occur no matter what the installation options were in the SQLServer's case. Just after having finished the installation you have to log onto the SQL server by running the "osql.exe.-E" command from the administrator's account. Subsequently, use the "sp_password @new='this-is-the-'sa'-password',loginame='sa'" procedure but not the mentioned password. If the server were not running the mixed mode, that would be the only password in the 'master' database and it would remain unused. A server not running in the mixed mode won't allow logging on the 'sa' account with the use of the password even if it was given correctly. However, the password has to be set up because a change in the mode of logging on isn't usually subject to security procedures identical with the surveillance of administrating passwords. A careless change of the authentication mode before setting up the 'sa' password is a frequently committed and serious mistake often used by worms. You probably already noticed that authentication based on passwords saved in the 'master' database is much more hazardous than Windows authentication. This is why applying the SQL server should be designed in a way, which would allow every application to establish a trusted connection with it. That would allow turning on SQLServer-based authentication mechanisms and using Windows-built-in account-securing mechanisms such as the account lockout, which helps to prevent brute force attack attempts. Moreover, the server's configuration should include a command to create a log of logon attempts so that it is possible to trace attack attempts. A considered restriction of access to the SQLServer's extended procedures is another safeguard. These procedures allow an ordinary user to take advantage of the programmer's trivial mistakes to gain 'sa' authorizations because these functions come from external DLL files and are run internally in the process of the MSSQLServer service. They run on the same rights as the 'sa' accounts do, so a simple fault, like a buffer overrun or an erroneous interpretation of the user's arguments may make the 'sa' account unusable. The faults in security bulletins no. MS00-092, MS01-060, MS02-020 are examples. Unfortunately, disallowing users from accessing extended procedures is not a warranty of full security. You should additionally ensure that the OLEDB libraries available from the level of the SQLServer service, i.e. the MS02-040 security bulletins are deprived of such errors. Apart from that, the MS02-007, MS02-038 and the MS02-039 functions contain internal faults. The most secure way is to install Service Pack 3  just after the SQLServer service has been installed. I strongly recommend that all readers interested in SQLServer security become familiar with the complete list of security bulletins . Moreover, it is worthwhile to enter the great www.sqlsecurity.com web site, which publishes up-to-date and important information and advice on safeguarding SQLServer services.
Safeguard your database!
Unfortunately this is one of the most neglected elements of many commercial databases. As I explained earlier, every database has defined groups. Apart from the special 'public' group, the db_datareader and the db_datawriter are also particularly interesting as regards data security. Both of these groups have built-in rights to enter, then read and write all the database tables. None of the special groups is allowed to perform all of the procedures in the database. I should also remind you that the 'dbo' user has full rights to all objects (tables, procedures, definitions, data integrity, etc.) in the database. All users of the db_owner role have the same rights as the 'dbo' user. The reason for data's vulnerability to unauthorized access is usually an incomplete implementation of the database. The programmers who prepare a database in their environment often forget to plan its safeguards, which means that when the base is being implemented, there are no plans for security. Completing an implementation requires that such a plan is created quickly, (which is often impossible) or else giving all users full access to all tables by placing them in db_datareader and db_datawriter groups, granting them the right to perform all procedures. In most cases this is done manually or with the use of a simple script, which derives the name of each procedure from the system tables and gives the 'public' group authorization to use it. You could place all users in the db_owner group instead of carrying out such a complicated job but this is a fatal error in the context of data security. A plan of an application's safeguards should be created while the application is being created. By the way, the SQL server's security system's specifics should then be used as it allows the definition of authorizations at the level of the application, incredibly efficiently. Some objects of the database, like procedures, functions and views, may use other ones, e.g. a view may use tables, another view or a function, whereas a procedure may use views, functions, tables and other procedures. A user who gained access to a procedure, a view or a function may use it even if he has no access to objects used by this procedure on condition that the owner of the procedure, view or function is at the same moment the owner of these objects. This is a simple way of implementing conditions, which limit access to data in the objects. You could, for example, create a 'my_data' view containing the "WHERE UID = USER_ID() WITH CHECK OPTION" condition which uses a 'personal_data' table. No user has direct access to 'personal data', so they have to use 'my_data' view. This view has a rigidly fixed condition to render only those lines that suit the user's ID accessible. This condition is an example of implementing the security in the application, so that it cannot be passed round by the user. A similar situation would occur if the 'A' user created a 'ca' table, a 'pa' procedure for manipulating the data in the table and if he gave rights for the 'pa' procedure to the 'B' user. 'B' could use the data included in the 'ca' table with the use of the 'pa' procedure. This situation is explained in Figure 4.
Fig. 4. A simple scheme showing how the 'B' user may access the 'A' user's data via the 'pa' procedure.
Subsequently, if 'B' created a 'pb' procedure which used 'pa' and granted 'C' the rights to it, 'C' wouldn't be automatically allowed to use the data from the 'ca'. That would happen because the 'pb' procedure would be using the 'pa' procedure, which does not belong to 'B'. For this reason the 'C' user's access to 'pa' would be checked, and he would be able to correctly perform the 'pb' procedure only if he was granted the authorization to use the 'pa' which is also used by 'pb'. This is called a 'broken ownership chain'. You can see it in Figure 5.
Fig. 5. Additional check of the 'C' user's privileges for the 'pa' procedure due to the 'broken ownership chain'.
This mechanism allows the programmer to create vast and efficient methods of limiting data access. However all of this only makes sense under one condition: the users must not be assigned to the db_datareader and not even to the db_datawriter groups. Otherwise all safeguards are for nothing because all the users would have direct access to all data! This way of implementing safeguards is tricky, for the popular 'sp_executesql' procedure allows one to execute a dynamically created wording as an SQL command using the authorizations of the user who ran the procedure. Let us get back to the example given earlier. If 'A' used the 'sp_executesql' when writing the 'pa' procedure to manipulate the data in the 'ca', then 'B' has to gain specific authorizations of access to the 'ca' table. Otherwise, the 'pa' procedure will not be running . This mechanism forces the users to gain authorization to access the tables even if the plan for the application's security assumes that they are not using the tables directly. This is how the users may gain access to data while bypassing the procedures or functions. A huge fall in data security is experienced.
At first glance, this seems complicated. However, three simple principles will allow you to build an efficient mechanism to safeguard the data:
- The database users should have no direct access to tables
- The users may access data only with the use of functions, procedures or views
- The 'dbo' is the owner of all objects in the data (to avoid the 'broken ownership chain' problem)
Two additional principles concerning user groups make it easier to manage the authorizations inside the database:
- Authorizations for views, procedures and functions are granted to groups (or roles) of users depending on the tasks they perform and the required level of data access they need.
- The authorizations of specific users are regulated by their membership in particular groups or roles.
Fig. 6. Subsequent layers safeguard the users' access to data.
Choosing some principles at the start of the process to create an application will let you reach a high level of data security, so it is important to consider this seriously. You would never reach such a high level of security if you added the safeguards later. In addition, such a process would be very expensive because of the required programming "overhead", tests and possibly (or even probably) complications.
As a final note, may I strongly recommend that all those who are interested in SQL security read Microsoft's documentation describing the basic SQL Server security practices . Very interesting materials concerning SQL security are published by NGSSoftware , SPI Dynamics , and obviously by SQL Server Magazine .
 What happens if you don't Mark your Controls?
 http://www.microsoft.com/technet/security/bulletin/MS02-034.asp, "Incorrect Permission on SQL Server Service Account Registry Key"