Joey on SQL Server
Ransomware: What It Means for Your Database Servers
Ransomware affects databases in very specific ways. Joey describes the mechanics of a SQL Server ransomware attack, what DBAs can do to protect their systems, and what security measures they should be advocating for.
- By Joey D'Antoni
Happy new year and welcome to 2020. Are you prepared for a ransomware attack?
Security is definitely not the most exciting topic. If you look at the program from any database conference, you will notice a heavy lean toward topics like performance tuning (especially performance tuning), new features or high availability. While these topics are interesting, having good database security might be your last line of defense in the ransomware attack that you are likely to have.
The Mechanics of Ransomware Attacks
Ransomware attacks typically use one of two major vectors. The more common one is a phishing attack in the form of an e-mail with an infected attachment. A user opens this attachment, allowing the ransomware to execute on your network.
The other vector is identifying a vulnerability in your network, like in your VPN software, and inserting itself into your network. Once inside your network, the ransomware begins to encrypt files on the target system and attempts to encrypt files on any mapped drives or even any network-connected device that the infected machine can write to.
After your files are fully encrypted, the infected computers will display a pop-up message asking for payment in the form of Bitcoin for a key to decrypt the files:
The time limit is a common ploy to pressure the victim into paying the ransom so they can gain access to their files without them being deleted. However, several recent cases have had firms paying the ransom but still not gaining access to their files.
How This Can Affect Your Database Systems
Ransomware attacks are highly effective, but they aren't the most sophisticated attacks. The attack functions just like Transparent Data Encryption (TDE) does in SQL Server, except that you don't have the have the encryption key.
Typically, variants will attack your MDF, NDF, LDF and your backup files (BAK and TRN). This leaves your SQL Server in an inoperable state, because the SQL Server service can no longer open master.mdf -- and then things go downhill from there.
While the encryption of your data and log files is really bad, the killer is that you can lose access to your backups. Once your backups are encrypted, it really is game over in terms of your data recovery.
So What Can You Do?
Protecting against ransomware is a multifaceted effort that requires defense in-depth at multiple layers of your infrastructure. However, as a data professional, your scope is probably defined to the database servers in your environment. Let's first talk about the things you can either personally do or advocate for.
Storing Backups in Another Location: I saw a Tweet a couple of week ago that said, "It's really hard to encrypt a locked box of backup tapes," and I thought that we have returned full circle to tape backup.
If trying to remember the latest LTO type isn't on your agenda, you should ensure that you have a second copy of your backups going to somewhere that is not easily network-accessible. I really like the idea of using the cloud for this (i.e., copying your backups to secured cloud storage).
Whatever you do, don't have the only copy of your database backups be on a file share that you can access from your desktop and as a standard user. Additionally, I would recommend carefully evaluating what has access to your backup environment. If it is on the company file share that employees use, consider moving the backups to their own set of file servers with limited access.
Use Admin Account: Instead of using your normal credentials for privileged operations like logging into a database server, you should be logging in with a different set of credentials (preferably with a multifactor authentication).
Some companies take this further by having a separate domain for production systems, which is an even more secure approach. The reason to do this is because if your machine becomes infected, your user account won't have direct access to production systems.
Patch All the Things You Manage: This should go without saying, but it's absolutely critical to keep systems up to date with the latest Windows and SQL Server patches. Encourage other teams in your organization to do the same; you are only as strong as your weakest link.
Use Windows Firewall: I'm guilty of not doing this myself, as it can be frustrating to open specific ports especially when you are using things like clustering or AlwaysOn Availability Groups. However, the protection that Windows Firewall offers is really good for a basic protection level. (Note: Some malware variants actually open ports in Windows Firewall.)
Test Those Backups: Your backups are only as good as your ability to restore them. Do this regularly.
What Should You Advocate For?
Though network architecture is typically outside of the scope of the DBA, it is some of the best protection against malware. By segmenting your network so that servers have very limited ability to communicate over the network with other services, you limit the damage that a malware infection can do.
From a security perspective, this is part of what's called an "assume breach" mentality -- you assume that computers in your network have been attacked or could be attacked at any time. Part of that mindset is protecting your most critical systems (e.g., your production database servers). This approach also means actively trying to attack your own environment and regularly recovery testing.
This is the new normal -- ransomware attacks are a clear risk for the foreseeable future and your critical business data is vulnerable. As a database or system administrator, you are limited in your influence, but you can help identify security anti-patterns while maintaining best practices across your systems to provide data protection.
Joseph D'Antoni is an Architect and SQL Server MVP with over a decade of experience working in both Fortune 500 and smaller firms. He is currently Principal Consultant for Denny Cherry and Associates Consulting. He holds a BS in Computer Information Systems from Louisiana Tech University and an MBA from North Carolina State University. Joey is the co-president of the Philadelphia SQL Server Users Group . He is a frequent speaker at PASS Summit, TechEd, Code Camps, and SQLSaturday events.