- Audit on SQL Server (Server Audit)
- How to Create an Audit to Monitor Job Creation, Modification, and Deletion in SQL Server
- How to create an Audit trigger to log object manipulation in SQL Server
- SQL Server - How to Implement Audit and Login Control (Login Trigger)
- Monitoring DDL and DCL Operations Using SQL Server fn_trace_gettable
- Using SQL Server Standard Trace to Audit Events (fn_trace_gettable)
- SQL Server - Database and Instance Level Permission and Audit Audit Trigger (GRANT and REVOKE)
- SQL Server - How to Monitor and Audit Data Changes in Tables Using Change Data Capture (CDC)
- SQL Server 2016 - How to “Time Travel” Using the Temporal Tables Feature
- SQL Server - How to use auditing to map actual required permissions on a user
- SQL Server - Trigger to prevent and prevent table changes
- SQL Server - How to Create a Data Change History for Your Tables (Audit Logs)
- SQL Server - How to Avoid Brute Force Attacks on Your Database
- SQL Server Security Checklist - An SP with more than 70 security items to validate your database.
- SQL Server - How to Avoid and Protect Against Ransomware Attacks Like WannaCry on Your Database Server
In this blog article number 350, I would like to share with you my experience during several tests I did about Ransomware on SQL Server database servers, such as WannaCry, which I downloaded and “infected” my VM just to perform these testing, understanding how it works, and how we can protect ourselves against this kind of attack, which, strangely enough, is still commonplace in the day-to-day consulting DBA.
For the creation of this article, I had valuable tips from MVP André Ruschel that helped me to better understand how this Ransomware works in general, remembering that WannaCry itself has several variations, so there is the possibility of another variant of it acting in slightly different ways than I will explain here.
What is Ransomware?Click to view content
The most well-known Ransomware to date is WannaCry, which was considered the largest attack of its kind to date, starting on 12 / 05 / 2017, attacking around 150 countries and infecting more than 230 thousand systems, although there are several others running over the network.
Ransomware behavior is often very similar:
- Brute force attack attempts on RDP, SSH, and other connections to gain control of the host machine. It can also be infected in traditional ways: Attachments in emails and internet links
- Once executed, the program will begin encrypting files of certain extensions on disks, removable drives, and accessible network drives (SMB). During my tests on a virtual machine (VM), I left a physical machine disk shared with the VM and several files on that disk were encrypted.
- Ransomware will silently encrypt background files
- After the process has been completed, it will change the desktop wallpaper, making it clear that the machine has been compromised and usually requires a screen to inform you that the machine has been attacked and instructions for making the payment.
How does WannaCry work on my computer?Click to view content
Speaking specifically about WannaCry, we can make some interesting observations about it:
- This Ransomware exploits a Windows vulnerability that was fixed 2 months before (March 2017 - MS-17-010) the mass attack performed, ie if everyone kept the operating system updated, this attack would not have occurred.
- It determines which files to encrypt according to the extension of those files. While it is completely technically possible to parse the files according to your Mimetype, scanning the files would take much longer and consume a lot of machine resources, making it easier to identify an attack and making it easier for the user to stop cracking.
- Despite showing the payment window all the time, the operating system is fully functional, since if it was not, it would not be possible to make the payment
- If this virtual pest is removed, either manually or by protection tools (such as anti-virus), the token generated by WannaCry at startup will be changed, allowing no further payment to be made.
- Analyzing running processes, what consumes the most CPU during encryption is diskpart.exe
- If the file is locked for reading, Ransomware cannot encrypt it. Ex: SQL Server bank file, where you can not even copy the MDF file with the online bank. But if you have a photo open in Paint, for example, because it doesn't lock the file, WannaCry can encrypt the photo normally.
- Wannacry elevates privileges and runs the virus with every RDP session it finds on the operating system.
- During my tests, even with the SQL Server service stopped, WannaCry was unable to encrypt the MDF files, since to access the SQL Server DATA folder (default directory), Windows asks me for confirmation (even though my user is an Administrator). ). It was only able to encrypt the files when I moved them to another directory without NTFS security policies (C: \ Data). I later found out that this is because it ignores the files that are in the "C: \ Program Files \" directory.
- WannaCry corrupts Volume Shadow Copy Service (VSS) to make it difficult to recover encrypted files.
- The mass attack was only stopped when a security expert analyzed the ransomware code and found that if it registered the domain www.iuqerfsodp9ifjaposdfjhgosurijfaewrwergwea.com, the virus aborted new infections on the machines it ran on.
Some time after the WannaCry Boom, it became available. a possible solution which promises to decrypt WannaCry encrypted files on some operating systems, since the machine may not have been shut down / restarted since infection and the memory segment of specific information cannot have been reallocated to another process on the machine.
Who remembers that time, was really a chaos in companies. In Whatsapp groups circulated various images and prints of large companies being attacked by this virtual plague and causing devastating attacks. Many even paid the ransom so as not to lose important data.
The extensions WannaCry sought to encrypt were:
.doc, .docx, .xls, .xlsx, .ppt, .pptx, .pst, .ost, .msg, .eml, .vsd, .vsdx, .txt, .csv, .rtf, .xnumx, .wks .wk123, .pdf, .dwg, .onetoc1, .snt, .jpeg, .jpg, .docb, .docm, .dot, .dotm, .dotx, .xlsm, .xlsb, .xlw, .xlt,. xlm, .xlc, .xltx, .xltm, .pptm, .pot, .pps, .ppsm, .ppsx, .ppam, .potx, .potb, .edb, .hwp, .2, .sxi, .sti, .sldx, .sldm, .sldm, .vdi, .vmdk, .vmx, .gpg, .aes, .ARC, .PAQ, .bz602, .tbk, .Capricorn, .tar, .tgz, .gz, .7z, .rar, .zip, .backup, .iso, .vcd, .bmp, .png, .gif, .raw, .cgm, .tif, .tiff,. nef, .psd, .ai, .svg, .djvu, .m4u, .m3u, .mid, .wma, .flv, .3g2, .mkv, .3gp, .mp4, .mov, .avi, .asf, .mpeg, .vob, .mpg, .wmv, .fla, .swf, .wav, .mp3, .sh, .class, .jar, .java, .rb, .asp, .php, .jsp, .brd .sch, .dch, .dip, .pl, .vb, .vbs, .ps1, .bat, .cmd, .js, .asm, .h, .pas, .cpp, .c, .cs,. suo .sln, .ldf, .mdf.ibd, .myi, .myd, .frm, .odb, .dbf, .db, .mdb, .accdb, .sql, .sqlitedb, .sqlite3, .asc, .lay6, .lay, .mml, .sxm, .otg, .odg, .uop, .std, .sxd, .otp, .odp, .wb2, .slk,. dif, .stc, .sxc, .ots, .ods, .3dm, .max, .3ds, .uot, .stw, .sxw, .ott, .odt, .pem, .p12, .csr, .crt, .key, .pfx, .der
And it had a mechanism for NOT encrypting the following directories (thus keeping the operating system working):
- “Temporary Internet Files”
- This folder protects against ransomware. Modifying it will reduce protection ”
- “\ Local Settings \ Temp”
- “\ AppData \ Local \ Temp”
- “\ Program Files (x86)”
- “\ Program Files”
- “\ ProgramData”
- “\ Intel”
Examples of directories attacked by WannaCry:
If you want to specialize in how this Ransomware works, technically understand how it operates, what calls it makes in the OS, see the files I have put as a reference, because they are very rich in these more technical details that are not the focus of this article. .
How can DBA protect against Ransomware attacks?Click to view content
- Prevent servers from being exposed and published to the Internet. In cases where the application server must be available for access from any IP, ensure that at least the database is on another server, isolated, inaccessible over the Internet, only accessed through the internal network or VPN.
- Manages database server firewall rules well. Ensure that only application servers and specific machines have access to those servers.
- Keep Windows and SQL Server up to date with the latest available updates, especially when the update states that it is correcting a security hole. The WannaCry case itself could have been avoided if the operating system of these machines were up to date.
- Windows server must use Windows Server. Never consider using non-Server versions of Windows, such as Windows Starter, Professional, Home Premium, etc.
- Always keep your operating system and SQL Server as current as possible. SQL Server 2008, for example, will lose support now in June / 2019 and will no longer receive Security or Updates. The same thing happens with Windows Server.
- Protect your database from brute force attacks. As I mentioned earlier, WannaCry can't encrypt files in use by the database, but if an attacker can access your database, it can stop the SQL service or take the databases offline and then start the attack, thus encrypting it. , your data
- As I said above, Ransomware usually targets specific extensions rather than parsing all files through MIMETYPE. It does this to optimize file search and encryption time. Because of this, It is good practice to use nontraditional extensions to data, log, and backup files. Avoid MDF, LDF and BAK. Use your imagination.
- Speaking of backup, where do you save your company backups? Not on the network or on the same server, right? A VERY important step in ensuring that you can securely recover your original data, even if this attack arrives at your company and encrypts your data, BACKUP in the cloud is critical to this.
It does not necessarily have to be for the cloud, although it is a very practical, safe and very cheap option. Your backup can be tape backup, blu-ray disc, etc. But the backup needs to be physically stored off the same source server. This is very important to ensure that in a scenario of total network intrusion, your backups are not compromised either.
Remember: If you lose your business data and backup, your business is over.
- Ensure that there is a regular backup testing policy in your company. If you don't know how to do this, read the articles. Automating Restore Test - Implementation, SQL Server: Automating Backup Restore and in the article Automating Database Restore.
There is nothing worse than DBA having the security that it can recover any data corruption issues in your environment, and when it tries to restore a backup prior to the problem, it finds that the backups are corrupt.
Backup without restore test is no guarantee of anything!
- Disable login and rename users that are standard in all SQL Server instances, such as sa (which is sysadmin on top), sysdba, and others. These are the users most used by brute force attacks, because the attacker already knows the username, only the password is missing.
- Ensure that no user with local server administrator permission has read or database access. And also ensure that the local user who has access to the bank has the minimum permissions available on the server.
If you have two users on the server because of this (1 to administer the server and one to the bank), use different passwords !! This will greatly hinder the success of attacks using your user in attacks.
- Always maintain a strict policy to control and audit local admin users. Keep these users as restricted as possible.
- Just as I did in the article SQL Server - How to Avoid Brute Force Attacks on Your Databasewhere I monitor the SQL Server log for connection failures, implement this same control in the Windows security and application log for connection attempts using RDP, SSH, and other protocols.
- Block or disable the SMB protocol where possible - Ports 137 and 138 UDP and TCP 139 and 445.
- Technically, it's all about the biggest ransomware attack.
- WannaCry Malware Sample Analysis
- Protecting Your Database from Ransomware like WannaCry
- WannaCry / Wcry / WannaCrypt ransomware: help / advice
Is that you? Have you had any Ransomware attack in your company? Share with me your experience in the comments and give feedback if you liked the article. I accept questions, suggestions and criticism too 🙂
I really hope you enjoyed it, a big hug and see you in the next article.