Manage Learn to apply best practices and optimize your operations.

Implement SQL Server security best practices in 4 easy steps

Managing SQL Server security doesn't need to be difficult. These four best practices from Microsoft MVP and author K. Brian Kelley can help DBAs keep databases secure.

Maintaining SQL Server security isn't easy, but breaking the process down into manageable steps can help DBAs get a better handle on it. Manual checks, regular audits, encrypted backups and minimizing the attackable surface area are all SQL Server security best practices that make it more difficult for hackers to gain access.

In a recent webinar, DBA and SQL Server author K. Brian Kelley discussed how DBAs can adopt SQL Server security best practices to ensure corporate data is secure in various versions of Microsoft's database.

1. Minimize the surface area

Kelley compares SQL security to the mathematical term surface area.

"Basically, what we want to do is make it harder for an attacker to be able to go after SQL Server," he said.

Tools like the Surface Area Configuration tool (2005), Surface Area Configuration facet and Policy Management (2008+) can be used to minimize the surface area, making it difficult for potential attackers to access SQL Server.

SQL Server uses a password policy the OS understands. Kelley recommends enabling a password policy for SQL-based logins. Using a password policy follows OS password policy rules, which will lock a user out after a preset number of failed login attempts. With Windows-based login, the password policy is enforced by the OS or the trajectory. Windows-based logins will also lock an account after numerous failed login attempts.

Another important measure in the list of SQL Server security best practices is database-level permissions. Database-level permissions should be treated the same way you would treat server permissions. Users with too many privileges can do anything, including access and change the database. Monitoring who has what privileges may not prevent incidents, but it can make it easier to track who has done what and how.

2. Encrypt your backups

If your backup isn't encrypted, that data is accessible to third parties.

Maintaining SQL Server security isn't easy, but breaking the process down into manageable steps can help DBAs get a better handle on it.

Passwords prevent backups from being restored, but the data in them is still readable. Passwords don't protect backup databases from being opened, perused or copied. With sensitive data, backups should always be encrypted.

When backing up data, it's important to consider who has access to the backups. In most environments, backups are written to network locations, and it's important to know who has access to those locations. Unfortunately, protecting backups with auditing just doesn't cut it, as it doesn't prevent unwanted individuals from accessing the backups.

3. Audit everything

The majority of the time, users have permission to perform certain tasks; DBAs, for example, have system admin rights. Users can't do their jobs without this access. This requires user flexibility, which makes it difficult -- if not impossible -- for administrators to put preventative measures in place.

Most of the time, preventative measures aren't needed; it's rare that an employee will deliberately jeopardize the company. However, administrators need to account for human error, which is more common than human malevolence. Because preventative measures are off the table, administrators must turn to audits as a way to catch perpetrators after -- or during -- the act.

SQL Server security tips

Audit all logins. Keep track of logins and look for logins that suddenly appear. When attackers attempt to gain access to a system, the first thing they do is try to create a login; this acts as a backdoor, giving hackers access even if their main route to the server is shut down. Administrators should keep a close watch on SQL Server logins, Windows users and Windows groups.

Server roles must also be audited. Environments utilizing Windows groups should be audited for Windows users. As users move around in the organization, their permissions should change. If a user's role within the organization changes, administrators need to be notified to update that user's permissions.

Often times, however, the administrator will not be alerted to remove outdated permissions and privileges; therefore, the user will still be able to access data even if it has nothing to do with their new role. Administrators who are more on the ball know to revoke outdated and unused permissions, Kelley explained in the webinar.

4. Check it manually

Manual checks are often the last resort for recurring tasks. Kelley recommends that administrators regularly take the time to manually check their databases and servers.

As a general rule, if there's one problem, there are probably more that aren't readily noticeable. By manually checking for irregularities, administrators can familiarize themselves with the servers and databases, and they are more likely to notice when something is wrong.

Dig Deeper on SQL Server Security