N O T I C E
MSPbots WIKI is moving to a new home at support.mspbots.ai
to give you the best experience in browsing our Knowledge Base resources and addressing your concerns. Click here for more info!
Page History
This guide will help you perform do the following tasks:
| Table of Contents |
|---|
steps for connecting the MSPbots SQL agent.
What's in this article:
Table of Contents style none
Complete the prerequisites
...
Please create a new read-only account in your SQL Server or MySQL as follows: How to Create a Read-only Account for Your SQL Server or MySQL. If you already have a read-only account specifically for communicating with MSPbots, please skip this prerequisite.
Connect the MSPbots agent
...
...
- Go to Integrations on the MSPbots app
and search for - menu.
- Find the SQL Agent and click the Config icon.
- Next, click the
Download link to - download icon for the MSPbots SQL Agent Client
file. You will need to install this on your SQL Server Management computer. - to start downloading the installer file.
- After the download is complete, follow the prompts to install the MSPbots SQL Agent
as an administrator- to start downloading the installer file.
- client.
- The installer will require you to enter the Secret and Data Server values from Step 2.
- Complete the installation on the Setup window.
- At the end of the Setup Wizard, you will be asked to launch the MSPbots MS-SQL Server Agent. Do not launch it because you need to run it as an administrator.
Now that we have installed the SQL Agent on your SQL Server, the next step that you need to do is to set up the configurations in MSPBots.
On the SQL Agent
Execute 
How to set up the MSPbots configuration
- Go to Integrations on the MSPbots app and search for SQL Agent.
- Click the + Add Connector button to add a data source then the SQL Server // button to expand the configuration space.
- You should have the same view as below once you have expanded the SQL Server //. Next, enter the following:
- Database Type - Select the SQL server that you have.
- IP
- Database Name
- User ID
- Password
...
| title | Important |
|---|
- During the setup, select Automatic Start and Create a desktop shortcut, then click Next.
- In the last step, tick the Launch MSPbotsAgentService checkbox and click Finish. The window will automatically close when the service starts.
- 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.
- During the setup, select Automatic Start and Create a desktop shortcut, then click Next.
Set up the MSPbots Agent Connector
...
- Go to Integrations on the MSPbots app menu.
- Search for SQL Agent and click it to open the connection settings.
- Click the +Add Connector button.
Info 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.
- 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.
Note 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.
- Click Save And Sync.
Create a dataset and validate the data
...
- Go to Datasets on the MSPbots app menu and click the New Agent Dataset button.
When the New window appears, click Integration and select SQL Agent. Don't forget to fill in the dataset Name and Description fields.
- Next, go to the Data Cleaning tab to show the Validate SQL section below.
- Select a database from the DB dropdown list.
- Scroll down to the Validate SQL section, fill in the SQL field. The most common SQL statement to use here is “select * from TABLENAME”.
- Input your desired Sync Frequency in minutes.
Tip 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.
- 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.
- Once you confirm the data is correct, click OK to create and save the dataset. The data will be synchronized in 10 minutes.
Save the logs and send the file to MSPbots Support Team
...
- Open the MSPbots Agent client and click the Save Log button on the upper right corner of the window.
- Click Confirm on the Tips popup that appears.
- Next, select the location to save the logs, provide a filename, and click Save.
- Send the file to support@mspbots.ai.
Related Topics
...
Content by Label showLabels false showSpace false excludeCurrent true cql label = "sql"
SQL User
The SQL user needs read permission only. Use the following SQL script as an example to create a read-only DB user and password.
GRANT SELECT ON *.* TO 'mspbots_sql_agent'@'%' IDENTIFIED BY '123456';
Don't forget to change 123456 to your preferred password.
ConnectWise Automate (2021.1+)
After creating the SQL user, add the user to the labtech.users table with a negative user ID to prevent the account from getting modified by ConnectWise Automate. 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.
Overview
Content Tools