Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Scenario: You're handed a database backup and told to restore it to a server (that's already hosting other databases), but are given no useful information about what the backup contains or whether the source should be trusted.

Question 1: What are the potential implications of restoring a backup that could well be malicious?

Question 2: What can you do to protect your server/the data in other databases from the impact of restoring a potentially-malicious backup? RESTORE VERIFYONLY would seem to be a good first step. The ultimate answer is probably 'restore the database in a sandbox VM with no access to the outside world', but let's assume that option is off the table. What else should be done in this situation?

share|improve this question
1  
Even assuming that the restore is data-only (no stored procedures, or some such), there's lots of malice that can happen. Suppose the backup is for a web application that contains a user table, with their respective permission levels, a malicious backup could grant access to users that shouldn't have them, and who knows what they could do from that. –  Lie Ryan Jul 20 at 8:46

8 Answers 8

up vote 17 down vote accepted

A database may contain malicious code, possibly a procedure that is going to change a password for the "sa" login or drop every database. However the only way that I can see that causing an issue is for an individual to restore the database, and then manually execute any code within that database. It would not execute in any automated manner.

There is no setting that can be applied within a database to have SQL Server automatically execute some bit of code within the database upon restoring it to a server. If it did I would expect Microsoft would loose the Common Criteria certification for the product. That is to great of a bug to have allowed in a DBMS to me.

share|improve this answer
    
If Service Broker is re-enabled as part of the restore (using WITH ENABLE_BROKER et al), then code can run "automatically." Obviously the restorer wouldn't want to use any of those options if security is a concern, but it could potentially be buried inside a 3rd-party vendor app where the user might not see it. –  Jon Seigel Jul 16 at 14:36
    
What kind of code can be executed through Service Broker? I never use it or set it up. –  Shawn Melton Jul 16 at 14:52
    
Activation stored procedures. technet.microsoft.com/en-us/library/… –  Jon Seigel Jul 16 at 14:59
2  
Maybe also do a RESTORE HEADERONLY to see if the database has containment enabled. If so, and containment is enabled on the server, users would be able to access it without you giving them server access. This is for SQL 2012 or newer, of course. if containment is not enabled on the server, and the database in the backup has it enabled, the restore will fail, so mainly only a concern if it is enabled on the server. –  Robert L Davis Jul 16 at 19:31
1  
@JonSeigel I don't think those will fire automatically, though. SOMETHING has to put a message into a queue by sending it to a service, so there has to be some interaction within that database to insert a record or fire a procedure or something. Broker queues don't just keep firing their activation procedures without any interaction, they watch for messages to show in the queue. –  JNK Jul 16 at 19:57

There are some prevention steps that you could do.

  1. Make sure no one but one sysadmin has access to the restored database.
  2. Put the db in single user mode after the restore is completed.
  3. Check the code inside all stored procedures and functions and triggers inside this database.
  4. Perform a dbcc checkdb to make sure there are no integrity issues.
  5. Check the users which used to have access to the database and remove all of them.
  6. Start allowing access, very restricted to specific objects checked by you.

Like Shawn said, the code will not execute by itself unless some stored procedure which seems vbalid has an exec of another malicious code. This is the reason of checking the code inside each one of them before putting it multi user mode.

share|improve this answer

I'm reaching here, but I can think of at least one dangerous scenario: if you restore a database that has a filetable, those files are now on your network by default (and specifically, on your SQL Server). You could restore a virus.

That by itself won't do anything, of course - the virus doesn't suddenly become sentient - but if your users then try to access the file, they could be infected. (Hey, I said I was reaching.) I'm envisioning a scenario where an outside hacker wants to get malware in the door, and he then sends an email to Bob in accounting saying, "Here's the file: \sqlserver\filetableshare\myvirus.exe" - at that point it's gone past your firewalls without detection, and we're now down to your internal antivirus and anti-malware tools.

share|improve this answer
1  
You could also express this as 'the database contains how-to instructions for our personel that must be read and applied'. If they follow the malicious how-to they will launch the rockets on Moscow. It would be ordinary varchar ina table... Ditto if you restore binaries and invite employees to run it w/o validating the origin, you had it coming. –  Remus Rusanu Jul 16 at 22:30
    
@RemusRusanu launch the rockets on Moscow, hahaha, nice! –  Brent Ozar Jul 17 at 3:45
    
Love the social engineering perspective. A targeted email with a .bak file could likely be very enticing depending on the target. –  Max Vernon Jul 27 at 2:28

RESTORE VERIFYONLY would seem to be a good first step. The ultimate answer is probably 'restore the database in a sandbox VM with no access to the outside world', but let's assume that option is off the table. What else should be done in this situation?

Restore verifyonly verifies integrity of database it WILL NOT tell you whether backup includesa malicious code or not RESTORE VERIFYONLY does not attempt to verify the structure of the data contained in the backup volumes. Its highly unlikey that if backup comes from inside the firm where you work could be malicious but if it comes from some third party you need to be careful as Shawn pointed.

Microsoft Online documentation says that

•For security purposes, we recommend that you do not attach or restore databases from unknown or untrusted sources. Such databases could contain malicious code that might execute unintended Transact-SQL code or cause errors by modifying the schema or the physical database structure. Before you use a database from an unknown or untrusted source, run DBCC CHECKDB on the database on a nonproduction server and also examine the code, such as stored procedures or other user-defined code, in the database.

share|improve this answer

The question focuses mostly on a backup containing malware, but it's also possible to get unwanted and potentially malicious behaviour from the restore operation itself.

I've accidentally found in the past that it's possible to crash SQL Server by trying to restore a corrupt backup file that causes SQL Server to try to read past the end of the backup file and crash. I'm not sure which versions are susceptible or exactly what is required to reproduce the problem. I documented some limited details here when I encountered this problem a few years ago.

share|improve this answer
    
Good point. I didn't necessarily mean to focus on "valid backup that contains malware", crashing the SQL server by means of an invalid backup is also a perfectly relevant answer to "what could go wrong?" –  Simon Righarts Jul 17 at 6:15

What risk is there is restoring an unknown database from an unknown source? None.

What risk is there in letting an unknown application connect using a sysadmin account to connect to that database and start running code? LOTS! If the application account only has rights within the database and no server level access then there's nothing it can really do outside of the database. This basically comes down to having a proper security framework setup on the server to begin with.

share|improve this answer

You're handed a database backup and told to restore it to a server (that's already hosting other databases), but are given no useful information about what the backup contains or whether the source should be trusted.

Nice. You demand a signed written statement from whoever is telling you to do this that they accept full responsibility for the consequences. If they're unwilling to do that, you should test install in a sandbox after examining the backup file (if possible), and thoroughly examine all tables, procedures, etc. If anything smells funny at any point, don't put it on the production system. Even then, you should make it clear (to your boss and his superiors) that you never trusted the backup and are doing this only under direct orders.

If they won't sign such a statement, alert their superior(s) before doing anything. As a professional, it's your duty to protect your system as much as possible, no matter what some dim-witted superior may order you to do. You might get fired, but you can hold your head high and know you did the right thing.

share|improve this answer

There are not alot of dangers per say, other than some far-reaching ones suggested here. As was mentionned, it is hard to have auto-executing stuff in a database backup itself. It needs some kind of exterior triggerring mechanism.

Get an old laptop/desktop and an evaluation version of your Database software (SQLExpress) if licensing is an issue. Copy the backup file on the machine, unplug the network/wireless, and do the restoration. Then start digging. Take all the time you need, because there are many places stuff can hide, most of them already covered by other posts in this thread.

Your DBA integrity and the well-beiing of your Production environment is more important than any order given by a superior.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.