This isn’t the complete guide to securing a SQL Server, but these are some of the more common things I bump into and wish I wouldn’t when it comes to SQL Server Security. Read these practices, understand them by searching the web or come here and ask me for questions or advice. If it means a more secure server? I’m happy to answer quick questions to me that can point you in the right direction. Too many people aren’t securing their databases like they could and should. Consider this “Security 101” and as part of my Straight Up SQL Server Tips series, I want to explore the beginnings of the topic here, we’ll go into more detail on subsequent posts. So, without further preamble – the concepts I want to stop seeing on SQL Server Health assessments we do:
SQL Server Security and the SA login
When it comes to SQL Server security, this is number one. The SA account is the default sysadmin account for SQL server. So many people use that daily for various things. So many more have “stupid easy” passwords for it. Stop that. Move away from SA entirely if you can. Disable the SA account, create a Windows group with that access or create a backdoor SQL authenticated account with sysadmin privileges and lock the secure password up in a vault. If you can’t disable SA – make that password quite complex. When people try and make a move on your SQL Server, they will invariable try through the SA account.
Who has the keys to SQL Server?
Look at your logins. Who can get on? What kind of password policy do you enforce with SQL Server authenticated users? Can you move away from SQL Authentication and move towards the generally more secure windows authentication? If so? Do it. If you can’t, enforce password policies for complexity. I cannot tell you how many times I see “password” or passwords like it for highly privileged SQL Authenticated users. Even at large companies. People are creatures of habit and they like to make it easy. Don’t let them take security easy or lightly. And while there – look and see who is in the fixed server roles – especially sysadmin, setupadmin, securityadmin, serveradmin – who has access to these privileges? Why? Does it make sense? Try and use least privilege principles and keep the list of those with higher privileges as low as you can to help guarantee SQL Server security.
Who can get on the server? And Why?
This is basic. This is a 101 post about SQL Server security. But I cannot tell you how many times I go to do an assessment and see a large list of “Not sure who that is” accounts in the local administrators group. Limit your surface area. Know who is in local administrators. Know who is a domain admin. Know who can RDP to a server. And keep that list small. 99% of what your users, even your DBAs, need to do to SQL can be done without a connection to the server. Keeping this list small prevents folks from hacking into SQL Server – depending on the version this can take anywhere from a few seconds to several minutes. But with local administrator access, someone with no SQL Server access can get themselves access to SQL server generally speaking. Keep this list tight, like the sysamin role.
Firewalls
Use them. SQL Server really has no reason to be on the “outside” of the firewall. It should be inside a layer of security. Sure a web server or a load balancer in front of it needs to have access to the outside world; generally speaking, SQL Server doesn’t. Keep it behind a tight firewall. Poke only those holes that are needed to get to those servers that SQL must communicate with. For sure that includes monitoring tools and ways for DBAs to gain access to do their jobs. For sure it includes the ports (hopefully not the default port) that SQL Server is speaking to your applications on. But lock it down to only those known servers, and keep it limited. You want your data behind the best layer of security you can afford. Data is a commodity. Data is your business. A lot of people want that data. Don’t let them take it. Don’t be the next breach caused by missing SQL Server Security.
Default Port
Sure it is “only” security through obscurity, but it can slow some attempts down and is something that you could get asked about in an audit or after an attack. Not just because of my blog post but because Microsoft and other bloggers hammer this one a lot. So will the auditors. If you are using the default port for SQL Server – you can change that.
Encryption
There are really two phases of data that can be encrypted. It can be encrypted at rest or in transit.
At rest means you are making it so if someone steals a hard drive with data, or with a backup, they can get into the file but the contents are meaningless. Out of scope for this series but this can be accomplished through Transparent Data Encryption in SQL Server enterprise, through third party drive encryption tools like Bit Locker regardless of SQL Server version. Of course, anyone with SA level access or application access can still see the raw data, so this doesn’t obviate the need for security awareness and best practices. You can also take advantage of a feature like Always Encrypted in SQL server 2016 (and with SP1, available in Standard edition SQL) to write encrypted data from the application to SQL – even the DBA won’t see what the data is without the decoder ring in the application.
In transit means you are sending data over the wire in an encrypted manner. Today? With no network encryption options, the data going across the wire in TDS packets can be sniffed and read potentially. So someone can intercept data and possibly get access to data. Encrypting connections can help here.
Service Account Best Practices
I nearly always bump into environments with the Service Account having windows local administrator rights. I’ve even bumped into a service account with Domain Admin rights a time or two!! I also often find many SQL Servers all using the same exact service account. Think about least privilege – only the permissions necessary (Domain user, yes. Local admin, no.) Think about password complexity. Think about “what happens if this account is compromised or fails?” – if you use one service account to rule all SQL Servers, then one service account problem affects all. Each SQL Server service should have its own. Prod and Test should always be separate (I use that to prevent against human error also). And you should be looking into Managed Service accounts and Group Managed Service Accounts if you’ve been taking care to upgrade. You can read more about service accounts from Microsoft.
Upgrade for SQL Server Security
One last point – speaking of upgrading, if you aren’t keeping up with your SQL Server versions? You aren’t secure by default. You need to keep up with your SQL Server updates and your windows updates. Again it really does help, but the auditors really will ask. I blogged about SQL Server versions a month or so ago in this series of tips.