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

1. Complete the prerequisites. 

The following is required to connect the agent: 

1.1. Internal IP of the SQL Server 

1.2. db_datareader permissions

    1. Open your SQL server and select db_datareader permissions for MSPbots.

      Giving any account full DB access is not recommended.

      db user permissions
    2. Click OK.

2. 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.
      image download sql agent
    4. Locate and double-click on the downloaded installer.
    5. During the setup, select Automatic Start and Create a desktop shortcut, then click Next
      sql setup
    6. Follow the prompts. In the last step, tick the Launch MSPbotsAgentService checkbox and click Finish. The window will automatically close when the service starts.
      complete sql setup
    7. Launch the MSPbots agent and log in using your MSPbots account. You can Minimize or Close the window after login but do not log out. 
      login agent client

3. Set up the MSPbots Agent Connector.

    1. Go back to Integrations and 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.

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

    3. Click Save And Sync

4. 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. Select a database from the DB dropdown list. 

      The most common SQL statement to use here is “select * from TABLENAME”.

      DB name and sync frequency

    4. Scroll down to the Validate SQL section, fill in the SQL field, and input your desired Sync Frequency in minutes.
      Sync Frequency is currently limited to 30 minutes and can only be decreased for smaller datasets. For larger datasets, the frequency may be limited due to a large amount of data ingestion and the constraint it would place on both your server and ours. 

    5. 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.
    6. Once you confirm the data is correct, click OK to create and save the dataset. The data will be synchronized in 10 minutes.

5. Save the logs and send the file to 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 popup that appears. 
    confirm log download 
  3. Next, select the location to save the logs, provide a filename, and click Save.
  4. Send the file to

6. Common Issue: The SQL Server is unable to connect to the database.

image SQL server cannot connect

Do the following steps to resolve the issue: 

  1. Download DBeaver.
  2. Install it on the computer where the SQL Agent Client is installed. 
  3. Launch DBeaver and connect to the SQL Server.

  4. Enter your account, password, host, and port number. Click Test Connection and check for any errors.
  5. If an error occurs as shown in the following figure, please perform the following steps.
  6. Retrieve the port number of your SQL Server through querying.

  7. Follow these steps to modify the port number of your SQL Server.
    1. Click MSPbots Integrations.
    2. Type SQL Agent in the search box.
    3. Modify the port number of the SQL Server where the error occurred to match the port number of your SQL Agent.

      The Database IP information configuration rules:

      • For SQL Server 
        • Default instance + Default port (1433): IP
        • Default instance + Custom port: IP, XXXX
        • Named instance + Default port: IP\ Instance Name
        • Named instance + Custom port: IP\ Instance Name,XXXX 
      • For My SQL 
        • The Database uses the default port (3306): IP 
        • The Database uses a custom port number: IP;port=XXXX

  8. Restart SQL Server to ensure the changes made take effect.


7.  What are the states shown in the SQL Agent Integration? 

Once connected to MSPbots, your SQL Agent will show different states at various times. Each state indicates the current condition of your connection with MSPbots and may sometimes require your action.

Below is an example. 

image SQL agent integration status

This article lists these states and explains what each means.

What are the different states displayed for the SQL Agent Integration? 

State of IntegrationDescription
"Success"MSPbots is successfully connected to the MSPbots Agent.
"Your database server connection error, please try again later."There is a connection error. Reconnect to the database server or check your database server settings and connection.
"SqlAgent not connected."The SQL Agent client has not established a connection with the server.
"Connect MSPbots SQL Agent error."The SQL Agent client was successfully connected to the server but encountered an error during the connection test.
"Please check your User Id or Password, Error."(Original database error message) The database username or password entered is incorrect.
"Please check your Database Name, Error."(Original database error message) The database name entered is incorrect. 
"Please check your IP config, Error."(Original database error message) The IP address entered is incorrect.
"Please check your Database Port, Error."(Original database error message) The database port entered is incorrect.
"Unable to connect to database."The SQL Server is experiencing an issue while connecting to the database. To resolve this problem, refer to the topic Common Issue: The SQL Server is unable to connect to the database in the article SQL Agent Integration Setup.
"Warning, Data sync stopped more than 24 hours ago."This state indicates a potential issue with your data synchronization. Check your data sync settings and connection as soon as possible to restore successful data synchronization.

For a complete guide in setting up the SQL Agent integration, read the article SQL Agent Integration Setup

1 Comment

  1. Connecting to ConnectWise Automate (2021.1+) via SQL Agent

    After creating the SQL user, please add the user to the labtech.users table with a negative user ID to prevent the account from getting modified by ConnectWise Automate as the users that are not in the labtech.users table automatically get their passwords updated and ConnectWise Automate users are modified to not allow access to the SQL database.