I wrote a post a couple weeks ago about not changing port 1433 for security reasons. I received this comment, which is not visible on that page because it warrants a lengthy response. I have redacted the name of the commenter.
I disagree.
Hundreds companies around the world were victims of ransomware attack even they had implemented best security practics ( all described in the article )
Its time to say the thruth that SQL server is not secured at all and is full of security bugs which can be easily exploited.Its why attackers could access DB files ( even it was locked by SQL service ) and encrypt entire environment.
Restoring in such situation is not solution as due to bottleneck it could take weeks to restore everything.
And yes i was a victim of this attack and i know exactly what is it and how to deal.The only work around i found ( not mentioned in the article ) is hiding DB behind mountpoints. By this attackers scripts cannot access related MFT and will not encrypt DB files.
The worst thing is that all security gaps which were exploited are still present in SQL and Microsoft is aware of it.
Firstly, I am sorry to hear that your company was a victim of a ransomware attack. It sucks. I have worked with companies in this situation, and in some cases I was unable to help and they were forced to go out of business. That’s a horrible feeling.
Secondly, I want to be very clear that you shouldn’t be taking free advice about your network security from strangers on the Internet. I suggested three security best practices, but it certainly wasn’t a complete list (nor did I suggest it was). The post was about not changing port 1433. If I wanted to write a comprehensive list of instructions of how to secure your SQL Server environment, that would take more than 500 words and you’d be paying me for it. So, if you’ve come here in bad faith to lash out, why are you wasting your time writing a comment that only I will ever read?
I’m willing to give you the benefit of the doubt however, because you seem nice, so I’ve put together some thoughts for you and other readers who want to react appropriately to a ransomware attack.
Rule 1: Test your backups
Securing your network from all attacks is impossible. Zero-days, malware, bugs in software (yes, including Microsoft’s products), and malicious employees & contractors are just some ways that data loss can occur.
The main responsibility of a database administrator (you didn’t specify in your comment whether this is your role, so I am making an assumption) is to be able to restore one or more databases within a time specified, which was previously agreed upon by all parties concerned, in a service-level agreement (SLA).
The two key requirements are Recovery Time Objective (RTO), which represents how quickly you can get everything back, and Recovery Point Objective (RPO), or how much data you’re prepared to lose. Everything else — at least for a DBA — is secondary.
You won’t know if a database is good until you restore it.
Rule 2: Test your backup monitoring solution
If you have an automated process to run backups, do you also make sure that the system is working? Do you test that the SQL Server Agent is still up and running? Do you check that your fancy restore process is restoring? Are you reading error logs?
Rule 3: Defence in depth
Security is non-optional, but as I said in Rule 1, you can’t expect to be perfectly secure. The only way to secure data effectively is not to have that data at all. This means your network needs to have layers of security. Now I already wrote about this in two separate books by Microsoft Press, but I’m going to expand on the points a little to help you appreciate why three suggestions on a blog post by a stranger on the Internet was never a complete set of security best practices.
I remind you that this is not exhaustive. I’m not a network security specialist. It goes a little something like this:
- Secure your network perimeter. Make sure your modems, routers, switches, hubs, Wi-Fi devices — and anything else that is a network device in 2022 — are all secure. Patch firmware, check firewall rules, check routing tables. Lock down ports that aren’t necessary. Close RDP access from outside your network. Require all employees and contractors to use a VPN to access your network from outside and give them access only to what they need. Run a separate physical network for your external devices and make sure the only way into your network is through one of those devices. If your SQL Server is exposed to the Internet, air-gap it if you can. If you need to keep it synchronized with production data, do a sync. If any of your internal services are exposed to the Internet, you need to be aware of the risks and act accordingly.
- Secure your physical perimeter. Lock the door to the network closet. Put a camera on the door. Check the camera footage. Rotate the tapes. Make sure access card logs are checked and cross-checked. Flag physically impossible log entries. Crossmatch entry logs with the HR database (should the CEO’s card be used to access a network closet at 3:15am when they’re listed as out of the office? Why does the CEO have access to the network closet at all?). Don’t have exposed Ethernet ports in public areas of your office building. If I can bring a laptop and plug directly into your internal network, you’ve got a bigger problem than ransomware.
- Follow the principle of least privilege. Back to our example of a CEO in a network closet, this means either the company is very small, in which case fine, but understand the risks. If it’s a company with an IT department, then the CEO doesn’t need privileged access to the network infrastructure. They can file a change request like everyone else. On the same theme, database administrators shouldn’t have full domain administrator access to the network, and domain administrators shouldn’t have system administrator access on database servers. Again, audit all access, and make sure you review those logs. For software, don’t give superuser access to an application. Lock down the database. Remove access that shouldn’t be there. Rename the
sa(system administrator) account on SQL Server, make sure you only access the database through Active Directory, and use network groups, not individual access. - Trust, but verify. Your network administrators are there to do a specific job, and they have privileged access to your infrastructure, so remove temptation, and log everything. We’re humans, so we get tempted. If you limit access to the people who need access, then the people who need access will make themselves known. At my new job the network is locked down really well. I can’t even get access to run a query against publicly accessible data without asking first. That request is approved and then logged. The same goes for your customers. Your application (whether it be a smart phone app, a web page, or a desktop application) should validate all user input. Yes, all of them. Then, the database should also sanitize inputs. In most cases this is invisible to the end user, and it makes your environment slightly more secure.
The bottom line
I’ve gone on for a thousand words already, so I’ll wrap up with this list of questions on the topic of ransomware as a data loss strategy:
- What is your disaster recovery plan when your organization experiences catastrophic data loss?
- How often do you test your disaster recovery plan?
- How often do you let a non-technical person test your disaster recovery plan?
- Does your disaster recovery plan include offsite backups?
- How often are your offsite backups tested?
- Can you get your offsite data back on-prem and restored successfully, within the confines of your SLA?
- Why not? What are your options?
Summary
I’m a stranger on the Internet who writes 500-800 words a week, which you read for free. Before I took my most recent full-time job, you were able to pay me money to consult to you. If you’re just taking security advice from free blogs, you get what you pay for. If you experience data loss because of a ransomware attack, it’s not Microsoft’s fault if the attacker used an exploit in Windows or whatever. That’s the ransomware attacker’s fault, and they’re the reason your data is now encrypted. That sucks, and it’s exactly when you should reach for the runbook which explains what to do in these circumstances.
Humans are bad at thinking when we panic, and we get emotional and lash out when we feel embarrassed. Sooner or later, you will be hacked. Sooner or later, I will also be hacked. If there’s money to be made, malware will continue to exist. Exploits will be exploited. The responsibility is on us, the database administrators, network administrators, and software developers, to make sure that when it happens, the impact is reduced by implementing patterns that slow down an attacker. That responsibility must be shared by executives who empower us to build, manage, and protect our environments, and test them so that there is a path to recovery.
Share your disaster recovery tips in the comments below.
Image courtesy of Markus Spiske.