Protect SQL Server from ransomware

What is ransomware

Ransomware is malicious software that blocks access to user files until a ransom is paid. A quite common “locking mechanism” is by encrypting all files on a user computer, server or even the entire network. Encryption is the process of scrambling original data using an encryption key. The same key is then required to decrypt scrambled data to its original format. Without the original key, the data becomes unusable. All files on our computer, server or even the entire network become a pile of garbage. The only way to decrypt that garbage is to obtain the original encryption key by paying a ransom.

Paying the ransom is never a good idea. It encourages attackers to carry on the attacks. We also never know if we will get the keys or if the decryption works.

How does ransomware spread

Similarly to any other computer virus, ransomware will spread through phishing emails, infected websites and physical media such as USB drivers.

If your computer gets infected, it may spread to all network resources to which your account has access. I will ignore any SMB (Windows File Share) vulnerabilities that allow unauthorised access, as this is a different subject.

How to protect against ransomware

I was lucky enough to have spent much of my career in enterprise-grade Data Centers with thousands of servers where security was a top priority. Hopefully, you will find my tips helpful.

Segregate your access

• Do not use the same Active Directory account for your daily desktop activities like internet browsing and email and server access. Use the dedicated account for any server access, and don’t make your SQL Server sysadmin account a local admin account on Windows.
• Use a limited user account and a dedicated privileged (admin) account. Do not use your admin account for browsing the internet etc. This prevents downloaded malware from running under your account with elevated privileges and potentially gaining access to the entire network where admin access is unrestricted. Although UAC has significantly mitigated this risk, there is still some risk, and it better be safe than sorry.
• Never give privileged access to the end-users, even for a short time.
• Consider using a separate AD forest for the production environment to eliminate cross-contamination with desktop accounts.
• If you don’t have Active Directory, don’t create the same “local admin” account with the same password on every single server. I will write more about the benefits of Kerberos authentication in future posts.

Segregate your networks

• Leverage physical or logical (VLAN) network isolation and put your servers in a separate network from your workstations and other devices with internet access. Restrict your server’s ability to connect to the internet.
• Have “jump serves” bridging your disparate networks – these could be your existing “DBA Management” servers that you will likely already have.
• Don’t put your database servers in DMZ. For those serving public applications, a two or more firewalls setup may be appropriate:
  Public |> DMZ ||> Databases <|| Workstations.
  This way, you are mitigating the exposure of underlying OS to a public network and only opening SQL ports on the first firewall from DMZ.
• Don’t extend your “office” Active Directory forest into DMZ; create a new separate and limited forest. Active Directory authentication has security benefits (Kerberos) and improved account management. If you don’t have AD, you will have to manage potentially lots of accounts across many servers.

Patch your servers

I often see SQL Servers that have never been patched or updated since the installation – both the SQL Server and the Windows Server. Microsoft spends a lot of money to ensure the best possible security. Patches are released often for a good reason; use them.

Do backups

• It seems obvious, but I see a lot of servers where backups either fail or are never conducted because of “oh, I didn’t know this database was not included in the daily backup’. Do daily or weekly exception reports showing any production database without a backup.
• Make sure you backup any users, agent jobs and system objects
• Back up your encryption keys for any TDE-enabled databases!
• Write down down configuration. Even better – have an inventory.

Protect your backups

Follow the 3-2-1 backup approach:

1. One local backup (for quick recoveries).
2. A second copy of the local backup in a local network or separate device. You could automate storage snapshots on the backup drives if you have SAN.
3. A third copy of the backup in an offsite location – could be online storage.

It is fair to say that any malware cannot access online storage without access keys; therefore, this would be a reasonably secure option. If you are paranoid, you can automatically send backups to the archive storage using PowerShell.

You can quickly implement the 3-2-1 routine in SQL Server for free. The fantastic and free Ola Hallengren Backup and Maintenance Solution or dbatools will give you everything. SQL Server can also write backups to Azure natively since version 2012

Test your backups

Corrupted backups are no good. You have to make sure that the 3 copies of backups you have are valid and that you can restore from them. Set up weekly or monthly automated restore testing across your entire environment. This is also possible with dbatools.

How to recover from a disaster

Having valid backups is the first step to success. The second step has servers to restore these backups to. In cases where entire servers were affected and had to be rebuilt, provisioned or restored, this could take days or weeks. A secondary data centre is often used for Disaster Recovery to speed up the recovery process. It would serve the load until the primary data centre is back in operation.

In any case, the disaster recovery approach will depend on many different factors and your infrastructure. However, no matter what these are, you should be doing Disaster Recovery Testing at least a few times a year to make sure that:

• You know how to rebuild a server and ideally have it automated.
• You know how to restore databases, especially TDE-enabled databases and certificates.
• The application team knows how to restore all applications that connect to databases.
• If there is a secondary Data Centre, everyone has to make sure services can be failed over and that everyone knows how to do it.
• Everyone knows what to do in a critical situation.

Write down job cards and step-by-step documents as you will be under pressure and stress recovering from a significant outage, the memory will fail, and you will make mistakes.

Conclusion

Hopefully, in this very short post, I was able to give you some ideas about how to protect your data and backups from ransomware. Security is critical. If you are unsure how to make it right, don’t be afraid to say “I don’t know” and ask someone who knows. Let me know if I have missed anything or if you would like to add more ideas in a comment.