Five SQL Best Practices


You hear about it all the time on TV news, print magazines, and online articles … a new breach, a new attack, a new threat, a new vulnerability.

However, did you know that over 97 percent of data breaches are due to or completed with the assistance of SQL Injection?

This means that your data’s top vulnerability doesn’t arise from something new and unknown, but instead from failing to follow tried-and-true best practices in database security.

So, what is SQL and why is it so vulnerable to attack?

Simply put, an SQL Server is a relational database management system (RDBMS) that relies on Structured Query Language (SQL) to exchange data with applications. The largest SQL provider is Microsoft, which currently offers six editions the latest from just this year. The primary function of an SQL Server is to store, receive, and share data between applications. Those applications can be connected virtually to a server through the cloud or by way of a network of shared computers on premise.

In 2013, Target learned firsthand just how damaging bad SQL Server practices can be when a mega-breach affected over 40 million of its customers and their debit and credit card information from was stolen. Sadly, Target was just one of literally hundreds of databases that suffer breaches every year.

To save you and your company from an equally costly breach, there are five best practices you should implement immediately: (1) authenticate outside of the SQL itself, (2) remove unnecessary users, (3) limit privileges, (4) monitor failed login attempts; and (5) disable unused features or browser services. Let’s take a look at each one.

1. Authenticate Outside of the SQL Itself

SQL Server’s native authentication – known as “mixed-mode” – poses an enormous security risk, most notably its vulnerability to brute-force attacks. Today, a hacker brute forcing their way through an account can generate millions of passwords a second. Unfortunately, SQL Servers themselves do not come equipped with login limits; therefore, hackers can try those millions of passwords without being locked out. What’s more, when the SQL Server handles authentication credentials (username and passwords), no methods of identifying attacks exist. Thus, the first best practices is to avoid SQL Server Authentication in mixed-mode and connect to your SQL Server using Windows Authentication.

Windows Authentication offers major benefits: Microsoft supports it directly compared to SQL Server Authentication (for which Microsoft does not offer support), it prevents and identifies brute-force attempts, and it centralizes your organization’s account administration into an Active Directory.

2. Remove Unnecessary Users

In a database, access should not be granted to all users equally but instead restricted to necessary users as it relates to their work. Think of this as a “need to know” formula. The same goes for the database backup folder, where permission should be granted to users that require access. Adopting loose, one-size-fits-all access rules at a user level result in data mishandling, deletion of critical files, and abuse of sensitive information. In addition, removing users who have left your company when they exit is critical to eliminating malicious activity from disgruntled employees.

3. Limit Privileges

When installing or running a SQL Server, you will be given a default choice between three system-defined accounts: Local System, Network Service, and Local Service. These accounts define the permissions (i.e., privileges) to the services you want to run. Instead of opting for one of these three system-defined accounts, create a local domain account that has minimal privileges. Identifying permission can be difficult, so rather than making an assumption for each account, you should limit privileges again based on employee need. Since some permissions are needed for certain services, you will likely need to create separate accounts for each of the services. In addition, only the SQL Server account administrators should be given full authorization of data, backup directories, and read/write activities.

4. Monitor Failed Logins

Closely related to our first best practice – authenticating outside of the SQL itself – this best practice is also about preventing brute force attacks. To do this, you must be able to audit failed logins by enabling login monitoring in your Windows SQL Server Authentication. Once enabled, failed and successful logins will be recorded in the SQL Server error logs. With this information, you can create far more accurate user and privilege rules based on normal database behavior. By extension, you’ll also be able to identify abnormal behavior and respond to threats as they occur.

5. Disable Browser Service and Unused Features

Our last best practice is to disable unused services using the SQL Server Configuration Manager, or through the policy-based management feature if you are using an SQL Server that is 2008 or above. Features such as XP_CMDSHELL, OLE AUTOMATION, OPENROWSET, and OPENDATASET should be disabled for reducing surface area attacks.

In addition, four types of protocols are supported by SQL Servers: shared memory, named pipes, TCP/IP, and VIA. To further reduce security risks and damage, you should only use the bare minimum of these protocols. As with users and privileges, this means disabling all features or services that are not in use as well as keeping protocols to a bare minimum.

Beyond the Basic SQL Best Practices

The five practices just mentioned will set you on the path to securing your SQL Server. Still other best practices are needed to move beyond the basics. One such additional practice is to disable SA accounts to prevent attackers from using the default admin account. Another practice that you may want to opt for is enforcing complex password and password change policies; implementing a passphrase instead of a password leaves you less vulnerable and can actually be easier for employees to keep track of. Lastly, always remember to use Windows Authentication mode and to avoid creating SQL Server logins; instead, use an Active Directory to control access rights to groups.

However, did you know that over 97 percent of data breaches are due to or completed with the assistance of SQL Injection? While database security cannot handle all forms of SQL injection, one should consider a state of the art Web Application Firewall (WAF) to protect against SQL injection.

For an even more detailed look at database security, download the Imperva SecureSphere Data Security Datasheet here. For more information about SQL Injection attacks, check out a research report we published a while back on the subject.