Rebecca Woof

3 September 2019

• Redgate Monitor
• Monitoring Large Estates

PowerShell Alert API and Server-level Permissions Overview in SQL Monitor

The ease with which new cloud-based, containerized or virtual machine-based SQL Servers can be provisioned means that estates are growing quickly, and across diverse platforms. This presents some challenges. For example, when working with our customers, we hear frequently that, as their estates grow, it becomes harder and more time-consuming to control and refine the required alerting strategy for each server, or group of servers, across the whole estate. Also, with stricter regulations around compliance, they need easier ways to review and audit who is accessing which servers, and with which privilege levels.

SQL Monitor 9.1 introduces two new features, designed to help address each of these challenges. It offers a new PowerShell Alert API to control and fine tune your alert configuration, across a group of servers, and a new Server Permissions Overview table, which summarizes who has access to a server via a “super-privileged” server-level role.

A PowerShell API for Alerting

The SQL Monitor PowerShell API allows you to write PowerShell scripts to configure alerts. The intent is to make it much easier to fine-tune an alerting strategy for a large set of servers, such as all Azure-based SQL Servers, or all servers in a cluster, of all instances in a SQL Monitor Group.

In SQL Monitor, you define and refine your alerting strategy using the GUI. Out of the box, each type of alert is pre-configured with sensible, default threshold settings that apply to all servers. However, for each server or group of servers, you can then adjust the alerting strategy, to enable or disable specific types of alert, to tune their threshold settings, and to specify who receives any alert notifications and how. When there is deviation from the alerting strategy inherited from “All Servers”, you can add comments explaining why this was necessary.

In SQL Monitor 9.1, you can now script much of this through the new PowerShell API. You can use PowerShell cmdlets to enable or disable an alert, or revert its settings to use inherited values. This works for any type of alert, and at any level (Machine, SQL Instance, Cluster, Availability Group, Azure SQL Server, Azure SQL Database, Job and Azure Elastic Pool level). You can also use the API to configure the alert notification settings and comments. You cannot, at present, script the alert threshold settings, but the development team are working on this and it is coming soon.

So, for example, you can script out which types of alert should be enabled or disabled for any server in a cluster, and who should be notified of each type of alert, and where to send alerts for each severity level. You can quickly replicate these settings across the estate. When you register a new server, you can use scripts that pre-define its alerting configuration, so the same alerting strategy will be automatically applied.

To help get you started, example scripts have been created and made available within the tool:

Figure 1: Example PowerShell Scripts for alert configuration

To start writing your own PowerShell scripts, you can follow the instructions here. You can generate Authentication tokens within SQL Monitor, which will be used to grant access to your PowerShell session.

Server-level Permissions Overview

Any security design for SQL Server that seeks to be compliant with current regulations regarding access to personal and sensitive data, ought to implement the principle of “least-privilege” access. This means that any person or process with access to SQL Server can perform only the tasks required, and no more. Administrators should maintain a security document listing the required tasks for each Principal against the minimal permissions required to accomplish each task. They can then implement this strategy using fine-grained access control. Critically, this will include strict control, documentation and monitoring of who has access to each server via any of the highly privileged server-level roles.

SQL Monitor now provides a permissions table for each server, summarizing which Principals have access to that server via one of the three “super-privileged” server-level roles (sysadmin, serveradmin and securityadmin). This is currently a unique feature in third party monitoring tools.

For each role that is populated for a server, you get a count of the Windows Logins, Active Directory Accounts and SQL logins that are members, and a SQL Monitor administrator will also be able to drill down to see when the user last logged in.

The following example is taken from the Overview screen of one of the ssc-test01.* servers on https://monitor.red-gate.com/:

Figure 2: New Server-level Permissions table

Tell us what you think

We are always looking for feedback to improve our tools, so if you have any thoughts or suggestions on the new features, please do get in touch on our SQL Monitor UserVoice page.

You can check out these features by visiting our live online demo or simply upgrade to 9.1 by clicking on the bar at the top of the screen within SQL Monitor.