[BreachExchange] Implement SQL Server security best practices in 4 easy steps

[Destry Winant]

https://searchsqlserver.techtarget.com/tip/Implement-SQL-Server-security-best-practices-in-4-easy-steps

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.

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.

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.