Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

This article provides step-by-step instructions for creating a new read-only account for communication with MSPbots in SQL Server or MySQL. 

What's on this page:

Table of Contents

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.

Applications and Required Permissions

...

This article is intended for administrators who need to establish a connection for 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 Microsoft SQL Server Management Studio. 
  2. Go to Logins under the Security section.
  3. Right-click on Logins and select New Login to create a new account.
    SQL server new loginImage Added
  4. Click General on the Select a page menu then, fill in the following fields. 
    1. Login name - Set a unique login name. Example: mspsqlagent 
    2. Select SQL Server authentication.
      1. Password - Set a password.
      2. Confirm Password - Enter the password again.
      3. Clear the checkboxes for:
        • Enforce password policy 
        • Enforce password expiration, and
        • User must change password at next login.
          SQL General Login settingsImage Added
  5. Click OK.
  6. Next, click User Mapping on the Select a page menu and do the following:  
    1. In the User mapped to this login section on the right, select the database name you want to synchronize with MSPbots. Example: Select autotest.

    2. Scroll down the Database role membership for section, select db_datareader and public.

      • db_datareader - This option gives permission to read data from your database.

      • public - This option gives permission to allow MSPbots access to your database. 
        Warning

        Giving any account full database access is not recommended.

        login propertiesImage Added
  7. Go back to the Select a page menu and select Status
    1. Under Settings on the right, select select the Grant option for Permission to connect to the database engine.
    2. Then select Enabled for Login.
      log in propertiesImage Added
  8. 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.
    MYSQL consoleImage Added
  2. Enter the following SQL statement to create a new MySQL account.
    Code Block
    CREATE USER 'Login Name'@'%' IDENTIFIED BY 'Password';
    Where:
    • Login Name is the login name for the newly created MySQL account. 
    • % will allow remote access.
    • Password is the passcode to log the new user. 

      Example:
      Code Block
      CREATE USER 'mspbots'@'%' IDENTIFIED BY '123456'; 
       
      Note

      If you have a user already but want to create a new user, delete the previous user before creating a new one. Delete by running the SQL command drop user 'Username'@'%';

      Example: If you want to delete the username mspbots, type 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'@'%';

    Where: 

    • Select - means read data only.
    • Database Name is the name of the database to which you want to sync data to MSPbots. 

    • * means all tables or you can specify any table.
    • Login Nameis the login name set when creating the MySQL account in the previous step (Step 2).
      Example: 
      Code Block
      GRANT Select ON autotest.* TO 'mspbots'@'%';
  4. Open the Task Manager by typing the key combination Ctrl + Shift + Esc .
  5. Click Services.
    task manager servicesImage Added
  6. Find MySQL service and right-click to show the options, and select Restart to restart your MySQL.
    restart MySQLImage Added

Related Topics 

...

Content by Label
showLabelsfalse
showSpacefalse
excludeCurrenttrue
cqllabel in ("sql","on-premise")