{"id":49038,"date":"2022-12-31T00:00:00","date_gmt":"2022-12-30T23:00:00","guid":{"rendered":"https:\/\/jotelulu.com\/blog\/how-to-make-your-sql-server-more-secure\/"},"modified":"2022-12-31T00:00:00","modified_gmt":"2022-12-30T23:00:00","slug":"make-sql-server-more-secure","status":"publish","type":"post","link":"https:\/\/jotelulu.com\/en-gb\/blog\/make-sql-server-more-secure\/","title":{"rendered":"How to Make Your SQL Server More Secure"},"content":{"rendered":"

In this article, we’ll look at the current best practices to make your SQL server more secure<\/strong>. Focusing on SQL Server 2022 deployed on a Microsoft Windows Server, we’ll examine the steps you can take to protect your data and have the most resilient and secure architecture possible.<\/p>\n

NOTE:<\/strong> Please note that the content of this article is mainly focused on Windows Server, but some of the recommendations here will also be valid for GNU\/Linux systems.<\/em><\/p>\n

As well as the tips that we’ll share with you in this article, we also recommend that you check out the article titled SQL Server 2022 Installation Requirements and Planning<\/a> on our blog.<\/p>\n

 <\/p>\n

Tips and Best Practices to Make Your SQL Server More Secure<\/strong><\/h2>\n

The first thing that is important to stress is that you can never have too much security<\/strong>. That’s why it is important to have multiple layers <\/strong>that provide different levels of protection for your data and systems.<\/p>\n

When securing any IT system, it’s essential to take a holistic approach. That means applying security measures to all levels and <\/strong>aspects <\/strong>of your installations, whether it’s the physical server installation, security patches or the way in which queries are designed.<\/p>\n

With that in mind, in this article, we’re going to take a look at the different aspects to consider if you want to keep your SQL database server as secure as possible.<\/p>\n

 <\/p>\n

Physical Security<\/strong><\/h3>\n

Before you think about software security, the first thing you need to do is ensure that your server is physically secure<\/strong>. It doesn’t matter what firewalls, antivirus programs, patches or access controls you use; it will all be completely worthless if there is no physical security for your server. This means preventing people from physically getting to the server, ensuring that the server temperature is regulated, etc. Otherwise, someone could simply walk in and turn it off or even remove it altogether.<\/p>\n

Physical server security can actually be quite complex. First, you should ideally ensure that the servers are located in their own separate room <\/strong>with access controls<\/strong>. This could be by using an electronic lock or a biometric system, for example. That room should also have some kind of fire extinguishing system<\/strong> and air conditioning <\/strong>to keep the temperature stable and stop the servers from overheating.<\/p>\n

Then, while it’s not strictly speaking a form of physical security, you should also consider the other utilities and services that your server needs. For example, you should consider whether you have redundant power supplies <\/strong>or uninterrupted power supplies<\/strong> (UPS). You may even want to consider having power connections from different energy providers just in case. And then you may want to consider having a redundant internet connection<\/strong> in case your main one fails. All of these elements will play a key part in ensuring that your server is physically protected from all manner of attack or incident.<\/p>\n

 <\/p>\n

OS Security<\/strong><\/h2>\n

This is something that I will never get tired of saying: if you don’t have a resilient operating system, it will be impossible for any application that you run to be completely secure.<\/p>\n

When first setting up your infrastructure, it’s essential to only deploy the services you need <\/strong>on the server. Ideally, you should only have one service per server, making use of virtual machines, containers<\/strong>, etc. That way, you’ll avoid creating single points of failure. Of course, for databases, you should definitely deploy them on a separate server that is only used for this service.<\/p>\n

It is also important to apply all available patches and updates <\/strong>that apply to you, but don’t install any that aren’t relevant.<\/p>\n

For example, if you don’t have a given piece of hardware, it may end up being counterproductive to install a patch for it. Similarly, if there is an available patch for Office but you don’t have Office installed, it’s best not to install the patch as it could contain a bug that may cause wider issues.<\/p>\n

Ideally, you should have a test environment available where you can trial any updates before applying them to your production servers.<\/p>\n

Of course, it almost goes without saying that you need to make sure that you have antivirus and antimalware software installed<\/strong>. The number of threats is forever increasing, and it’s important to keep yourself protected against them.<\/p>\n

Lastly, it’s important not to forget the Operating System firewall<\/strong>. Make sure you only open the ports you need. If you need help with this, we recommend checking our article titled Managing SQL Ports on Your Windows Server<\/a> or the article on Microsoft Learn about the Windows Integration Services Service<\/a>.<\/p>\n

 <\/p>\n

Now that we’ve looked at physically securing your server and the steps to secure your operating system, let’s take a look at how to make your SQL Server more secure.<\/p>\n

 <\/p>\n

SQL Server Files Security<\/strong><\/h3>\n

SQL Server stores some files <\/strong>using the operating system’s file system. To avoid any potential problems, you should restrict access <\/strong>to these files. To determine which folder locations you need to restrict, run the following function in SSMS:<\/p>\n

SELECT CONVERT(char(20), SERVERPROPERTY(‘productlevel’));<\/em><\/p>\n

NOTE: <\/strong>In this tutorial, we’re working with SQL Server 2022, which is 16.x, but we’ll explain this for those that might have a different version. <\/em><\/p>\n

Here is a table of the possible results:<\/p>\n\n\n\n\n\n\n\n\n\n\n
Version<\/strong><\/td>\n*nnn*<\/strong><\/td>\n{nn}<\/strong><\/td>\n<\/tr>\n<\/thead>\n
SQL Server 2022 (16.x)<\/td>\n160<\/td>\n16<\/td>\n<\/tr>\n
SQL Server 2019 (15.x)<\/td>\n150<\/td>\n15<\/td>\n<\/tr>\n
SQL Server 2017 (14.x)<\/td>\n140<\/td>\n14<\/td>\n<\/tr>\n
SQL Server 2016 (13.x)<\/td>\n130<\/td>\n13<\/td>\n<\/tr>\n
SQL Server 2014 (12.x)<\/td>\n120<\/td>\n12<\/td>\n<\/tr>\n
SQL Server 2012 (11.x)<\/td>\n110<\/td>\n11<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

Once you know which version of SQL Server you are running, you can use the table able to complete this folder location<\/strong> <\/strong>“<drive letter>:Program FilesMicrosoft SQL Servernnn”.<\/p>\n

Where:<\/p>\n