Denny Cherry

20 December 2024

• Redgate Monitor
• Security and compliance
• Performance monitoring and optimization

Database Monitoring and Security Go Hand in Hand

Security and performance monitoring through a single pane of glass

Tracking database performance and security data side-by-side reveals variations in query performance, resource usage and user activity right alongside any suspicious permission changes or unauthorized configuration drift.

You will often see correlations that can lead to faster diagnosis and mitigation of risk. For example, a sudden spike in activity on sensitive data tables might coincide with an unauthorized permission escalation. Alternatively, a sudden increase in query execution times on a SQL Server instance might correlate with configuration drift for the max degree of parallelism (MAXDOP) setting.

This article will explore the features of Redgate Monitor Enterprise that will allow DBAs to:

• Ensure security and compliance with Microsoft SQL Server configuration monitoring
• Streamline the compliance and audit process with standardized templates that can be easily exported and shared
• Monitor user roles and permissions to ensure compliance and security
• Ensure high availability configuration so that Redgate Monitor is always running
• Utilize extensibility options to connect your monitoring data with other applications

Monitoring of Microsoft SQL Server configuration

Redgate Monitor Enterprise’s configuration compliance feature provides DBAs with a comprehensive view of key instance and database settings, automatically tracking configurations that impact both security and performance.

For example, the company’s configuration policies might include disabling xp_cmdshell by default, while an attacker would turn it on so they can download and install software from the command line. Catching unauthorized changes to this and other security settings might be your first indication that an attack is happening. Similarly, spotting quickly that auto_update_statistics has been disabled on a busy database might help you avoid query performance issues before they occur.

Without a monitoring solution, it’s nearly impossible to know what has been changed or why, whether by a system administrator or a threat actor. By automatically tracking both performance and security configurations, Redgate Monitor can help ensure a secure, standardized environment while maintaining optimal database performance.

Compliance and auditing templates

Going through an audit can be daunting for any IT team. By centrally managing database security monitoring, Redgate Monitor Enterprise makes auditing reviews easier, quicker and simpler to complete, because the information your auditor needs is in one place.

DBAs can audit current configuration settings against predefined compliance templates that reflect organizational standards or the requirements of a particular set of industry regulations, or a ‘gold standard’ benchmark such as the CIS (Center for Internet Security) Benchmark for SQL Server 2022.

These templates will allow DBAs to identify and investigate configuration deviations speedily. They will also verify for an auditor that all instances and databases are configured in compliance with relevant standards and regulations. If needed, you can adapt or create your own compliance template which has the workload settings which are important to your organization and to your auditor.

When audit season comes around DBAs can simply export the report that demonstrates compliance with the regulatory template and give that report to the auditor, along with the definition of the template. If additional requirements arise, they can extend the template to capture the new metrics, and Redgate Monitor will gather and analyze the additional data automatically.

Monitoring SQL Server user roles and permissions

The volume of access requests on a database server will fluctuate depending on user behavior, server workload schedules, and many other factors. However, the security footprint of a server, such as role memberships and permission assignments, should remain stable unless changes are explicitly authorized by a security administrator.

Redgate Monitor Enterprise’s permissions features enable DBAs to track, by server, database, or user, exactly who has access to what. It monitors membership of powerful fixed server roles, Windows logins and groups, as well as the granular permissions assigned to individual database users. You can also view, filter and export the status and history of the user permissions on the system.

Changes in the membership of a fixed server role, for example, can indicate unauthorized activity. In the 2024 cyber-attack against Microsoft by Midnight Blizzard, attackers created a Windows account to infiltrate the corporate environment and access target devices. By automatically tracking changes to fixed server role memberships, Redgate Monitor can provide an early warning of such threats. Similarly, by closely monitoring Windows login and group permissions, DBAs can quickly identify sudden increases in the population of a Windows group that holds elevated privileges, through role membership.

What distinguishes Redgate Monitor Enterprise is its ability to track permissions data alongside performance and activity metrics. This combination establishes baselines for server and user activity, helping DBAs identify anomalies. For instance, Redgate Monitor can establish baselines for metrics relating to query performance (execution times, waits), resource usage (CPU, IO, memory, waits), and workload volumes (user connections, batch requests per second, locking and latching statistics, etc.). Using these baselines, DBAs can compare current behavior to historical patterns. If a DBA observes a suspicious “out of hours” activity spike on tables containing sensitive or controlled data, for example, Redgate Monitor enables immediate investigation. The DBA can review activity patterns, correlate them with any recent permission changes, and initiate a thorough audit to mitigate potential risks.

High availability monitoring

We all understand the importance of ensuring that our database instances are always running and available. High availability features for Microsoft SQL Server ensure they remain accessible even if a service goes offline. An issue that often gets less consideration is the availability of your monitoring solution. If the servers that are running the monitoring solution fail, there will be a period where metrics are not being tracked, so you risk missing performance bottlenecks or unauthorized access attempts.

To address this, Redgate Monitor Enterprise supports native high availability for both its core monitoring service (data collection) and its web service (data visualization). You can configure active/passive base monitoring services, or active/passive base monitor-web service pairs, with automatic failover if any one of these components fails.

Only a single base monitoring service at a time writes its results to the monitoring repository and if its ‘heartbeat’ stops then a passive standby immediately takes over and an alert is raised for the failover event. By mirroring the principles of SQL Server’s HA features, Redgate Monitor Enterprise provides resilient monitoring that ensures continuous surveillance of the availability, security and performance of your Microsoft SQL Server environments.

Integrations with Redgate Monitor Enterprise

One standout feature of Redgate Monitor Enterprise is its extensibility. Many monitoring tools restrict how you can use the data they collect, allowing access only through their standard dashboards and a set of pre-defined reports. Redgate Monitor Enterprise extends this access by making its monitoring data available through a set of APIs, so that applications can use the data within the monitoring repository. Third-party applications or custom-built in-house solutions can consume the data, analyzing and presenting it in ways that best suit the requirements of the various teams within your organization.

You can, for example, build custom dashboards for management oversight of key performance and security metrics. Or you could integrate monitoring data into incident management and ticketing systems, automatically linking database activity, security and performance metrics to incident reports. The integration possibilities are limited only by the solutions you want to integrate with.

The whole solution

With Redgate Monitor Enterprise, you have a highly available and integrated monitoring solution that can centrally track security, performance and health metrics for your whole database estate, and present all the data in a single dashboard.

You can manage user roles and permissions and retain oversight of who has access to what. You can track database and instance configuration settings, ensuring compliance with pre-defined or custom auditing templates, which you can export for review by auditors. Native High Availability monitoring will keep the monitoring solution up and running, even in the event of a hardware failure of the server hosting the monitoring solution. With the extensibility options available through the API, your company can gather data from the monitored environment and use it where and how it needs it.

With Redgate Monitor, organizations can ensure continuous performance and security monitoring through a unified approach. This not only helps in identifying and mitigating potential risks but also streamlines compliance and auditing processes, with a solution that enhances both the stability and security of your database estate.