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

Prerequisites

On Gateway

• OpsRamp Classic Gateway 15.0.0 and above.
• OpsRamp NextGen Gateway 15.0.0 and above.
• It is recommended to use the latest Gateway version for full coverage of recent bug fixes, enhancements, and improvements.

On Target Cluster Nodes / Standalone Nodes

The following prerequisites are required for a non-admin user

Add Windows Domain Permissions

• The Windows domain user should be added to the Performance Monitor Users group to fetch performance counter metrics.

net localgroup "Performance Monitor Users" /add <user>

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. Click the Security tab.
5. Select Root and click Security.
6. Add the user and select the following permissions:
   • Execute methods
   • Enable account
   • Enable remoting
   • Read security
7. Click Advanced, select the added non-admin domain user and click Edit.
8. Select This namespace and subnamespaces from the dropdown in the field Applies to.
9. Click OK three times.

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. Click the Members tab and click Add.
5. Add the required users.

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 <user>

CONNECT SQL

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>

Connection Modes

Starting from MS SQL Cluster version 6.3.0, you can select the connection mode. The options available are:

1. PowerShell (existing)
2. OpenSSH (new)

If PowerShell is selected as ConnectionMode:

The windows domain user should have following permissions and configurations to establish a successful connection between the Gateway and the target machine using PowerShell remoting.

• Enable PowerShell Remoting on the target machine using the following command:

  Enable-PSRemoting -Force
  

• Add the user to the **Remote Management Users** group to allow remote management of the target machine.

  net localgroup "Remote Management Users" /add <user>
  

• Add OpsRampGatewayIp to the TrustedHosts list on the target machine to allow the PowerShell connection from the Gateway to the target machine.
  • 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
      

    • To restart:

      Restart-Service -Force WinRM
      

• Ports 5985 and 5986 must be opened on all nodes and clusters for PowerShell remoting.

  Note

  By default, WS-Man and PowerShell remoting use ports 5985 and 5986 for connections over HTTP and HTTPS.
  • Verify connectivity from the Gateway to the target machine using the following command:

    telnet <cluster or node ip> 5985/5986
    

• The user should be added on all nodes and clusters.
• Ensure that the provided IP address/host name and credentials are valid and accessible for successful integration. Verify connectivity from the Gateway using the following steps:
  • Launch PowerShell using the following command:

    pwsh
    

  • Run the following commands to establish the connection using the details of 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
    

  • Check the session state:

    $session.State
    

    Note

    The session state should be Open.
  • If the session is not established, verify that all Prerequisites are met on all servers and clusters.

If OpenSSH is selected as ConnectionMode:

• OpenSSH must be installed and configured on the target Windows machine.

• Password authentication must be enabled in SSH config: PasswordAuthentication yes

  • Edit sshd_config (run PowerShell as Administrator)
  • notepad C:\ProgramData\ssh\sshd_config (Note: If not at this location, locate the correct path for sshd_config and perform the following step)
    • Find the line #PasswordAuthentication yes (or PasswordAuthentication no), uncomment it and set it to yes, then click Save.
    • Find the line AllowUsers (or add it if missing) and set it to - AllowUsers <ssh-username>
    • Save the file.

  Restart-Service sshd`
  

• Ensure the sshd service startup type is Automatic

  Set-Service -Name sshd -StartupType Automatic`
  

• Set the OpenSSH default shell to powershell.exe using the following command on all the nodes

Set-ItemProperty -Path "HKLM:\SOFTWARE\OpenSSH" -Name DefaultShell -Value "$PSHOME\powershell.exe"; Restart-Service sshd

• Open default SSH port 22 on all the nodes and clusters for OpenSSH connection.
• The user should be added in all the target nodes & cluster

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.

×