This section guides you through setting up, configuring, and managing the MS SQL integration.
Prerequisites
On Gateway
- OpsRamp Classic Gateway (Linux) version 15.0.0 or later.
- OpsRamp NextGen Gateway version 15.0.0 or later.
Note
We recommend using the latest Gateway version for full coverage of (Recent bug fixes, enhancements, etc.) - Ensure the cluster or standalone node, all applicable nodes, and the SQL Server instance port are accessible from the Gateway.
- Open TCP port 22 for PowerShell over SSH.
Configure OpenSSH
OpenSSH is the only supported connection method for Windows Failover Cluster monitoring. Run the following commands in an elevated Windows PowerShell session.
Pre-checks
Check whether OpenSSH Server is installed:
Get-WindowsCapability -Online -Name OpenSSH.Server*
Check the SSH service:
Get-Service sshd
Install OpenSSH Server
Install OpenSSH Server if it is not already installed:
Add-WindowsCapability -Online -Name OpenSSH.Server~~~~0.0.1.0
Start the SSH service
Start-Service sshd
Set-Service sshd -StartupType Automatic
Open TCP port 22
New-NetFirewallRule `
-Name "Allow-SSH-Port-22" `
-DisplayName "Allow SSH Port 22" `
-Protocol TCP `
-Direction Inbound `
-LocalPort 22 `
-Action Allow `
-Profile Domain,Private,Public
Configure SSH access
Open the SSH configuration file as an administrator:
notepad C:\ProgramData\ssh\sshd_config
Configure password authentication and permit the monitoring user:
PasswordAuthentication yes
AllowUsers <ssh-username>
Set Windows PowerShell as the default SSH shell:
Set-ItemProperty `
-Path "HKLM:\SOFTWARE\OpenSSH" `
-Name DefaultShell `
-Value "$PSHOME\powershell.exe"
Restart the SSH service:
Restart-Service sshd
Confirm that the service is running:
Get-Service sshd
Validate SSH connectivity
From the OpsRamp Gateway, run:
ping <IP-address-or-hostname>
telnet <IP-address-or-hostname> 22
ssh <username>@<IP-address-or-hostname>
After connecting, verify the active shell:
$PSVersionTable.PSVersion
The SSH connection must succeed, and PowerShell must be configured as the default shell.
Required Account Privileges
The following permissions are required regardless of the selected connection option:
Configure the non-administrator user on the target cluster and all applicable node
Note
Execute all commands in an elevated PowerShell session (Run as Administrator).1. Add User to the Performance Monitor Users Group
The user must be a member of the Performance Monitor Users group to collect performance counter metrics.
net localgroup "Performance Monitor Users" /add "<domain\user>"
2. Grant Remote WMI Read-Only Permissions
To grant remote WMI read-only access, perform the following steps on each monitored system:
- Press Windows Key + R, type wmimgmt.msc, and press Enter.
- In the left pane, right-click WMI Control (Local) and select Properties.
- Navigate to the Security tab.
- Expand Root and select CIMV2.
- Click Security.
- Click Add, enter the non-administrator user account, and click OK.
- Select the user and grant only the following permissions:
- Enable Account (Allows local read access)
- Remote Enable (Allows remote read access)
- Execute Methods
- Read Security
- Click Advanced.
- Select the user and click Edit. Set Applies to as This namespace and subnamespaces.
- Click OK, then Apply, and close all WMI management windows.
“Execute Methods” allows method execution, so the permissions aren’t strictly read-only if that permission is granted. Enable Account and Remote Enable are the core permissions for remote WMI access; follow the product’s requirements for any additional permissions.
3. Grant SQL authorization permissions
- SQL DB instance should accept connections on the DB port. Ensure the firewall is allowing traffic over the DB instance port.
- OpsRamp supports mixed authentication (both NTLM and SQL Server authentication).
- To monitor DB metrics, you should have the following minimum privileges:
- Server Role: public - This server role is available by default
- Securables: Enable the following securables for connecting to SQL instances and underlying databases, along with required read-only permissions:
CONNECT ANY DATABASE
GRANT CONNECT ANY DATABASE TO <domain\user>
CONNECT SQL
GRANT CONNECT SQL TO <domain\user>
SELECT ALL USER SECURABLES
GRANT SELECT ALL USER SECURABLES TO <domain\user>
VIEW ANY DATABASE
GRANT VIEW ANY DATABASE TO <domain\user>
VIEW ANY DEFINITION
GRANT VIEW ANY DEFINITION TO <domain\user>
VIEW SERVER STATE
GRANT VIEW SERVER STATE TO <domain\user>
Create the login on every monitored SQL instance (For NTLM/domain authentication)
USE master;
IF SUSER_ID('<domain\user>') IS NULL
CREATE LOGIN [<domain\user>] FROM WINDOWS;
Grant VIEW SERVER PERFORMANCE STATE for SQL Server 2022+ versions only
GRANT VIEW SERVER PERFORMANCE STATE TO <domain\user>;
Explicit Backup-History Access
USE msdb;
IF USER_ID('<user>') IS NULL
CREATE USER [<domain\user>] FOR LOGIN [<domain\user>];
4. Grant Read-Only Failover Cluster Access
The domain user must be granted read-only access to the failover cluster.
Grant-ClusterAccess -User "<domain\user>" -ReadOnly
For monitoring services like Windows Cluster Service, Windows domain users must be a part of the Local Administrators group as the integration uses win32_Service class for fetching the details. Refer to Get-CimInstance.
If the non-administrator monitoring account cannot retrieve Windows service information because of insufficient permissions, follow these steps to grant read/query access without adding the account to the local Administrators group.
Note
Skip these steps if service monitoring already works with the existing account configuration.Refer to Enable Windows Service Monitoring Permissions
SQL Server Agent job monitoring permissions
To populate the SQL Server Agent job status metrics, grant the database user configured for monitoring permission to read the SQL Server Agent job and job history tables in the msdb database:
USE msdb;
GRANT SELECT ON dbo.sysjobs TO [<domain\user>];
GRANT SELECT ON dbo.sysjobhistory TO [<domain\user>];
Summary of Required Permissions
If the user is a non-administrator, they must have the following privileges:
- Membership in the Performance Monitor Users group.
- Remote WMI read-only access to the
Root\CIMV2namespace and subnamespaces. - Grant SQL authorization permissions.
- Read-only access to the failover cluster.
- Please ensure that all privileges are enabled before installing the integration.
Configure and Install the MS SQL Cluster Integration
- From All Clients, select a client.
- Go to Setup > Account.
- Select the Integrations and Apps tab.
- The INSTALLED INTEGRATIONS page, where all the installed applications are displayed.
Note: If there are no installed applications, it will navigate to the Available Integrations and Apps page. - Click + ADD on the INSTALLED INTEGRATIONS page. The AVAILABLE INTEGRATIONS AND APPS page displays all the available applications along with the newly created application with the version.
Note: You can even search for the application using the search option available. Also you can use the All Categories option to search. - Click ADD in the MS SQL Cluster application.
- In the Configurations page, click + ADD. The Add Configuration page appears.
- Enter the following BASIC INFORMATION:
| Field Name | Field Type | Field Dependency | Description |
|---|---|---|---|
| Name | String | NA | Enter the name for the integration. |
| MSSQL Type* | Dropdown | N/A | Select the type of Microsoft SQL Server instance you are integrating. The options availble are:
|
| IpAddress/HostName* | Text | N/A | If the MSSQL Type is set to CLUSTER, enter the IP address or hostname of the Windows cluster. If set to STANDALONE, enter the IP address or hostname of the individual node instead. |
| MSSQL Cluster Type | Dropdown | Appears only when Cluster is selected in the MSSQL Type field. | Select the type of SQL Server cluster configuration being integrated. The options available are:
|
| MSSQL Cluster / Node Credentials* | Dropdown | N/A | Provide the Windows authentication credentials required to connect to the Windows cluster hosting the SQL Server cluster, or to the individual nodes in the case of a standalone SQL Server deployment. You can either select from the dropdown if the credentials are existing. Else click +ADD to create credentials. In the ADD CREDENTIAL window enter the following:
|
| SQL Server Authentication Mode | Checkbox | N/A | Enable this option to provide SQL Server credentials if the specified Windows credentials do not have sufficient SQL Server access rights.
|
| SQL Server Credentials | Dropdown | This field is displayed only when the SQL Server Authentication Mode checkbox is selected. | Select the SQL Server credentials from the dropdown. You can either select from the dropdown if the credentials are existing. Else click +ADD to create credentials. In the ADD CREDENTIAL window enter the following:
|
| Connection Mode | Dropdown | N/A | Open SSH is the only supported option. |
| App Failure Notifications | Checkbox | N/A | This checkbox is used to confirm whether a notification should be sent in the event of an app failure due to authentication or connection-related exceptions.
|
| SSH Timeouts | Checkbox | N/A | Select the checkbox to configure timeout settings for OpenSSH connections and script execution requests made by the integration to the end device. |
| Connection TimeOut In Mins | Dropdown | N/A | Select the maximum time, in minutes, that the integration should wait while establishing an OpenSSH connection. Default value: 1 minute. |
| Script Execution TimeOut In Mins | Dropdown | N/A | Select the maximum time, in minutes, that the integration should wait for script execution to complete. Default value: 2 minutes. |
- Select the below mentioned Custom Attribute:
| Field Name | Field Type | Field Dependency | Description |
|---|---|---|---|
| Custom Attribute | Dropdown | N/A | Select the custom attribute from the dropdown. |
| Value | Dropdown | N/A | Select the value from the dropdown. |
Note: The custom attribute that you add here will be assigned to all the resources that are created by the integration. You can add a maximum of five custom attributes (key and value pair).
- In the Native Types section, select the native types to be discovered.
- In the DISCOVERY SCHEDULE section, select Recurrence Pattern to add one of the following patterns:
- Minutes
- Hourly
- Daily
- Weekly
- Monthly
- Click ADD.
Note
Sample values shown in the configuration fields are for illustration purposes only. Use your actual integration details when configuring.The configuration is saved and displayed on the configurations page after you save it.
Note: From the same page, you may Edit and Remove the created configuration.
- Click Next.
- Below are the optional steps you can perform on the Installation page.
Under the ADVANCED SETTINGS, Select the Bypass Resource Reconciliation option, if you wish to bypass resource reconciliation when encountering the same resources discovered by multiple applications.
Note: If two different applications provide identical discovery attributes, two separate resources will be generated with those respective attributes from the individual discoveries.

- Click +ADD to create a new collector by providing a name or use the pre-populated name.
- Select an existing registered profile.

- Click FINISH.
The application is now installed and displayed on the INSTALLED INTEGRATION page. Use the search field to find the installed application.
Modify the Configuration
See Modify an Installed Integration or Application article.
Note: Select the MS SQL Cluster application.
View the MS SQL Cluster Details
- Navigate to Infrastructure > Search > DATABASES > MS SQL Cluster.
- Select the application on the MS SQL Cluster page.
- The RESOURCE page appears from the right.
- Click the ellipsis (…) on the top right and select View Details.

View resource metrics
To confirm MS SQL Cluster monitoring, review the following:
- Metric graphs: A graph is plotted for each metric that is enabled in the configuration.
- Alerts: Alerts are generated for metrics that are configured as defined for integration.
