×

This section guides you through setting up, configuring, and managing the MS SQL integration.

Prerequisites

pre-requisites to be implemented on Gateway:

• OpsRamp Classic Gateway (Linux) 15.0.0 and above.
• OpsRamp Nextgen Gateway 15.0.0 and above.
• Upgrade to the latest Gateway version to ensure comprehensive coverage of recent bug fixes, feature enhancements, and so on.
  

Pre-requisites for Target Cluster or Standalone Nodes

To execute PowerShell cmdlets successfully, ensure the following requirement is met:

• Windows domain User should perform the following to do powershell remoting.
  Enable-PSRemoting -Force
  
• Add the Windows domain user to the Remote Management Users group:
  net localgroup "Remote Management Users" /add <user>
  
• Add the Windows domain user to the Performance Monitor Users group:
  net localgroup "Performance monitor users" /add <user>
  
• Add the OpsRamp Gateway IP to the TrustedHosts list on the target machine to allow PowerShell connections from the gateway:
  
  • To add TrustedHosts use the following command:
    
    • To allow any host:
      Set-Item WSMan:\localhost\Client\TrustedHosts -Force -Value *
      
    • To allow a specific host:
      Set-Item WSMan:\localhost\Client\TrustedHosts -Force -Concatenate -Value <OpsRampGatewayIp>
      
  • Set up and restart the WinRM service for the changes to take effect.
    
    • To set up: Set-Service WinRM -StartMode Automatic
      
    • Restart using: Restart-Service -Force WinRM
      
• Ensure that ports 5985 (HTTP) and 5986 (HTTPS) are open.

  Note

  By default, WS-Man and PowerShell remoting use ports 5985 (HTTP) and 5986 (HTTPS) for remote connections.

Enable the following permissions for non-admin/operator users:

Grant Remote WMI Rights

1. Click Start and select Run.
2. Enter wmimgmt.msc and click OK.
3. Right-click WMI Control (Local) and select Properties.
4. Go to the Security tab.
5. Select Root and click Security.
6. Add the user and grant these permissions:
   • Execute methods
   • Enable account
   • Enable remoting
   • Read security
7. Click Advanced, select the added user, and click Edit.
8. Set Applies to as This namespace and subnamespaces.
9. Click OK three times to confirm.

Add User to “Performance Monitor Users” Group

1. Click Start and select Run.
2. Enter lusrmgr.msc and click OK.
3. In the Groups folder, right-click Performance Monitor Users and select Properties.
4. Go to the Members tab and click Add.
5. Add the required users.

• To monitor services like Windows cluster service and MSSQL services (SQL Server, SQL Agent, SQL Browser, and so on.), the user must be part of the Local Administrators group (required for using the win32_Service class).

  • If you prefer not to add the user to the local administrators group, configure Security descriptors for service monitoring.
  • For details on configuring security descriptors, refer to Enable Windows Service Monitoring.

• Grant the Windows domain user read-only access to the cluster using the following command:
  Grant-ClusterAccess -User <domain\user> -Readonly

• Ensure that the provided IP address/host name and credentials are valid and accessible for successful integration. To verify connectivity from the gateway, follow these steps:

  1. Launch PowerShell using the pwsh command.
  2. Run the following commands to establish a remote session to your end device:

  - {$pw = convertto-securestring -AsPlainText -Force -String "<password>"}
  - {$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist "<username>", $pw}
  - {$session = new-pssession -computername "<ipAddress>" -credential $cred -Authentication Negotiate -ErrorAction Stop}
  - {session.state}
  

SQL authorization permissions:

• Ensure the SQL Server instance accepts connections on the database port and that firewall rules allow traffic over this port.
• OpsRamp supports mixed authentication for JDBC connections, including (both NTLM and SQL Server authentication).
• The user account used for monitoring must have the following minimum privileges:

  • Server Role:

    • public (default server role)

  • Securables:
    Grant the following permissions to the monitoring user for connectivity and read-only access to SQL instances and databases:

    • CONNECT TO ANY DATABASE:
      GRANT CONNECT ANY DATABASE TO <user>
    • CONNECT TO SQL SERVER:
      GRANT CONNECT SQL TO <user>
    • SELECT ALL USER SECURABLES:
      GRANT SELECT ALL USER SECURABLES TO <user>
    • VIEW ANY DATABASE:
      GRANT VIEW ANY DATABASE TO <user>
    • VIEW ANY DEFINITION:
      GRANT VIEW ANY DEFINITION TO <user>
    • VIEW SERVER STATE:
      GRANT VIEW SERVER STATE TO <user>

Configure and Install the MS SQL Cluster Integration

1. From All Clients, select a client.
2. Go to Setup > Account.
3. Select the Integrations and Apps tab.
4. 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.
5. 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.
6. Click ADD in the MS SQL Cluster application.
7. In the Configurations page, click + ADD. The Add Configuration page appears.
8. Enter the following BASIC INFORMATION:

9. Select the below mentioned Custom Attribute:

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).

10. In the RESOURCE TYPE section, select:
    • ALL: All the existing and future resources will be discovered.
    • SELECT: You can select one or multiple resources to be discovered.
11. In the DISCOVERY SCHEDULE section, select Recurrence Pattern to add one of the following patterns:
    • Minutes
    • Hourly
    • Daily
    • Weekly
    • Monthly
12. Click ADD.

Now 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.

13. Click Next.
14. 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.

15. Select an existing registered profile.

16. 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

1. Navigate to Infrastructure > Search > DATABASES > MS SQL Cluster.
2. Select the application on the MS SQL Cluster page.
3. The RESOURCE page appears from the right.
4. 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.