Page History

This article primarily focuses on provides step-by-step instructions for creating a new read-only account for communication with MSPbots in SQL Server or MySQL. If you are using SQL Server, please refer to SQL Server. If you are using MySQL, please refer to MySQL.

What's in on this page:

Background Information

...

To facilitate the management of your database account, it is necessary to have a dedicated account for granting MSPbots permissions such as synchronizing data and establishing communication. Once integrated, MSPbots will synchronize your database information based on the permissions granted to it.

...

This article is intended for users in administrator roles who need to connect SQL Agent integration, E-Automate On-Premise integration, ConnectWise Automate On-Premise integration, and other integrations.

...

Creating a new read-only account for communication with MSPbots in SQL Server 

...

1. Log in to Micrisoft Microsoft SQL Server Management Studio. 
2. Go to Logins under the Security section.
3. Right-click Logins and select New Login to create a new account.
   
4. At On the General tab, fill in the following fields. 
   
   1. Login name - Set a unique Login name, for example, mspsqlagent.
   2. CheckSelect SQL Server authentication.
      
      1. Password - Set a password.
      2. Confirm Password - Confirm the password again.
      3. Uncheck the checkboxes for:
         • Enforce password policy
         ,
         •  
         • Enforce password expiration, and
         • User must change password at next login.
           Image Modified
5. Click User Mapping.

   1. In the User mapped to this login section, check the database name you want to synchronize with MSPbots, for example, autotest.

   2. In the Database role membership for section, check db_datareader and public.

      • db_datareader - Permission to read data from your database.

      • public - Permission to allow MSPbots access to your database. 
        

        Warning
        Giving any account full DB access is not recommended.

        
6. Click Status. 
   1. For the Permission to connect to the database engine option, check the Grant box.
   2. For the Login option, select Enabled.
      
7. Click OK to save your settings.

...

Creating a new read-only account for communication with MSPbots in MySQL  

...

1. Use an account with root or admin privileges to log in to theMySQL Console.
   
2. Enter the following SQL statement to create a new MySQL account.
   

   Code Block
   CREATE USER 'Login Name'@'%' IDENTIFIED BY 'Password';

   
   
   • Login Name - The login name for the newly created MySQL account, for example, mspbots.
   • % - Allow remote access
   • Password - The password used for logging in, for example, 123456.
     • For example,
       CREATE USER 'mspbots'@'%' IDENTIFIED BY '123456';

       

       Info
       If you already have a user but want to create a new user, please delete the previous user before creating a new user. Run the SQL command: drop user 'Username'@'%'; For example, if the username you want to delete is mspbots, enter: drop user 'mspbots'@'%';

3. Enter the following SQL statement to grant read-only permissions to MSPbots.

   Code Block
   GRANT Select ON Database Name.* TO 'Login Name'@'%';

   
   

   • Select - Only read data.

   • Database Name - The name of the database to which you want to sync data to MSPbots, for example, autotest.

   • * - All tables or you can specify any tables.
   • Login Name - The login name set when creating the MySQL account in step 2, for example, mspbots.
     • For example,
       GRANT Select ON autotest.* TO 'mspbots'@'%';


4. Press the following key combination: Open the Task Manager by typing the key combination Ctrl + Shift + Esc to open the Task Manager. Then click .
5. Click Services.
   
6. Find and Find MySQL service and right-click on your MySQL serviceit, then select Restart to restart your MySQL.
   

...