Continuing on with our ChatGPT series, where I asked ChatGPT to give me seven blog post ideas, we’ll talk about some best practices when it comes to securing your databases in SQL Server.

Where to Begin?

There are a lot of angles you could come from when talking about best practices. I’m starting to realize that best practices, sometimes, are relative to who you’re talking to. When it comes to security and SQL Server, standardizing and implementing security best practices can sound good on paper, but is much more difficult to maintain long term. How many times have you seen (I’ll say seen cause as the awesome DBAs we are, we never veer from perfection in our own environments) situations where someone gives elevated permissions to a user or a service account just to get an application working, and then “forgets” to take those elevated permissions away later? More times than I’d like to admit. Anyways, here are seven best practices that I like to consider when securing my databases in SQL Server.

Authentication and Access

There are two commonly used types of authentication that are implemented in SQL Server security: Windows Authentication and SQL Server Authentication. Windows Authentication authenticates the user when they log into their computer with their windows account, and their account is stored in Active Directory (AD). It usually looks like this in the logins folder of your SQL Server:

DOMAIN\UserName

This allows the user to basically be “pre-authenticated” so when they connect to a SQL Server they have access to, the authentication is relying on the Windows user and password that was already provided when they logged into their computer. SQL Server Authentication works by way of a username and password being supplied when connecting to a server. There is no “pre-authentication” that as was described before and the user is authenticated at the time of logging into the SQL Server. In the logins folder, you’ll usually see just a username without the domain prefix like this:

SQLUser1

There is another method of securing access to your SQL Server and that is by using groups. Groups are much more streamlined when it comes to security and long term maintenance. With groups, you can create one in AD, put users in that group, create the group in your SQL Server, and then just assign permissions to that group. That way, if a user leaves the company, or no longer needs the permissions the group is assigned, it is much easier to just remove the user from the group in AD, than to remove the user if it were explicitly created in SQL Server. In order of what is most secure, here’s a general guideline to follow when deciding what type of authentication to implement on your SQL Server:

1. Groups
2. Windows Authentication
3. SQL Server Authentication

Principle of Least Privilege

When deciding what permissions to give to users or groups in your environment, following the Principle of Least Privilege (we’ll call it PLP in this post so I don’t have to repeatedly type that) is a must. At a high level, PLP refers to granting users the lowest level of permissions so that they can still carry out expected work and nothing more. For example, say you have a service account that an application is going to use to connect to your SQL Server. One of the first things that should be asked is “What is this application going to do?”. Say the application just needs to read data from a specific database, well, then the only database role that should be assigned to that service account is db_datareader. It doesn’t need db_datawriter, or db_securityadmin, and for goodness sake it does not need db_owner. The point here is to get an understanding of what exactly the users and logins are going to be doing in your databases and assigning the appropriate permissions so they can accomplish their work.

Updates and Patching

Get it? Eye patch cause of patching? Anyways, patching and updating your SQL Server with Cumulative Updates (CUs) and or Service Packs (SPs) promotes and ensures a secure and stable environment. Microsoft routinely releases these updates that implement security improvements and fix security bugs or holes for your SQL Server environment. This is definitely an underrated best practice in my opinion.

Encrypt your Data

There are a lot of methods of data encryption that can be used to encrypt your data and secure it. Transparent Data Encryption (TDE) is a great technology to use to encrypt your data at rest. At a high level, this basically prevents someone from being able to restore your database onto another server unless they have the certificate associated with the database. Secure Sockets Layer (SSL)/ Transport Layer Security (TLS) is a good tool to help encrypt your data in transit. There is another method of encryption called Always Encrypted that protects sensitive data from being accessed by unauthorized users, even other database administrators. Thorough research should be done to find out what method of encryption should be used for your environment.

Secure your SQL Server Network

Hopefully your infrastructure team is already doing this as apart of their standards, but if you’re wearing many hats in your shop, it’s best to know that securing your SQL Server network is an important part of database security. It is best practice to disable the SQL Server Browser service if it is not needed, use firewall rules to restrict access to your SQL Server, and in some cases change the default SQL Server port from 1433 to a non-standard port. Of course, that last one is easier said than done, but it’s a good option to consider to further secure your databases.

Implement Strong Password Policies

If you do decide to set up your SQL Server with Mixed Authentication, and you choose to use SQL Authentication, please, please, please use strong passwords. You have no idea how many times I’ve seen the password of a SQL Authentication user be set to ‘password’. It is even better if you have a tool that can manage and rotate passwords for you regularly. However, I don’t recommend having an expiry date for service account passwords. The last thing you want is for some password to expire for a critical account at 12 midnight and you’re woken up by the on-call page that SQL stops working or a business critical application suddenly dies.

Regular Backups and Test your Backups

Arguably speaking, the most important best practice of all is this last one. As a DBA, backups are the #1 way you can ensure your databases are secure. In the event you are hit with ransomware, corruption, or a developer deleting a record without a WHERE clause, backups will get you restored back to a stable state. Just taking backups is not enough though. You should ensure that your backups are encrypted, have some sort of redundancy – meaning you back them up locally or to a network share, and then copy those backups off somewhere to an offsite location, and test your backups. Implementing a process to test your backups and check for corruption is a surefire way to make sure your databases remain secured in the event of an emergency.

Your Turn!

What are your security best practices? I’d love to hear what you like to implement when you are making sure your databases are secure.