How to Make Your SQL Server More Secure

In this article, we’ll look at the current best practices to make your SQL server more secure. 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.

NOTE: 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.

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 on our blog.

Tips and Best Practices to Make Your SQL Server More Secure

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

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

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.

Physical Security

Before you think about software security, the first thing you need to do is ensure that your server is physically secure. 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.

Physical server security can actually be quite complex. First, you should ideally ensure that the servers are located in their own separate room with access controls. 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 and air conditioning to keep the temperature stable and stop the servers from overheating.

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 or uninterrupted power supplies (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 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.

OS Security

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.

When first setting up your infrastructure, it’s essential to only deploy the services you need on the server. Ideally, you should only have one service per server, making use of virtual machines, containers, 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.

It is also important to apply all available patches and updates that apply to you, but don’t install any that aren’t relevant.

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.

Ideally, you should have a test environment available where you can trial any updates before applying them to your production servers.

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

Lastly, it’s important not to forget the Operating System firewall. 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 or the article on Microsoft Learn about the Windows Integration Services Service.

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.

SQL Server Files Security

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

SELECT CONVERT(char(20), SERVERPROPERTY(‘productlevel’));

NOTE: 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.

Here is a table of the possible results:

Once you know which version of SQL Server you are running, you can use the table able to complete this folder location “<drive letter>:\Program Files\Microsoft SQL Server\nnn\”.

Where:

• <drive letter>: is the drive where SQL Server is installed.
• nnn: identifies the version of SQL Server.

It’s also important to remember that there may be other components depending on your installation.

• MSSQL: SQL Server Storage Engine. This will be followed by a version number, an underscore and the secondary version, a full stop and an instance name. For example: MSSQL{nn}.MSSQLSERVER.
• MSAS: Analysis Services. This will be followed by a version number, an underscore and the secondary version, a full stop and an instance name. For example: MSAS{nn}.MSSQLSERVER.
• MSRS: Reporting Services. This will be followed by a version number, an underscore and secondary version, a full stop and an instance name. For example: MSSQL{nn}.<instance>.

Having said all that, for an instance called “Test1”, the folder locations would be as follows:

• C:\Program Files\Microsoft SQL Server\MSSQL16\
• C:\Program Files\Microsoft SQL Server\MSAS16.Test1\
• C:\Program Files\Microsoft SQL Server\MSRS16.Test1\

SQL Server Security in the System Registry

Just as you need to protect SQL Server files on the file system, you also need to protect entries in the Windows Registry (which you can access using RegEdit).

Once you know which version you’re running, you should protect the following registry entries which are found in “HKLM\Software\Microsoft\Microsoft SQL Server<instance>”.

For the previous example of an instance called “Test1”, these entries would be found at:

• HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL16.Test1
• HKLM\Software\Microsoft\Microsoft SQL Server\MSAS16.Test1
• HKLM\Software\Microsoft\Microsoft SQL Server\MSRS16.Test1

Configure SQL Server Database Engine for Encrypting Connections

To further secure your platform, you should apply encryption to all inbound connections to Microsoft SQL Server. There is the option to enable encryption just for a specific group of clients, but in our opinion, this is much less secure than enabling it for everyone.

To configure encryption, you first need to configure SQL Server to use a certificate that meets the certificate requirements for SQL Server. This will then allow you to apply other measures that give your DBMS an additional layer of security.

In this article, we don’t want to get too bogged down in the detail if we can help it. So, to find out how to configure SQL Server to use a certificate, check out this page on Microsoft Learn, where you can also learn how to change the encryption settings for your instance of SQL Server. With these two steps, you can enable encryption for all inbound connections to SQL Server when using a certificate from a trusted certification authority.

Changing Accounts Used by Services

Another fairly simple measure that you can implement is to change the accounts used by services to authenticate SQL Server. This is really important, both changing passwords (which should be easy as they should be unique) and changing user names so that they’re not generic. This will significantly increase security.

To change these accounts, you will need to open the SQL Server management tool, which you can either run from the Start menu in Windows or by running the program found at “C:\Windows\SysWOW64\SQLServerManager<nn>.msc”, where <nn> is the version of SQL Server that you have installed.

Under the heading “SQL Server Files Security”, we explained how to find out which version of SQL Server you’re running. For example, if you are running SQL Server 2022, the version number would be 16 and the file location for the management tool would be “C:\Windows\SysWOW64\SQLServerManager16.msc”.

NOTE: When a password is changes in the SQL Server management tool, the changes will take effect immediately without the need to restart the service.

Dynamic Data Masking

Dynamic data masking (DDM) provides an additional layer of protection by limiting access to data by non-privileged users.

NOTE: DDM is only available from SQL Server 2016 onwards.

This measure provides a good level of security with relatively little design effort. Dynamic data masking allows customers to specify how much data they want to reveal for their query and who has access to it.

One of the benefits of DDM is that it can be applied to fields that you don’t want to filter out. Other details from the same table can be shown while preventing access to specific columns.

To implement DDM, you will need to designate a central data masking policy which will act on the sensitive fields in the database. Next, you will need to designate the privileged users or roles that will have access to the data. They will be the only ones with access. Access will be denied to all other users and roles.

Dynamic data masking is configured using T-SQL commands, but there are limits to their use and you will need to follow a specific syntax.

Firstly, it’s not possible to use DDM on the following column types.

To begin with, we need to know that you cannot use DDM on the following types of columns.

• Encrypted columns
• FILESTREAM
• COLUMN_SET
• A column in a PolyBase external table
• A column with data masking can’t be a key for a FULLTEXT index.

It’s also important to bear in mind that you can’t configure masks for computed columns. However, if a computed column uses a column with a mask, the resulting data will be masked.

To set up DDM, you will need CREATE TABLE and ALTER permissions, whereas to modify, add, replace or remove a mask, you’ll require ALTER ANY MASK and ALTER TABLE permissions. Lastly, users with SELECT permissions will be able to view the table, as with any other query, but it’ll depend on the possibility of access.

To learn more about this option, we recommend reading this article on the Microsoft Learn site.

Extended Protection for the Database Engine

There is a security feature on SQL Server that for some reason is not activated by default. Instead, the administrator needs to turn it on. This feature is called Extended Protection for Authentication.

Extended Protection for Authentication is a feature of the network components implemented by the Operating System. This feature makes connections more secure when they are made using Extended Protection.

This feature uses either service binding or channel binding to prevent authentication relay attacks, where an attacker uses credentials to pass for a legitimate server and authenticate themselves on the service.

To enable this function, open the SQL Server Configuration Manager, expand SQL Server Network Configuration, right-click on “Protocols of _<instance>” and click on Properties (<instance> is the name of the instance you wish to configure).

Then, on the Advanced tab, activate the “Extended Protection”, checking that Forced Protocol Encryption is also enabled.

You’ll need to restart the database for the changes to take effect.

Review the SA Account

The SA account is a well-known account that is built in to SQL Server and has generated much debate between SQL Server administrators and OS administrators. This account is used to log on to the SQL Server database engine and has special administrator privileges.

It’s created by default when deploying an instance of SQL Server. It can’t be restricted, but it can be disabled.

We recommend disabling SQL server authentication and using Windows authentication only. This means that the SA account will be present but disabled and will use a randomly created complex password.

Limiting SQL Server Guest Users

Just like many other applications, SQL Server also allows people to connect as a guest user. Guest user permissions are applied to all users that have access to the database with having their own account.

This account can’t be removed, but it can be disabled by revoking its CONNECT permission.

NOTE: You can use the T-SQL command “REVOKE CONNECT FROM GUEST;” provided the database is not master or tempdb.

Protection Against Side-channel Attacks

IT systems require physical components to operate, and this leads to the generation of all kinds of ‘footprints’, such as timing, images, sound, etc.

Side-channel attacks exploit these ‘footprints’ to obtain sensitive information using an algorithm that analyses the data output patterns of a computer or system.

The following are ways that you can minimise the risk of a side-channel attack on your SQL Server system:

• Keep your system as up to date as possible (we’ve already said this, but we can’t stress it enough).
• Don’t forget the most recent firmware updates for any local hardware.
• If you are working with a public cloud, add additional protection against side-channel attacks with isolated virtual machines, dedicated hosts or Confidential Compute virtual machines.
• In the case of the private cloud, there are options like Microsoft Hyper-V shielded virtual machines.

Protection Against SQL Injection

SQL injection is a web service security vulnerability whereby an attacker can interfere with a query that an applications makes to a database.

Attackers normally use this vulnerability to view data that they normally wouldn’t be able to retrieve, including data belonging to other users or any other information that the application can access.

In more serious cases, the attacker will be able to even modify or delete data contained in the database, causing persistent changes to the application’s content and behaviour.

The following are some steps that you can take to minimise the risk of SQL injection:

• Construct dynamically generated SQL statements in a parameterised manner.
• Both security administrators and developers should review all code that calls EXECUTE, EXEC or “sp_executesql”.
• Review processes that construct SQL statements.
• Always validate user inputs.
• Scrub error outputs from being spilled.

Furthermore, you should disallow the following input characters:

• “;” Query delimiter.
• “’” Character data string delimiter.
• “—“ Single-line comment delimiter.
• “/ * … * /” Comment delimiters.
• “xp_” Catalogue-extended stored procedures, such as “xp_cmdshell”.

It is not recommended to use “xp_cmdshell” on an SQL Server environment. Use “SQLCLR” instead.

Windows Authentication for Reporting Services

The operating system handles the authentication of users for Reporting Services through integrated security or the validation of user credentials.

However, this doesn’t necessarily need to be the case. Instead, it is possible to develop customised authentication within Reporting Services to support additional authentication schemes. This can be achieved through the security extension interface called “IAuthenticationExtensio2”.

To learn more about this feature, we recommend checking out this page on the Microsoft website.

System and Database Audits

Compiling regular reports as well as audits for applications and operating systems is an excellent measure to help keep your systems secure. That’s why it’s important to establish audit policies for your servers and your databases.

When creating an audit, don’t forget the tables or columns that contain sensitive data that may be subject to any security measures.

As well as creating these rules reports, it’s obviously also important to review then regularly to check that everything is OK and act if an issue is found.

To find out more information about audits, check out this page about SQL Server Audits on the Microsoft Learn site.

Secure Passwords

Something that we have always insisted on, and will continue to insist on, is the importance of using strong passwords. So, I’d like to finish this article by answering a simple question: What does Microsoft consider to be a strong password?

In reality, this will depend a little on the requirements of your domain or verification application. And given the increasing computer power dedicated to breaking passwords, we’re having to use increasingly more characters. However, in general terms, a good password should:

• Be at least 8 characters long
• Not include the name of an organisation, user or person
• Not include any word currently in the dictionary
• Not be similar to previous passwords
• Contain uppercase letters
• Contain lowercase letters
• Contain numbers
• Contain special characters

Conclusion

In this article, we have taken a look at some of the best practices for keeping Microsoft SQL Server 2022 as secure as possible. All of the advice found here is also applicable for other versions of SQL server that are currently supported, whether on-premises or cloud-based.

We’ve covered physical security, permissions, masking, passwords and many other steps that you can take to make your systems more resilient.

All of these measures will help improve security. But the best thing you can do, as we always say, is plan carefully when deploying a service. This will help to eliminate the majority of vulnerabilities or errors that can occur.

Obviously, this article has to end somewhere (thankfully), and we wouldn’t want to bore you. That’s why we’ve left out some other interesting topics, such as the use of Transact-SQL Cryptographic Functions. But maybe we’ll tackle these in a future article.

We hope that this article helps you to improve your database security. But remember, these are just a few tips. We definitely recommend doing your own research to see if there’s anything else you can do to make your SQL server more secure.

Thanks for reading!