Preparing for and Responding to Threats and Attacks

One of the primary jobs of any administrator of a computer system is securing that system from attack. This is true whether you are a SQL Server DBA or a Cisco router administrator. Security is one of your primary concerns because without good security, any other structures you have set up could be deleted, altered, or otherwise compromised.

The following sections will cover a variety of ways that attackers can harm your SQL Server database servers, including both internal and external attacks. We will cover both intentional, malicious attacks and unintentional or incidental mistakes that could harm the integrity of your server.

Note 

We will refer to anyone who attempts to change, alter, gain access to, or otherwise inappropriately access a SQL Server or its data as an attacker. This could be an employee or a criminal on the other side of the world who has never contacted your company before.

Understanding SQL Server Injection Attacks

SQL injection is one of the few ways that your SQL Server is vulnerable to external attack that cannot be prevented with some Windows or network policy. This type of attack came about in the era of web pages that accept anonymous input from users all around the world.

SQL injection involves submitting various SQL commands inside an input stream in an attempt to alter the behavior of dynamic SQL commands. An example is the easiest way of explaining this hacking technique.

Suppose that you have built some application, not necessarily a web page, that accepts a name and password from the user. You take those values and submit them to the database server to authenticate an individual with a dynamic SQL statement such as this:

 Select userlevel from Users where name = '%1; and pwd = '%2'

In this example, %1 and %2 would be replaced by the values submitted by the user. Suppose the user typed Steve for the user field and Rk#js2DF for the password. The SQL Statement executed on the database server would look like this:

 Select userlevel from Users where name = 'Steve' and pwd = 'Rk#js2DF'

Your application would return the results and somehow use this data to either deny access to the user or allow them to continue inside the application. This is exactly the type of authentication code that is used in thousands of web and desktop applications. But suppose an attacker entered Me for the user and this for the password:

 noidea'; select * from users--

The resulting SQL statement would be as follows:

 Select userlevel from Users where name = 'Me' and pwd =    'noidea'; select * from users--

This code would probably result in no result set being returned for the first SELECT statement, but it could result in the entire contents of the Users table being returned. It could even be displayed on the screen, depending on the code of the application, thereby giving the attacker all the usernames and passwords for your application. Even if the table name were not correct, the attacker would likely gain some knowledge of the system from the errors returned. In this case, no destruction of data is occurring, but imagine the attacker entering a DELETE or DROP TABLE statement.

The first reason this works is because dynamic SQL allows the end of batch character, the semicolon, to be placed in the stream. Second, the attacked uses the comment characters, the double hyphen, to prevent a syntax error. This is the most common type of attack, and almost any command you can think of can be executed in this way.

To prevent this type of attack, you can change a number of things in your application architecture. Each of these has its own advantages and disadvantages, and you should try to incorporate as many as possible to keep your servers secure.

Using Stored Procedures

The main reason that most SQL injection attacks are successful is that developers often want to construct SQL statements in code rather than embed them inside a stored procedure. By using a stored procedure to hold the SQL code, requiring parameters, and executing it with a stored procedure call in ODBC or ADO.NET, you almost eliminate the opportunities for SQL injection.

Validating Input

An application should never expect some particular format of input from a user. It should use validation to ensure that any data entered by a user conforms to the expected type and length needed by the application. A phone number field should not allow a binary object to be embedded; a sales quantity should not allow semicolons or alphabetic characters. The application should be designed with validation for every place that a user inputs data. The validation should include data types, lengths, and formats.

In addition, characters that have special meaning in SQL Server, such as semicolons, double hyphens, and escape characters, should not be allowed or should be converted to a format that prevents them from being interpreted as command characters. XML documents should also be validated before being passed on to SQL Server.

Understanding Denial-of-Service Attacks Specific to SQL Server

A denial-of-service (DOS) attack is based on one principle: preventing access to a particular service. You can accomplish this by changing the configuration of some element of the network so clients cannot find the service or overwhelming the service so that it cannot respond to clients. The latter is the more common type of DOS attack, but either can occur. In this section, you will examine each of these attacks and ways for preventing their success.

An attack that prevents clients from connecting to a SQL Server is characterized by a lack of network traffic or activity on the SQL Server. In this case, clients never communicate with the SQL Server; no alerts or errors will be raised on the SQL Server, and only if the lack of traffic is reported somehow will administrators be aware of it. Often this occurs because of some type of network configuration problem, DNS, firewall rules, routing, and so on.

Preventing this type of DOS attack requires that system administrators actively monitor their servers’ low as well as high levels of activity. If you know your server should have 100 transactions an hour and you have seen only 2, then you can investigate further. Often this type of problem requires assistance from network administrators to diagnose and correct the issue. You may also notice an abnormal number of connections being opened and not used, which can cause the server to stop responding as well.

If you can identify a specific set of computer addresses that are sending most of the traffic, then you should have your network engineers block traffic from those addresses. If it is too large a number of addresses to block, you might need to block all traffic to your SQL Server until the attack subsides.

Denial-of-service attacks that overwhelm your SQL Server instance are more common and are characterized by abnormally high levels of network traffic or transactions on your server. These can come from a single workstation that has a virus or other misconfigurations or from thousands of compromised workstations across the Internet that are all executing some software designed specifically to attack your server. The latter case is a distributed denial-of-service (DDOS) attack. The response to this type of attack is the same as a single-computer DOS attack.

Understanding Virus and Worm Attacks Specific to SQL Server

Over the years, SQL Server has proven to be a fairly secure environment, especially when compared with the Windows operating system, many types of mail software, and other database platforms. However, there have been places where SQL Server has been directly targeted by a worm.

The 2003 SQL Server Slammer worm was the most devastating attack on SQL Server platforms ever seen. This worm attacked unpatched SQL Servers and spread through the 1,434 UDP packets SQL Server uses to identify which instances are running. Inside of hours, many companies around the world had to shut down their networks and take all SQL Servers offline until they could be patched.

Since the release of SQL Server 2005, there have been no security patches released and no attacks against this version published as of the time of this writing in the spring of 2007. However, that does not mean that none will occur in the future.

Worms and viruses are two types of software that replicate themselves, spreading to other computer systems through the network. The technical difference is not important; both require a rapid response to prevent your systems from being overwhelmed.

If you suspect that one of your SQL Servers is infected with a worm or virus, you should take a few steps as quickly as possible:

• Isolate the server.   The best way to do this is remove the network connection immediately if possible. This prevents the spread of the virus to other systems. If you can avoid turning off the power, do so in order to preserve any evidence that exists. If you suspect that data loss may occur, power the system down immediately.

• Verify the infection.   Most DBAs will not discover a virus or even understand where it exists in their system. Usually one of the large antivirus or security firms will publish the signatures and other methods of determining infection. You should obtain all the technical information you can and verify the infection. If you have powered down the system, we recommend booting it from some removable media and another instance of Windows, such as Windows PE, to verify the infection. In this way, you can prevent any further damage from the infected Windows or SQL Server software.

• Remove the infection.   Updated antivirus signatures or patches released by Microsoft might be the solution. You should always test these patches on another system before applying them to your production servers, even in times of crisis. At the least, install a similar SQL Server instance on another computer and apply the patch to be sure it does not break the SQL Server software. Apply the patch, and then use your verification method from the previous step to determine whether the infection is gone.

• Monitor systems for a period of time.   Most virus and worm breakouts occur within a few days and then die out as more and more systems are patched. Be sure that you monitor your servers for some time following the incident until you determine that the outbreak is past. Be especially careful in monitoring servers that never were infected to be sure they do not become compromised before they are patched. All servers that were not infected should be patched as soon as possible.

• Determine a root cause.   You should reexamine your security policies and procedures in light of any outbreak. This allows you to determine whether there is any way to prevent future issues.

The best defense against worms and viruses is a good prevention program for your systems. Although SQL Server itself is rarely a direct target, it depends on your network, the Windows operating system, and possibly other software. To defend against future attacks, you should ensure that you are proactively protecting your platform:

• Protect SQL Servers with firewalls.   Every SQL Server should be protected from the Internet by at least one firewall, and preferably two. Your SQL Server should not be directly accessible from the Internet, meaning that no network address translation (NAT) or router rules exist to allow a direct connection. In addition, any machines in a demilitarized zone (DMZ) that access your SQL Server should have explicit rules that limit access to those machines only and not any machine on the DMZ.

• Install antivirus software.   One of the main ways of preventing viruses is having antivirus software on all your servers. Keep the signatures up-to-date, and exclude your SQL Server log and data files from scans.

• Do not browse the Internet from servers.   One of the ways that many infections occur is through malicious websites that are visited by users. A server should never be used to visit websites, even if you are sure they are safe. Instead, do research, download files, and the like, from another workstation, and copy the files to the server.

• Install patches in a timely manner.   Most patches are released to correct specific problems in software. It pays to apply these patches as soon as possible after they are released to ensure that your server is running properly. There are problems with patches at times, and they can change functionality, especially with new features being introduced in service packs, but they are still your best defense against problems. Usually Microsoft requires the latest service pack before it provides support, so keeping your servers patched helps prevent delays in receiving support from Microsoft.

• Use strong passwords for all accounts.   This especially applies to service accounts, but all accounts involved in SQL Server should have strong passwords. This means you should never use blank passwords, even for SQL Server Express Edition installations. At least one SQL Server 2000 worm spread because many MSDE installations had a blank sa password. Also, ensure that default passwords are changed, especially for third-party applications that run on SQL Server.

• Secure Database Mail.   SQL Server 2005 has multiple mail systems: legacy SQL Mail, SQL Server Agent Mail, and Database Mail. Since many viruses and worms spread through mail systems, be sure these accounts are secure with the least amount of rights required and that they have strong passwords.

Real World Scenario-Real-Life Virus Response: Slammer

When Slammer was discovered on the network of an ERP software company I worked for, I had just returned from vacation and went straight to work without unpacking. The traffic was so overwhelming that the entire network was quickly shut down at all the switches and routers. Within a few hours, we had identified the worm and received a patch from Microsoft to clean the servers.

I spent hours working on a deployment code with others in the IT organization, and we were able to quickly patch hundreds of database servers by splitting the workload. We had a couple people burning CDs with the patch and others physically visiting the servers in our data center, while a number of us used remote KVM systems to apply the patches. Within 24 hours we were ready to turn on the network.

And we were forced immediately to turn it off again. We had not realized the extent to which we had MSDE versions of SQL Server deployed on desktops in our organization. Our main product required MSDE installed locally, and we had more than 10,000 desktops and laptops on the network.

Needless to say, the patching solution for all these machines took nearly another 24 hours to complete. But it is a good reminder that more and more desktop machines will be running MSDE or the new SQL Server 2005 Express Edition. You should also be aware that many cell phones and PDAs have the capability to run SQL Server Compact Edition as well, giving attackers one more place they can target.

-Steve Jones

Understanding Internal Attacks Specific to SQL Server

Most of the time when a SQL Server is under attack from an internal source, it is an employee who is testing to see whether they can gain access to some area that they are curious about. All employees are curious about data they do not have access to, such as salary information, sales figures, or even the vacation schedules of their co-workers. Sometimes people will try to access data just to see whether it is possible to learn something.

You’ll find, however, that a number of real attacks occur daily. Someone may have been offered a financial reward if they can steal data, they may be leaving the company and want to take data to a competitor, or they may be upset and want to destroy data. It is hard for a DBA to determine whether an attack is born out of maliciousness or curiosity.

The response, however, must be the same. You need to prevent the attack from succeeding while learning as much about the attack as possible. These two goals may be mutually exclusive, in which case the prevention is the most important, but if possible, you should gather as much information about the attacker as possible. This means that as long as your data is safe, you may choose to allow the attack to continue in order to learn more about the attacker.

Prevention for internal attacks is really the same as prevention from external attacks. Ensure that every account has the minimum amount of privileges it needs to accomplish its job, known as the principle of least privilege. This means that internal employees who do not need access to the SQL Server should not have access. Review your internal Windows groups used for security purposes to ensure this is the case. You should also make sure you do each of the following:

• Passwords used for administrative purposes should be changed on a regular schedule, at least every 30 days.

• All other user passwords should be changed according to your domain policy, but no more than 90 days should pass between changes.

• Accounts for employees or contractors who are no longer working at the company should be disabled immediately.

• Shared accounts, such as a general application password or passwords used in an application role, should be changed whenever a user with knowledge of them leaves the company or there is a suspicion of tampering.

• Review logs for unusual activity on your database servers.

Detecting an attack is a result of monitoring. Examine your security logs regularly, preferably with an automated program, to look for a large number of failed logins. Ask your network administrators to monitor the network traffic for any deviations from normal patterns.

The response to an internal attack is in many ways similar to any other attack. The difference is that by working with your network administrators, you should be able to isolate from which machine the attack is occurring. This should allow you to quickly determine whether this is a malicious attack.

The response from your company is an administrative decision and one that you will need to allow your management to make. As a DBA, your job should be to identify the attack and prevent its success, gather information about the attacker, and then let your managers decide how to proceed.

After any attack is over, we highly recommend that you immediately change all administrative passwords, both for administrator user accounts and for the sa accounts. It is possible that the attack succeeded before you detected it and these accounts are compromised. By changing these passwords, you ensure these accounts are safe.

One last protective measure to take once an attack is over is to examine your login accounts to be sure a new account has not been added. It is especially important to check that new accounts have not been added to the sysamdin or db_owner role. Exercise 9.1 walks you through checking for new accounts and other changes.

Exercise 9.1: Checking for Tampering

After an attack has been successfully prevented from controlling your server, it is prudent to check to see whether the server has been tampered with in any way. This exercise will walk you through some of the things you should check on your server.

The first step is to look for new login accounts. Since it is hard to keep a list current of the logins on all your servers, you will check for any recent additions.

1. Connect to your database server using SQL Server Management Studio and select New Query.

2. Type the following code in the code window:

    select create_date, name  From sys.server_principals  order by create_date desc

3. Click the Execute button, or press Alt+X to run this query. You should receive results like those shown here.

   

4. The list of accounts and dates may be different on your server, but you will see the date that a login was created and its name. You should examine this list closely to be sure that no accounts exist that should not be there.

5. Two other important areas to check are changes to your privileged roles such as sysadmin and db_owner. First you will check the sysadmin role.

6. Select the Security folder in SQL Server Management Studio’s Object Explorer for your server, and expand it.

7. Next expand the Server Roles folder.

8. Double-click the sysadmin role. You should get a dialog box similar to the one shown here.

   

9. You should have a document somewhere that lists all the accounts and groups that are allowed system administrator access. This should be a small list of users and groups. Be sure that no additional accounts have been added here.

10. You will now check a database for db_owner role changes. Your production servers will have their own databases that need checking. In this example, we will show how to check the AdventureWorks database, but the procedure is the same for any database.

11. Select the Databases folder in SQL Server 2005 Management Studio. Expand this folder, and then expand the folder for the AdventureWorks database, as shown here.

    

12. Select the Security folder and expand it, and then expand the Roles folder and the Database Roles folder below that.

13. Double-click the db_owner role to see its properties, as shown here.

    

14. Examine this role to be sure that no accounts have been added to the role. In this case, only the dbo user is a member. As with the system administrators, this should be a small list of users.