How to Protect SQL Server from Hackers and Penetration Tests
Updated: Sep 30, 2020
On June 3rd, the InfosecMatter blog published a post titled "Top #10 Vulnerabilities: Internal Infrastructure Pentest". This blog post detailed the top most common vulnerabilities in Windows servers and networks found during more than 60 internal infrastructure penetration tests around the world. After reading this article, I was prompted to actively look for similar resources on SQL Server penetration testing, and I got some interesting findings. See below my conclusions.
After reading up on a bunch of SQL Server penetration testing articles, I found that the steps of a common penetration test are as follows:
Attacking (Loot / Destruction)
Logically, these steps mimic the steps taken by a common would-be hacker (except, of course, they try not to actually damage anything).
I'll briefly describe each step from the point of view of a hacker or penetration tester, the common methodologies of each step, and offer recommendations that we can follow to protect our database systems at every level.
The first step a hacker or a "pentester" would do when introduced into a network, is to start looking for SQL Servers. This is done using IP and Port scanner tools that scan the network for common ports that may represent a vulnerable system.
Scan the network for open TCP/IP 1433 port (the SQL Server default)
Scan the network for open UDP 1434 port (the SQL Server Browser service)
Using SQL Injection (to discover the server address via an existing application)
How to Protect Ourselves
Shut down the SQL Server Browser service and disable it (use static ports instead)
Alternatively, if possible, disable the TCP/IP protocol entirely and rely on Named Pipes or Shared Memory instead.
2. Gaining Access
Now that we have our "targets", our next step is trying to break in. Basically this means guessing a working username and password combination or using an elevated Operating System account to create a new login.
Try SA or some other username and a weak or default password (via brute force/password lists)
Discovering credential leaks (username and password combinations or certificates with private keys). These are commonly saved in configuration files somewhere in the file system (XML, web.config, appSettings.json files etc. that contain things like connection strings for applications)
Using a highly privileged Windows account to create a basic Scheduled Task that logs into the SQL Server using NT SERVICE\SYSTEM or some such, and create a new sysadmin login that can be used.
SQL Injection is also a common method for trying to gain access via the permissions of an existing software application.
How to Protect Ourselves
Disable SQL Authentication. Use Windows Authentication only.
Do not save your connection strings in clear text anywhere.
Offloading your passwords using a service such as Azure Key Vault or something similar.
Using Windows Authentication will also solve this issue by not requiring you to save your passwords anywhere.
Use something like Microsoft CredScan to detect possible credential leaks that you may have missed.
Remove SQL Server access from the NT SERVICE\SYSTEM Windows login if it exists.
Remove SQL Server access from the BUILTIN\Administrators Windows Group if it exists.
Remove SQL Server access from any Windows Account that also has Windows Administrator permissions on the same server.
Follow the "Principle of Least Privilege".
3. Elevating Permissions
If we gained access to the SQL Server using a non-sysadmin account, we would want to elevate our account to sysadmin. If we already have access to a sysadmin account, we would want to elevate access to the entire operating system, and/or gain access to additional logins that can increase our attack surface area.
Look for databases with the TRUSTWORTHY setting turned on, whose owner has sysadmin permissions, and use it to elevate permissions of the current user to sysadmin.
Also if the current login has the securityadmin server role, it's possible to use it to create logins with very high privileges.
Retrieving the list of other logins together with their hash encrypted passwords, for brute-force cracking off-site.
Running OS commands using the context of the SQL Server logon service account, via an enabled xp_cmdshell
Running OS commands using the context of the logon service account, via enabled OLE Automation procedures (OA_*)
SQL Injection may also be possible not only by attacking vulnerable front-end applications, but also internally within the database, using vulnerable stored procedures that use dynamic SQL. Such use cases can be opportunities for permission elevation.
How to Protect Ourselves
Do not enable the TRUSTWORTHY setting in any database.
With the sole exception of MSDB which requires this setting.
Circumvent this limitation by denying from any non-sysadmin users the permissions to create or alter objects in the MSDB database.
If you need to use CLR assemblies with UNSAFE or EXTERNAL_ACCESS permission sets, please do it the proper way - using certificates and signing your assemblies.
Do not give any login the securityadmin server role.
Do not grant SELECT permissions on system tables to any login that doesn't actually need them.
If possible, keep CLR disabled.
Protect against SQL Injection, inside stored procedures with dynamic SQL as well!
4. Attacking (Loot / Destruction)
This basically comes down to utilizing our achieved permissions to retrieve as much data as we can or to cause as much damage as we can. The higher-privileged user we got, the more widespread our impact can be.
Updating system tables.
Stealing, deleting, or modifying data or even entire databases.
Discovering, stealing, and/or destroying backup files.
Creating additional hidden back-doors for repeated access and attacks.
Locking down the database using encryption.
Initiating a Denial-of-Service attack (DoS or DDoS) by "choking" the SQL Server instance with heavy operations that freeze the entire server.
Causing widespread damage on the OS, domain, and network (including worms, viruses, and Trojans).
How to Protect Ourselves
Follow the "Principle of Least Privilege". i.e. do not give permissions to any user unless they truly require it. Forget about giving sysadmin to everyone by default! And in general, try to avoid giving server roles unless you must. They all can be pretty dangerous.
Maintain a healthy backup plan with proper RTO and RPO, and perform periodical restore tests to verify your RTO and the overall health of the backup files.
Make sure to install your SQL Servers on virtual machines, that can have VM-wide snapshots taken periodically and restored quickly when needed.
Avoid saving your encryption keys together with your database (for example, decryption certificates installed on the SQL Server machine, or clear-text passwords being used inside stored procedures to open Symmetric or Asymmetric Keys).
Column Level Encryption (Always Encrypted) is a strongly recommended solution, where possible.
Run the SQL Server service using an account with minimum Windows permissions. Follow the "Principle of Least Privilege" in this case as well.
Auditing can also be an important tool to protect from and investigate possible attacks. There are several options available in SQL Server: