11 Steps to Secure SQL

Posted by UpGuard

Securing SQL

Whether you’re running Microsoft’s SQL Server (soon to run on Linux) or the open source MySQL, you need to lockdown your databases to keep your data private and secure. These 11 steps will guide you through some of the basic principles of database security and how to implement them. Combined with a hardened web server configuration, a secure database server will keep an application from becoming an entry point into your network and keep your data from ending up dumped on the internet. When provisioning a new SQL server, remember to factor security in from the get-go; it should be a part of your regular process, not something applied retroactively, as some key security measures require fundamental configuration changes for insecurely installed database servers and applications.

See Target's Security Grade

1. Isolate the Database Server
Production database servers should be isolated as much as possible from other applications and services. Dedicated DB servers have a smaller footprint and hence attack surface, and there’s no need to worry about contending resources or conflicting traffic. Operating systems should be lean, with only the necessary services installed and running. Don’t install other applications unless they are required by the database server.

Depending on the size of your environment, you should consider putting your SQL server in a restricted network segment/VLAN so that only authorized traffic can pass to it. Typically, only an application server or web server will be talking directly to the database, so fairly restrictive network policies can be enacted to prevent illicit connections. This should go without saying, but you should never open your database server up to the internet on ports 1433/1434 (MSSQL) and 3306/3307 (MySQL).

2. Tailor the DB Installation
Example of MSSQL Features (source: Microsoft.com)Both MSSQL and MySQL offer tons of additional features, most of which you probably won’t need for any particular instance. By removing the pieces you don’t need, you reduce the possible inroads for exploitation. If you want to keep a feature around to play with that you aren’t using yet, do it in a test or development environment— best to keep production locked down as much as possible, especially before determining what effects a new module may have on your environment.

3. Keep it Updated
Both MSSQL and MySQL are regularly patched, so be sure to keep your version up to date. Most vulnerabilities that get exploited have been known for over a year, so installing security patches in a timely manner can prevent the majority of attacks by simply sealing up these flaws. Having a regular patching schedule and protocol can help to implement updates in a test environment so any negative effects can be discovered without interrupting production. Many shops lack this luxury and fly by the seat of their pants, installing updates directly into production and hoping for the best. Usually this works out, fortunately, but when it doesn’t, it can go bad quickly, so at the very least understand your rollback options and procedure, as well as exactly what the patch is changing.

4. Restrict the DB Processes
The user under which the database service runs determines the access database processes have to the rest of the server, including the filesystem, ability to execute programs, and so on. As with most Linux applications, MySQL will typically run under a dedicated mysql user account with minimal permissions to the rest of the server. You can verify this with a simple ps command, and make sure that MySQL hasn’t been configured to run as root, which definitely happens, especially in extreme circumstances, troubleshooting an outage for example, and then isn’t reconfigured once the crisis has been averted.

But in Windows installations, MSSQL is often run as local system or an administrator account, allowing database processes, including stored procedures and command shell interfaces like xp_cmdshell, full access. Ideally, MSSQL should be run as a dedicated, non-administrator local account with minimal privileges. Newer MS installation wizards can even automate this step for you, so if you’re installing a fresh server, be sure to configure this option. Other SQL services such as the SQL Agent should also run as restricted local accounts, with permissions given as needed, for example to a backup directory.

Failure to take this step can allow a compromised database server to compromise the rest of the machine and possibly infiltrate the network.

5. Restrict SQL Traffic
As mentioned in step one, database servers typically only have another server (or several) connecting to it. If this is the case, access to the server on the database ports should be blocked everywhere else. By only allowing SQL traffic to and from designated IP addresses, you can be sure that a malicious actor or infected client inside the firewall doesn’t hammer away at your server. In some instances, clients will need to connect directly to the database server itself, for example with a thick client front end application. The same logic applies here, restricting those SQL connections to the specific IPs (or at least IP segment) that need it. Because these are endpoints, be sure to secure them properly, as malware can scan and attack SQL servers. You can handle this with iptables on Linux, the Windows firewall, or preferably, a dedicated firewall device.

6. Use Least Privilege When Assigning Permissions
Database users, like users on any system, should only have as much access as they need to perform their duties. Stay away from “ALL” grants in MySQL and sysadmin role membership in MSSQL if possible. Consider granting read access to views instead of directly to tables, to protect sensitive fields if necessary. Stored procedures, maintenance plans and other automated tasks should be run as dedicated users with the appropriate permission set. This measure prevents any one piece of the database server, or any malicious or compromised user, from wrecking the whole system. Often times application instructions will have you put their users in a full access admin role. This is against general best practice and typically represents either sloppy programming that requires more access than it should, or a desire to remove security from support considerations, neither of which has your data’s best interest in mind, so always consider how implementing application accounts can affect your overall resiliency.

7. Set a Strong Admin Password
In MSSQL, the sa account is used whenever mixed-mode authentication is selected. Microsoft recommends using Windows integrated auth, but many applications require mixed-mode to support their database users and connection strings. If you do have mixed-mode auth enabled, be sure to secure the sa account with a complex password to prevent it being brute forced.
Similarly, the root user for MySQL should have a complex password. If someone is scanning your database server, the first thing they are going to do is try to login as the default admin account, so failure to lock it down can result in total system compromise.

8. Audit DB Logins
Part of your overall logging and monitoring should include login auditing for your SQL database. At the very least, these records will prove useful in forensic situations, but if regularly monitored or even integrated into an automated notification system, repeated failed logins can alert of attacks and other issues before they become critical, allowing you to disable compromised users or change their passwords, while logging successful logins keeps a record of which admins, users and applications have connected, helping troubleshooting and change management.

9. Secure Your Backups
Guess what? Your backups have the same data as your production databases and need to be secured with as much care as the server itself. This can mean locking down backup directories, restricting access to the server or storage hosting the data, physical security of removable media, network access to backups and reviewing who has access to perform and access backups. Just don’t forget backups are part of your data ecosystem when it comes to security or someone might just go through the open window to get around the barricaded door.

10. Protect Against SQL Injection
When a web application accepts user input and sends it to the database, unsanitized data can “inject” malicious code into the server and perform unauthorized tasks, including getting full shell access, depending on the server’s configuration. There are several ways to mitigate these attacks, including step 6 above, restricting the ability users to perform unauthorized tasks, but there’s really only one way to prevent them, and that’s to utilize stored procedures instead of direct SQL queries for webapp interaction.

Stored procedures only accept pre-established parameters and can only perform very specific functions, so they prevent the injection of data into a raw SQL query. This has been best practice for many years now, but many production applications still run code with SQLi vulnerabilities, one of the most commonly exploited vulnerabilities on the internet.

11. Continuous Visibility
Getting everything setup and configured securely can save you a lot of trouble down the line. But the only way to ensure that your database system remains secure is to have constant visibility into its configuration state, with tests being run against a policy you create. This way, you will be notified when something changes, say a new db user added as a sysadmin or given db_owner permissions. Without something like this, you’re essentially guessing that nothing has changed since you last checked, or even if you want to make sure, you have to manually gather your configuration information, which is both time consuming and ultimately futile, as to perform the same check in the future would require a replication of that effort. UpGuard offers continuous visibility into SQL database systems, as well as the rest of your servers and network devices. The first 10 nodes are free, so give it a try and find out if those two SQL servers you run are really as identical as they’re supposed to be.

Get the Digital Resilience eBook

More Articles

10 Essential Steps for Configuring a New Server

That’s a nice new Linux server you got there… it would be a shame if something were to happen to it.
Read Article >

The Website Security Checklist

Putting a website on the internet means exposing that website to hacking attempts, port scans, traffic sniffers and data miners. If you’re lucky, you might get some legitimate traffic as well.
Read Article >

The Email Security Checklist

By following this 13 step checklist, you can make your email configuration resilient to the most common attacks and make sure it stays that way.
Read Article >

Topics: SQL, security, mysql, database

UpGuard Customers