SQL database server like other database servers is a key asset in most organizations. Many data breaches that occur today can be traced to poorly secured database servers
Without the right security measures, a database can easily serve as a gateway for cyber attacks. A single act of negligence can compromise the safety and security of your organization’s SQL database. Attackers can inject malicious codes, and compromise the integrity, confidentiality, and availability of the database.
Implementing the right security measures will go a long way in protecting your database servers and minimizing your exposure to business and security risks. This article outlines some essential SQL Server security basics and best practices, including some of the top tools for managing SQL Server security and performance.
Information security cannot be completely achieved without proper physical security. Physical security should be the first line of defense and must include the prevention of unauthorized access to the facility where the SQL Server is hosted. Physical security deals with facility and personnel access controls, external boundary protection mechanisms, intrusion detection, and corrective actions. It protects your assets from intruders, theft, environmental threats, physical damage, among others.
Physical security should be implemented by using a layered defense approach. The idea is that if an attacker breaks through one control layer, there will be many others in the way before he or she can gain full access. Any breach in the physical security of your database server could result in loss of productivity, reputation, and customer confidence.
To enhance the physical security of the SQL Server, Microsoft recommends you do the following:
- Place the server in a room accessible only to authorized persons.
- Place computers that host a database in a physically protected location, ideally a locked computer room with monitored flood detection and fire detection or suppression systems.
- Install databases in the secure zone of the corporate intranet and do not connect your SQL Servers directly to the Internet.
- Back up all data regularly and secure the backups in an off-site location.
Network security is concerned with the protection of the underlying networking infrastructure of your database server from unauthorized access. It combines multiple layers of defenses that implement policies and controls at the edge and in the network. Authorized users gain access to the database via the network, while unauthorized users are denied access. A network infrastructure designed with security in mind around your database server safeguards the database from losses that occur from network security incidents.
Implementing network security starts with keeping unauthorized users off the network. This is where network firewalls come into play. Firewalls play an important role in securing the SQL Server application. By putting your database behind a firewall, you create another layer of security preventing malicious traffic from reaching your server. Firewalls can also help prevent malicious outbound traffic such as data exfiltration.
Firewalls will be most effective if you adhere to the following recommended guidelines from Microsoft:
- Put a firewall between the server and the internet. Enable your firewall. If your firewall is turned off, turn it on. If your firewall is turned on, do not turn it off.
- Divide the network into security zones separated by firewalls. Block all traffic, and then selectively admit only what is required.
- In a multi-tier environment, use multiple firewalls to create screened subnets.
- When you are installing the server inside a Windows domain, configure interior firewalls to allow Windows Authentication.
- If your application uses distributed transactions, you might have to configure the firewall to allow Microsoft Distributed Transaction Coordinator (MS DTC) traffic to flow between separate MS DTC instances. You will also have to configure the firewall to allow traffic to flow between the MS DTC and resource managers such as SQL Server.
- Disable all unnecessary ports and protocols including NetBIOS and Server Message Block on your SQL Server, Web servers, and Domain Name System (DNS) to reduce the attack surface. Allow only protocols and ports that are essential to the functioning of the SQL Server such as TCP 1433 (database engine), 2383 (analysis services), 4022 (service broker), and UDP 1343 (browser access).
Application and Data Security
Microsoft SQL Server provides several built-in features that enable the security of the application and the data contained within it. The database administrator can leverage these features, or use additional security measures as needed, to address the security needs of the SQL application and data.
Some of the additional security measures may among other things include:
- Protection against SQL and other Injection attacks Just as the name suggests, SQL injection is an attack in which malicious code or commands is injected into query strings. These commands can damage or corrupt the database or be used to exfiltrate sensitive data. You can thwart SQL injection attacks by sanitizing all database inputs to ensure they do not contain characters that can be used to execute code. It is best practice to use stored procedures and parameterized commands while avoiding dynamic SQL, and restricting permissions on all users. You also need to guard against connection string injection attacks that occur during SQL Server login. If an SQL Server login string is not checked for valid keyword pairs, an attacker can add extra characters that can perform unauthorized actions on the server. You can mitigate this risk by using the SqlConnectionStringBuilder to create and validate connection strings at run time, or use the more secure Windows authentication wherever possible.
- Verbose errors exploitation SQL Server generates all kinds of logs such as error logs, system event logs, performance counter data, among others. You need to find a way to make sure these errors don’t fall into the wrong hands. Otherwise, attackers may use it to identify inherent vulnerabilities that they may want to exploit. Ensure that all procedural code uses error handling, to prevent these default SQL Server error messages from reaching the user.
- Privilege escalation attacks These attacks occur when users somehow assume the privileges of a trusted account, such as an account owner or an administrator. This enables them to gain unauthorized access to data and database functions. To mitigate this risk, always run users as least-privileged accounts, assign only needed permissions, and block the ability to execute code from administrative or owner accounts. This limits the amount of damage that can occur if an attack succeeds. If a user requires extra permissions to perform a task, use certificates to sign stored procedures or impersonation only for the duration of the task.
- Distributed denial of service (DDoS) attacks Production SQL Server databases may be subject to DDoS attacks, in which attackers flood the database with fake queries, slowing performance for legitimate users, and eventually resulting in downtime. If you run SQL Server in the cloud, you can leverage DDoS protection services, which can capture and divert malicious traffic away from your database.
SQL Server Security Best Practices
The following are some key SQL Server security best practices that can enhance the security of your SQL database:
- Backup regularly The need for regular backup of your database cannot be overemphasized. Backups protect the availability of data and ensure that your database remains resilient in the face of data breaches, ransomware, or denial of service attacks. Consider investing in a good SQL Server backup solution as part of your security strategy. This provides you with a lifeline to restore mission-critical data in the event of an attack.
- Regular Security Audits Security audits are a regulatory requirement for most industries. But beyond that, SQL Server security audits help prevent potential attacks and in maintaining a healthy SQL database. Efforts should be geared towards identifying missing database objects, repeated server login failures, and permissions or configuration changes. Microsoft provides a built-in tool called SQL Server Audit which can be used to create server-level and database-level audits.
- Follow the Principle of Least Privilege This is a tried and tested principle that helps to lower your asset exposure to risk. The plan is to grant SQL Server services and users only the appropriate rights required to perform a task and no more. For service accounts, run and associate SQL Server services using the lowest possible permissions and privileges.
- Have a Strong Password Policy Setting a strong password or preferably a passphrase that is hard to guess is essential to the security of your database. Consider using Kerberos authentication, Windows Authentication for connections to SQL Server, and a password manager for better password management.
- Apply updates regularly The importance of regular updates cannot be overemphasized. The OS and SQL Server updates contain important security enhancements that maintain the health and security of your database. Test these updates and apply them to your production server as soon as possible.
- Use Encryption The built-in SSL/TLS and Windows Data Protection API (DPAPI) encryption protocol enable encryption of data in transit and data at rest respectively. This protects the confidentiality of your database.
- Use SQL Monitoring Tools SQL monitoring tools provide an automated means of managing the security and performance of your database server. The tool should be capable of scanning and detecting changes to server settings.
The Top Tools for SQL Server Security
Several tools on the market can automate the management of the security and performance of your SQL Server. Below is a review of some of the top tools for managing SQL Server security and performance. Hopefully, this will guide you in the process of choosing the right solution for your business.
SolarWinds Security Event Manager (SEM) enables database administrators to monitor server logs, track database error rates, audit SQL user logins, maintain SQL Server security, and detect cyber attacks. With SEM, you can track, report, and alert on specific events done against a SQL database that may signal potential threats, and generate SQL Server audit log reports for compliance purposes. SEM provides out-of-the-box event correlation rules that study database transactions, detect known threats, and track malicious and non-trusted activities.
SEM supports various security assessment protocols and applications to help you defend against SQL injection attacks and maintain a secure database. SEM leverages its pre-built SQL injection rules and alerts to keep you informed. It can detect and disable malicious users and processes, as well as perform other automated threat responses to help secure your database when SQL injection indicators are detected. A free trial is available for download, so you can determine whether it’s right for your business.
Try SolarWinds Security Event Manager (SEM) with a fully functional 30-day free trial.
Paessler PRTG is an agentless network monitoring tool that lets you monitor your network, routers, switches, and servers. With PRTG, you can measure the time an SQL query needs for its entire request, including the establishment of the connection, the execution of the query, the handling of the transaction, and the termination of the connection, all of which can be visualized on the PRTG dashboard. The PRTG licensing model is based on sensors (parameters that can be monitored on a device such as CPU load, port status, or network traffic); and comes with pre-configured sensors for all major databases. For Microsoft SQL, PRTG offers the Microsoft SQL v2 Sensor which monitors the response time of an entire request as well as the response time of an individually defined query.
PRTG comes in different editions and variants such as:
- PRTG Enterprise Monitor—targeted at large networks with thousands of devices and systems across multiple locations.
- PRTG Hosted Monitor—a cloud-based option that allows you to monitor from the cloud
- PRTG Desktop—allows you to manage multiple PRTG servers.
- PRTG Mobile App—allows you to check your network, devices, and sensors while on the go.
A free 30-day-trial with full access to all the features is available for download.
3. Microsoft Defender for SQL
Microsoft Defender for SQL is an SQL Server security tool that helps to detect and mitigate potential SQL database vulnerabilities and anomalous activities that pose a threat to your database.
Microsoft Defender for SQL comprises two separate Microsoft Defender plans:
- Microsoft Defender for Azure SQL database servers: This plan is designed to protect Azure SQL database, Azure SQL managed instance, and dedicated SQL pool in Azure synapse.
- Microsoft Defender for SQL Servers on machines: This plan extends the protections for your Azure-native SQL Servers to fully support hybrid environments and protect SQL Servers hosted in Azure and other cloud environments, and even on-premises machines: including SQL Server on virtual machines and on-premises SQL Servers:
You can enable these plans at the subscription level (from Microsoft Defender for Cloud or via the REST API, Azure CLI, PowerShell, or Azure Policy), or you can enable at the resource level. When you enable either of these plans, all supported resources that exist within the subscription (including future resources created on the same subscription) are protected.
Datadog is an agent-based on-premises and cloud infrastructure monitoring service for cloud applications, servers, databases, tools, and services. Datadog uses its auto-discovery service to explore and identify applications, devices, and servers in your network. Once all devices and links have been identified, you can overview all activity from the Datadog dashboard, and any changes to the network can be automatically spotted.
Datadog provides end-to-end visibility into the health and performance of your SQL Server instances. It provides tools and key metrics for SQL Server monitoring, including two out-of-the-box dashboards for SQL Server:
- A screenboard that gives you a real-time overview of your SQL Server instances.
- A timeboard that’s well suited for correlating SQL Server metrics with system metrics and events.
A free 14-day-trial with full access to all the features is available for download. After that, the software is generally sold through monthly subscription plans based on hosts, events, or logs
5. ManageEngine Applications Manager (APM)
APM is an agentless application and server performance monitoring solution that helps organizations ensure optimal performance of their business-critical applications such as SQL Servers across physical, virtual, and cloud environments. APM offers out-of-the-box discovery, availability, health and performance tracking, and reporting of SQL Servers and other applications.
With APM, database administrators can gain visibility into SQL applications, track resource usage, optimize the performance of SQL Servers, prevent the occurrence of errors, and maintain the overall health of the database application.
ManageEngine APM is available in the following editions:
- The Free edition Provides most of the functionality of the Professional edition with a few restrictions and supports up to 5 monitors.
- The Professional edition: It includes monitoring, alerting, and reporting features and targets SMBs looking to monitor up to 500 applications based on load.
- Enterprise edition: It includes all the Professional edition + distributed monitoring and failover capabilities and targets large enterprises looking to monitor 500 or more applications.
A 30-day free trial is available for download. During installation, you can choose either the Professional or Enterprise edition.