Recently in the news an article circulated about a coordinated attack by a team of hackers known as Anonymous. Information about this incident is all over the internet here is one article with details RT News.
Part of that attack included deleting a database belonging to the Israeli Foreign Ministry. The details are not clear, but I wanted to write about what may have happened and how you can prevent it or recover from it in your own environment.
**Disclaimer: This post is not about politics or whether the actions taken by Anonymous were legal/illegal or ethical/unethical. This post is to take a look at the actions that were done from a technical standpoint only. So if you have comments about the actions or anything about the politics surrounding the situation please pick a forum that discusses these issues regularly and post your thoughts there. Thanks**
As I mentioned in my introduction, I don’t know exactly what happened when Anonymous “deleted” the databases belonging to the Foreign Ministry. It is inconclusive if they deleted data, the actual databases, or brought down the server. So I’m going to post different scenarios as to what could happen and the ways to prevent such incidents or recover from them. These examples are geared toward a SQL Server environment.
Drop vs. Detach:
So in this scenario let us say that a group of hackers got access to your database and had elevated privileges (You can check Books Online for the exact permissions).
Running the sp_Detach_DB procedure would “logically drop” the database from your SQL Server instance making it inaccessible. However, the database and log files will still remain on the file system and must be removed manually.
Running a DROP DATABASE command will not only remove the database from your SQL Server Instance, it will also remove the any associated database and log files from the file system. You can test the outcome with the following simple code if you want to see for yourself.
--Make sure you know where your SQL Server Instance places data and log files so you can verify this test
Create Database TestHack
Drop Database TestHack
Create Database TestHack2
EXEC sp_detach_db 'TestHack2'
Delete vs. Truncate vs. Drop Table:
This is one of my most favorite questions to ask candidates that interview for a SQL Server DBA or Developer role. Delete is a DML command. Drop Table and Truncate Table are DDL commands.
Let’s talk about DELETE first. Assume that the hackers ran a DELETE Table statement on all the tables in your database. A DELETE statement logs all individual row deletes to the transaction log which makes it slower than a truncate and it uses more resources and even space (T-Log fills up with information on deleted rows). So a delete command could potential slow down a hackers ability to do the job quickly and if you have triggers on the table, then all the triggers will fire. So if your system is designed to fire triggers that make a copy of all records upon update or delete then you could have saved yourself a lot of headache. Most systems are not designed like that however.
Next up is the TRUNCATE TABLE command. This is a double sided sword. It is extremely fast, bypasses all triggers, and does not log individual deletes in the transaction log. This command is considered a “non-logged” operation because it does not log each delete as the DELETE command does. However, it does log all the page deallocations that it does; TRUNCATE basically deallocates all pages/extents of the table.
Unlike Truncate and Delete, the Drop Table command actually removes the table, its definition, keys, indexes, and any triggers you have on there. It basically wipes it from existence and all objects that are related to it; not views, procedures, and functions that reference it which will be unusable after the drop anyway. There is nothing logged about this very powerful DDL command and it is extremely quick.
All three commands however may have problems firing if you have enforced foreign key constraints. Now you know why it is important to create constraints in your database right? If you have your foreign keys defined with ON DELETE CASCADE, then you will lose all related data when a delete command is issued. Truncate and DROP TABLE commands cannot by pass the constraints, but then again a good hacker could have some custom code that finds and drops all constraints before they fire off a DROP or TRUNCATE command.
Finally, these commands can affect databases that are being log-shipped, mirrored, or have replication on them. One simple example. If you run a DELETE, TRUNCATE, or DROP TABLE command to remove data from your database, it will remove it on the mirror as well. Just something to keep in mind in case you think you are safe because you have a mirroring or log-shipping setup.
Importance of Recovery Models and Backups
So now that I have depressed you and made you feel hopeless, let’s take a break and review recovery models.
If your database is set to FULL RECOVERY and you do regular backups (Full and T-Logs at minimum), then you have a good chance of recovering your data to a point in time. If your database is set to SIMPLE RECOVERY then you can only restore back to your last full or diff backup. You cannot do any log or tail-log backups on your database in SIMPLE RECOVERY which means individual transactions will be lost. Let me put it this way, if your last full backup was at Midnight, and all your tables were deleted at 11:59pm, then you’ve lost an entire days worth of data (unless you have some differential backups that ran throughout the day).
What does this mean? This means that your safe haven are your backups. So if you want to lose your data and your job don’t do backups! Just ask your business how much data they can stand to lose and setup your T-Log backups for an interval that captures enough data so that if there is a loss you are covered. Also, with FULL RECOVERY you can use third party tools to quickly scan through your transaction log and recover important data reducing your downtime.
Backups will save you from any of the problems mentioned above. There is one exception however. If a hacker were to gain access to your server, and deleted all your databases, and deleted all your backups and you don’t have backups stored somewhere else. It is advisable to make sure your backups are stored off the server somewhere in case anything were to happen to that server. There are many tools that you can use to sync backups to a network location. These tools are on separate servers which means a hacker would have to compromise other servers to be able to wipe out all of your data.
I’ll quote Paul Randal to sum this all up “Rollback is *purely* driven by what’s in the transaction log.”
Other Prevention Techniques
1. Don’t use in-line SQL on applications which can cause SQL Injection. What does that mean? For example, don’t have a web page have a query for looking up a customer that looks like this:
‘SELECT * FROM table WHERE Column = ‘+@input
Someone could just pass the following string into your input and delete your table: ”’two”; drop table table’
2. Rename your SA account and use a strong password. If you are using your server name as the password for the SA account then you’re asking for it. Just be careful if you are doing an in place upgrade, for it may fail.
3. Make sure you backup your system databases in case the hacker manages to turn off SQL Services and delete your system databases. We often neglect doing backups of these databases but it can help reduce downtime when you’re in a jam, don’t think that it is just easier to re-install SQL server. The link above has a reference to an article that describes how to rebuild your master database.
4. Be careful with SQL Browser. It is an interesting service that you don’t hear much about. When turned on, it basically tells the network “Hey I’m a SQL Server name [ServerName].” Why does it help? If someone does not know the name of the server then they can browse for it. Now if someone malicious wants to search for SQL Servers on your network they do not need to know the server names. Another way it helps is that if you have a named instance on that server, and you do not have SQL Browser turned on, then you have to specify the port that the named instance of SQL Server runs on when connecting. This is something you will need to investigate and make sure is necessary in your environment.
5. From a network security perspective, you can get a company to do a professional PEN Test (Penetration Testing) to make sure your network is secure from hacking.
6. Copy your backups off the server! Yes I’m repeating myself because this is crucial. There are plenty of software vendors that create programs to automatically sync files over the network upon creation, update, etc. So you could point that application to your local backup folder and have the backups synced as soon as they are created.
7. Disable guest accounts on your databases. Even if they don’t have much in terms of permissions, you could unknowingly be granting unneeded access to your data.
8. Understand Database Cross Ownership Chaining. You could potentially allow for one user with strong permissions on one database to be malicious with another database on your server. I gave a presentation with PASS on security and described these scenarios in detail (link is available in the Presentations section)
9. Policy Based Management and proper database integrity constraints can give a headache to potential hackers. It adds an extra layer of security that can slow them down. Remember, they don’t want to hang around too much because of the fact that it might help them get caught. Policies can prevent and log certain configuration changes and other changes on your server or databases. It’s not a replacement for proper security, but it can be an enhancement. It can also be used across multiple servers.
10. Add a server wide DDL Trigger to prevent Dropping of Databases. It is as simple as it sounds, and adds another layer of security to get through. Here is more information on how to do that.
Please feel free to add your own recommendations in the comments section.