Skip to main content

SQL Agent Integration Setup

Updated

SQL Agent is used to connect to your own SQL servers to convert local SQL server data into a dataset inside MSPbots that you can use for dashboard purposes.

Due to the very flexible use case and complex nature of the custom SQL code and how the SQL agent data is pulled, MSPbots only suggests SQL agent datasets in these two situations, with conditions:

  1. BrightGauge Migration, if you have dataset that inside BrightGauge and we offered free migration, we will support the dataset Migration and very limited modification. Only Standard templated ConnectWise Manage, ConnectWise Automate, N-able N-central and E-automate SQL dataset is supported, any new dataset should be developed using API dataset instead.
  2. If you are SQL expert yourself and you can write your own SQL dataset, we can't support the generic non MSPbots standard SQL dataset since there are just too many different situations that we are not able to train our employees to be capable handling all kinds of situations.

This guide will help you do the following steps for connecting the MSPbots SQL agent.

Prerequisites

Create a new read-only account in your SQL Server or MySQL.  Proceed to the next step if you already have a read-only account specifically for communicating with MSPbots.

Limitations

Before connecting to the SQL Agent integration, take note of the following limitations:

  • Ensure that the server memory or CPU usage for installing the MSPbots SQL Agent Client is below 90%. If it reaches 90%, the SQL Agent stops syncing.
  • By default, the minimum sync frequency for each dataset can be set down to 30 minutes, and the maximum sync data volume is 250,000.
    • If your requirements need to have a lower frequency or a higher data volume, please reach out to our Support. Our development team assesses the feasibility of your requirements before implementation 
  • A maximum of 2 datasets can be executed in parallel at the same time. If more than 2 datasets are run simultaneously, the extra ones are queued, which may cause synchronization delays for some datasets.

1. Connect the MSPbots Agent

  1. Go to Integrations on the MSPbots app menu. 
  2. Find the SQL Agent and click the Config icon. 

    SQL agent config

     
  3. Next, click the download icon for the MSPbots SQL Agent Client to start downloading the installer file. 


     
  4. After the download is complete, follow the prompts to install the MSPbots Agent client.
     
    • During the setup, select Automatic Start and Create a desktop shortcut, then click Next. 

      sql setup
       
    • Select the Launch MSPbotsAgentService checkbox and click Finish. The window will automatically close when the service starts. 

      complete sql setup
       
  5. Launch the MSPbots Agent client and log in to your MSPbots account using one of the following methods:
    • Microsoft Office 365 credentials.
    • Your registered email address and password.

      MSPbots Agent_3.jpg
       
  6. You can minimize or close the window after logging in, but do not log out.

    MSPbots Agent_1.jpg
     

2. Set up the MSPbots Agent Connector

  1. Go to Integrations on the MSPbots app menu. 

    integrations
     
  2. Search for SQL Agent and click it to open the connection settings. 


     

  3. Click the +Add Connector button. 
     
    image sql add connector
     

    The last success auth shows the last time when the MSPbots Agent successfully pulled the database configuration information. By default, this process is set to occur every 5 minutes.

     

  4. Fill in all of the following required fields. It may take a few moments for the agent to check in after installation. 
    • Database Type - Select either SQL Server or MySQL.
    • Agent - The agent will auto-populate with the machine name you installed.
    • IP - Provide the internal IP address or FQDN of the SQL or MySQL server.
    • Database Name
    • Time Zone - Select the time zone used by the connected database.
    • User ID - This is the username of the user with read-only access to the database.

    • Password - This is the password of the user with read-only access to the database. 
       

      image sql agent info
       

      If you have installed multiple MSPbots agents with different databases in different network segments, you have to select the device name where you manually installed the MSPbots Agent. You can find the device name in the Agent selection box. Select the one under the corresponding gateway of the database.

  5. Click Save And Sync. 

3. Create a dataset and validate the data

  1. Go to Datasets on the MSPbots app menu and click the New Agent Dataset button. 

    new agent dataset
     

  2. When the New window appears, click Integration and select SQL Agent. Don't forget to fill in the dataset Name and Description fields. 
     

    new dataset
     

  3. Next, go to the Data Cleaning tab to show the Validate SQL section below. 
    1. Select a database from the DB dropdown list. 

    2. Scroll down to the Validate SQL section, and fill in the SQL field. The most common SQL statement to use here is:

      “select * from TABLENAME
      If you encounter an error prompt, refer to Error Prompts for Validating SQL When Creating a Dataset.
    3. Input your desired Sync Frequency in minutes. 

      DB name and sync frequency
       
  4. Click Confirm when done. This will show a preview of the data for your validation. You can customize or use the default character length for string-type fields, and the time field can be set with a timezone. 


     
  5. If you confirm that the data is correct, click OK to create and save the dataset. The data will be synchronized in 10 minutes.
     
  6. Back to Integrations and click the SQL Agent integration to check the status of the datasets you created.

    dataset status.jpg

     
    • The status of the datasets includes Success, Failed, and Idle. 
       
    • When a dataset fails,
      • The SQL Agent integration will display the   icon. For example,   means there are currently 3 datasets that have failed.
         
      • The Error column will show error information.
         
    • Click the dataset link to jump to the dataset page to view the dataset information.
    • The Idle status means when editing the dataset, the dataset's Status switch has been turned off in Data Cleaning.

      Idle.jpg
       
    • The SQL Server or MySQL may have a Connect Success status even with a Failed or Idle database. The Connect Success status only indicates that the current database is accessible and available. 

      SQL-connect-success.jpg
      MySQL-connect-success.jpg
      For related information on SQL statuses, read SQL Integration and Dataset Statuses.

4. Save the logs and send the file to the MSPbots Support Team

  1. Open the MSPbots Agent client and click the Save Log button on the upper-right corner of the window. 

    save log
     
  2. Click Confirm on the Tips pop-up window. 

    tips
     
  3. Next, select the location for saving the logs, provide a filename, and click Save.
  4. Send the file to support@mspbots.ai.

Other available actions

How to disconnect SQL Agent connection in MSPbots

  1. Go to Integration on the MSPbots app menu.
  2. Searching for SQL Agent in the search bar, click the ellipsis button and then select Remove.

    SQL Agent.gif
     
  3. When the pop-up appears, click Remove.

    Remove.jpg
     

How to uninstall MSPbots Agent from your computer

  1. Open the Control Panel on your computer.
  2. Click Uninstall a program.

    Control Panel.jpg
     
  3. Locate the program MSPbots Agent version 3.0.x and right-click on it, then select Uninstall.

    MSPbots Agent version.jpg

    uninstall.jpg
     
  4. When the MSPbots Agent Uninstall pop-up appears, click Yes.

    Yes.jpg
     

How to update the MSPbots Agent

If you want to update the MSPbots Agent to the latest version, see Connect the MSPbots Agent to re-download and install the MSPbots Agent.

Known issues

1. Unable to connect to database or Login failed for user

When you encounter the prompts Unable to connect to database or Please check your User Id or Password, Error: Login failed for user, it may be due to a database connection failure.

Refer to How to Resolve "Unable to connect to database" Error to solve the issue.

error-1.jpg

error-2.jpg

 

2. The server shows Connect MSPbots SQL Agent error


Conect MSPbots SQL Agent eror.jpg

 

Possible Causes

In the MSPbots SQL Agent settings, the Agent field displays the Device Name of the machine where SQL Agent is installed.

If you make changes, such as upgrading the MSPbots SQL Agent, this may cause the Device Name of your computer to change. However, when the MSPbots system cannot match the original Device Name, it will default to displaying the Device ID.

Here is a reference for whether the Device ID and Device Name may change:

Situation Device Name Device ID (Machine GUID)
Clean OS reinstall (format) Changes Likely changes
OS reset (keep files/settings) Usually same Usually same
Hardware change (motherboard) Unchanged Likely changes
Domain join May change Unchanged
Manual rename Changes Unchanged
System cloned without Sysprep May conflict Duplicate possible

Solution

  1. Go to Integrations on the MSPbots app menu.

    integrations
     
  2. Search for SQL Agent and click it to open the connection settings.


     
  3. Click    on your server.
  4. Click the Agent drop-down list and select your current device name.
  5. Click Save and Sync.