Microsoft SQL Server is a popular relational database management system created and maintained by Microsoft. It’s effective in numerous use cases: storage and retrieval of data as part of a DBMS, transaction processing and analytics applications.
However, there are some essential measures you must take to protect your database from cybercriminals and security breaches, as the default security settings are relatively insufficient to keep your database safe.
This article guides you through the process and steps of using standard practices to secure your Microsoft SQL Server on the network, transport and database levels.
The primary function of Microsoft SQL Server, like any other database management system, is storing and retrieving data, and it’s essential that you have proper security measures in place to keep that data protected.
The following are just some of the reasons why securing your Microsoft SQL Server is important:
As you can see from these examples, and further reinforced by various other potential SQL server security threats, having proper security standards for your database is critical. With that in mind, the rest of this article focuses on practical ways you can secure your SQL Server on three levels: network, transport, and database.
Network security ensures that only trusted nodes can access the database by restricting any access from unauthorized users. This section will show you some methods to set up network security for your SQL Server.
Network security can be improved through the use of firewalls that control and monitor network traffic. The following sections explain how you can set up the Windows Firewall—using
netsh, PowerShell, and the Microsoft Management Console (MMC) — along with the Azure Service Firewall and the Azure Database Firewall.
For restricted access to the SQL Server, you need to enable the firewall on the device the server is running on. However, note that this can only be set up for SQL Server Database Engine and not the Azure SQL database.
You can set up a firewall on Windows using
netsh, PowerShell, and MMC.
netsh is an administrator tool capable of controlling and monitoring devices running on Windows using a command prompt. To set up a firewall using
netsh, follow the steps below:
You can also enable the Windows Firewall using PowerShell. The steps are as follows:
Lastly, the Windows Firewall can be set up using the Microsoft Management Console (MMC). The steps are as follows:
You can find more details about configuring the Windows Firewall.
You can configure the Azure Database Firewall for Azure SQL using
sp_set_database_firewall_rule. It allows only the IP addresses within the specified range to be able to connect to the database.
This procedure doesn’t work for SQL Server Database Engine. However, the steps for setting it up for Azure SQL Database are as follows:
Configuring the Azure Service Firewall enables server-level firewall settings for the Azure SQL database using the
sp_set_firewall_rule. The steps are as follows:
Again, if you would like more information about this rule, the official documentation on the Azure SQL Database is a good source.
Transport security ensures the secure and protected transfer of data from one node to the other. This section will show you some standard practices for ensuring appropriate transport security for your SQL Server.
You can use TLS/SSL cryptographic protocols for transport security in your SQL Server. The instructions below demonstrate how to set up encrypted connections to the Database Engine. Note that the forced SSL connections can be used in both the SQL Server and Azure SQL, whereas the optional SSL connections can only be used in the SQL Server Database Engine.
An instance of the Server Database Engine and SQL Server Configuration Manager is used to set up the specification of the cryptographic protocol certificate. After downloading the certificate from a trusted Certificate Authority, follow the steps below for its installation (note that these steps are for the installation on a single server):
You can learn more about installing a certificate across multiple servers.
Database security ensures that the right users can access the database with the correct credentials and perform authorized tasks. In this section, you’ll learn how to achieve an appropriate level of database security through authentication, authorization,
EXECUTE AS statements, auditing and encryption.
Authentication is the process of checking who the user is and if they have the right credentials and permissions to access the database.
There are two authentication methods you can choose from: Windows Authentication or Mixed Mode Authentication (combining SQL Server Authentication and Windows Authentication). These should be chosen at the time of setup.
Windows Authentication is applicable only for the Windows database engine, whereas the SQL Authentication (as used with Windows Authentication in Mixed Mode Authentication) is applicable for Windows Database Engine and the Azure SQL.
Follow the steps below to determine the current password policy setup on the device and to enable authentication methods:
Similarly, you can change the remaining policies. Learn more about Authentication Mode.
You can also look into the setting up Azure Active Directory for Authentication purposes.
You can utilize the
EXECUTE AS statement to enable the permissions of a specified user, instead of the one calling the statement. It limits the permissions of the user for that specific session.
For example, if you have admin privileges but are calling this command for a test account, then the context will be switched to the permissions of that test account for the particular session instead of the admin privileges.
The following is the command for it:
You can authorize distinct permissions to the user based on requirements, which decide what a user can do with the data:
Auditing defines the process of recording the access and action details of the user. You can set up server- and database- level audits. To implement a server audit using Transact-SQL, follow the steps below:
There are also other methods of auditing that you can try, including creating a server audit using SQL Server Management Studio.
It’s always a best practice to keep the data encrypted to protect it from potential security risks. The data can be encrypted at the drive, folder, and file level.
This is also known as transparent data encryption (TDE), which is the process of real-time encryption of sensitive data using an encryption key:
You can utilize the commands and functions of encryption at rest as per your requirements.
You can also set up a backup encryption using PowerShell:
If using this method, there are some recommended practices associated with backup encryption that you should also familiarize yourself with.
There are numerous practices that we covered in the article for the security of your Microsoft SQL server. In addition to Firewall, TLS/SSL, authentication, authorization, auditing, and encryption, you can setup row-level security and temporal tables for enhanced security.
You have seen several methods and standards of securing your Microsoft SQL Server that can be very effective. However, implementing these methods and keeping them up to date requires a significant amount of effort and attention. This process can be greatly simplified and smoothened through the use of security software that takes care of these issues for you.
For example, Remoteler is a platform that brings together numerous security standards including connectivity, authentication, authorization and auditing in one place.
Remoteler supports multiple databases, including MySQL, PostgreSQL, MongoDB, Redis, CockroachDB, MariaDB and Microsoft SQL.
Microsoft SQL Server is a popular database with several use cases. However, the default security of the database can leave your data vulnerable. This is why you should incorporate some standard security practices in your database.
This article discussed how you can secure your Microsoft SQL Server on three levels: network, transport and database. Respectively, properly implemented and configured firewalls provide network security; incorporating cryptographic SSL/TLS ensures the best transport security; finally, the appropriate use of authentication, authorization, auditing and encryption support strong database security.
Though all these methods are effective, implementing and monitoring them all manually can be challenging. An open-source multi-protocol access plane like Remoteler simplifies the process of securing your database by providing a single platform that handles it all for you.