10 Best Practices for Hardening SQL Server Security

In the IT world, security is a top priority, especially nowadays with ransomware and cyber extortion increasingly filling the news headlines.

Ransomware isn’t just an inconvenience anymore, precisely because of the increasing number of security breaches, this calls for a blog post to revisit your database and server security.

The biggest problem DBAs face is not the hacking part but the fact that they haven’t backed up their corporate data on time. Then, when a security breach happens, restoring data is nearly impossible.

And you know what the best part of it is?

The guy you pay to get your data back after a ransomware attack isn’t even the guy who stole your data in the first place. What happens is that the stolen data changes hands, and your ransomware notes are being replaced with other notes, which makes tracking down your data even harder.

And besides, there’s no guarantee you’ll ever see your data ever again even if you pay up.

Hardening SQL Server security with SQLDocKit

Before I get started with the details on what you should look out for and how to harden SQL Server security, I’d like to point out the obvious—all the security implementations and special configurations won’t save you if you only secure the SQL Server instance and fail to secure the physical environment.

For example, don’t place the SQL Server in a location with poor physical security. This also means you should work on minimalizing exposed segments, leaving no loopholes.

SQL Server security should always be taken seriously because, depending on the type of business your company runs, damages caused by easily overlooked mistakes and—of course—omissions caused by pure carelessness can be irreparable if hackers get a hold of data such as social security and credit card numbers, client information, or—even worse—medical records.

That’s why managing an SQL Server environment is easier and more transparent with a third-party tool such as SQLDocKit, a tool developed for DBAs that can be used to see the list of all servers and users who have access to those servers.

With a combination of SQLDocKit features, you can have a better insight into your SQL Server environment and secure it by keeping an eye on your SQL Server instances, databases, storage, tracking changes, and auditing SQL Server database roles.

While some of these best practices may not seem like crucial measures but in the long run, they all lead to a more secure environment and the safekeeping of corporate data.

Basic SQL Server Security Best Practices

As with most IT issues, it’s always beneficial to research the community best practices because when things go south, there’s no going back.

1. Auto-detect SQL Servers in the domains

Find out how many SQL Server instances you have. That’s kind of where you should get started with the whole security matter. You’re responsible for all SQL Servers, which tends to get problematic if you don’t know how many of them you have and where are they located.

SQLDocKit can auto-detect all SQL Server instances in your domains. You can use the SQL Server inventory feature to generate an entire inventory containing information on SQL Servers and Databases, as well as Windows Servers that are hosting these databases.

2. Restrict who has access to SQL Servers

While you might think it’s a good idea to skip this step because it’s too much work, it’s actually one of the most important things.

Unauthorized users should have no business snooping around the SQL Servers. Even when giving permissions to the newly employed DBAs, you ought to know that this person is trustworthy and won’t try to pull any malicious stunts. Think carefully who should have access to the database backups and minimalizing the number of administrators.

3. Incorporate strong passwords

Always have strong passwords because this is usually the weakest link in SQL server security, and yet it’s the simplest requirement to meet.

Try not to pick words that can be easily guessed or found in a dictionary. Also, avoid using any names of your computers when creating your passwords.

If you’re choosing a new password, make it as different as possible from your previous one, avoid using any password patterns and change passwords regularly. This goes for all passwords anywhere.

I know all of this is kind of obvious, but you haven’t seen your fair share of disasters, obviously.

4. Have a fool-proof maintenance plan

We’re not going to go on about how and why having a maintenance plan is important before things go south. Backup your databases so you don’t get into the situation where you want to throw yourself off the roof.

SQLDocKit can help you find out when was the last time you backed up your databases. Corporate data is the heart of any business that’s why you should always test that you backed up your data properly in case you need to restore the data. Be sure to back up transaction logs as well.

When doing the database backup, encrypt them. Two ways exist to do this: transparent data encryption and the SQL Server 2014 backup encryption feature. Third-party tools are also at your disposal when backing up databases so chose an option that suits you best. For example, back it up on a disk and encrypt it, or use the Windows Azure Storage.

5. Turn off unnecessary services and features

Each unused service can become a potential vulnerability, and that’s why it would be a good idea to disable all nonessential services. Change the settings manual start-up or just disable them altogether. Security features are updated with the service packs, so you should always regularly install service packs, security patches, and cumulative updates.

SQLDocKit can help you with these too so you can keep your servers up to date.

6. Use the SA authentication

It’s always best to configure different authentication modes; however, my advice is that you should always use a Windows account for authentication. You can change the authentication mode in the SQL Server Management Studio or Transact-SQL. With SQLDocKit, you can check the security settings to get a quick overview of which type of server authentication you’re using.

7. Track failed logins

You can choose whether or not you want to monitor SQL Server logins and audit both successful and failed, or just the failed logins.

The best advice is to track both failed and successful attempts because 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, 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.

8. Change the default SQL Server port

TCP 1433 is the default port for the SQL Server, and it’s also the official Internet Assigned Number Authority socket number for the SQL Server. It’s used to connect to the database engine, and the SQL Server Management Studio uses it to manage SQL Server instances across the network.

Because all these default static ports are well-known and can be used for some low-level amateur hacking (which can still bring havoc in your environment), they are changed when assigning server ports.

You can change this value in the SQL Server Configuration Manager, navigate to the SQL Server Network Configuration, then select the instance whose port you want to change and change the port number. Here’s an article with a step-by-step guide how to change the SQL Server port.

In cases when you have a cluster, disable the check pointing to the quorum to prevent reverting to the original settings.

Take a look how some of the reports look like. All SQLDocKit reports can be exported, delivered via email, saved to your SharePoint site, or stored on a file share system.

Try SQLDocKit, and tell us what you think!

I’ve only named 8 things, what would you name as the two remaining?

Tell us what was your worst DBA nightmare about security.

Wouldn’t it be awesome if you could administer your SQL Server environment and at the same time cover a vast array of security issues? I bet it would.

SQLDocKit has a free 30-day trial and if you liked some of the options and features we discussed here, give us a call so we can set up a personalized demo for you.