I’m going to use this blog post to name 5 SQL Server security best practices that should be your top priority in keeping your SQL Server environment safe. You are, maybe, a newbie DBA who is looking for the best practices in maintaining a SQL server environment or you are just a first-aid Windows systems engineer who also administers other Windows services. As a DB admin, you need to take care of SQL server security and protect the integrity of your organization’s data.

On top of hardening the physical security measures, you should always have additional means of ensuring that SQL Servers, databases, and corporate data stay safe.

1. Allow access to SQL Servers to authorized users only

For obvious reasons unauthorized users have no place in your SQL Server environment, let alone have the permissions to snoop around and access any segment of your SQL server environment. No exceptions.

At no point should a database backup be vulnerable and reachable by unauthorized users. It takes only a few minutes to delete a corporate backup, accidentally or intentionally.

Revoke all unnecessary privileges for user and system databases. DO NOT use SQL authentication. It is not needed for any reason. Use the Windows authentication.

2. Regularly perform SQL Server login audits

Audit all accesses to SQL server. You need to be able to prove who actually had access to the server and to see if you have too many failed login attempts.

You need to turn on both failed and successful login tracking for production servers in the SQL Server Management Studio. What’s safe is safe. Navigate to the Server Properties and in the Security tab, go to Login auditing.

So, by tracking both failed and successful attempts, you will have an answer ready if an auditor asks you for a list of all people logged into a certain instance. Another common use case might be if a user can’t log in for some reason, and then you can check what’s going on. However, if you do track both, be aware that this can lead to quite a collection of event records, so you may opt for auditing just the failed logins.

3. Change the Server Authentication mode

In one of my previous blog posts, I talked about the benefits of using Windows Authentication rather than SQL Server Authentication. This is really important when it comes to SQL Server security best practices. The reason is that Windows Authentication makes accessing your SQL Servers more secure because you will leverage the existing Active Directory environment.

The other option is to use the mixed mode and while you’re at it, set a strong password for the SA and don’t use the SA account for authentication (create a new one)! You can change the authentication mode in the SQL Server Management Studio or Transact-SQL. Of course, there are times when one or the other will suit your environment better; however, I recommend Windows Authentication because it’s more convenient.

4. Encrypt SQL Server database backups

This is the most important measure for protecting corporate data. Even if the data, somehow, gets stolen, without a decryption key it’s useless.

There is transparent data encryption and backup encryption. The latter was introduced with SQL Server 2014, and offers the option to encrypt a backup directly from the database engine. There are, of course other ways and third-party tools to back up your database, so be sure to browse the forums and check what may be suitable for you.

Before you find yourself needing to restore data from a backup, make sure you backed it up properly, because after all, it’s all about recovery plans—you don’t want to wind up with a corrupt backup.

5. Configure a server to listen on a different port

Change the default SQL Server port value, namely because 1433 is well known to malicious users. To find out how you can configure SQL Server to listen on a specific port, check out this blog post.

Now, before I conclude this rant about the basic things you can do to harden your SQL Server security, I’d like to ask you a few questions, so feel free to leave a comment below.

Do you know how many SQL Server instances you have, what authentication is enabled, and if your databases are being backed up?

If you want to ease your DBA and SQL Server administration troubles, there’s an easy-to-use third-party tool that I talk about all the time. It’s good for managing a SQL Server environment—it’s SQLDocKit.

You can use SQLDockit to audit SQL Server instances and configurations, check which users have access to particular databases and what roles they have, check if backups are being performed regularly, and much more. For example, you can check disk allocation, manage database growth, keep an eye on modelDB growth, and perform other maintenance tasks from one central console for all your SQL servers.

Can you keep up with service packs, versions, and updates?

How secure is the SQL server environment you administer? What special measures do you take to secure your SQL Server environment? I am really interested in seeing what approaches other great system engineers are using.