How to Create a Read-only Account for Your SQL Server or MySQL

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 provides step-by-step instructions for creating a new read-only account for communication with MSPbots in SQL Server or MySQL. 

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

        Giving any account full database access is not recommended. 
        

        
        
        
        
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. 
      
      
      
      
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 the MySQL Console. 
   
   
   
   
2. Enter the following SQL statement to create a new MySQL account.
   

   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:
     

     CREATE USER 'mspbots'@'%' IDENTIFIED BY '123456'; 

     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.

   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 Name is the login name set when creating the MySQL account in the previous step (Step 2).
     Example: 
     

     GRANT Select ON autotest.* TO 'mspbots'@'%';

4. Open the Task Manager by typing the key combination Ctrl + Shift + Esc .
5. Click Services. 
   
   
   
   
6. Find MySQL service and right-click to show the options, and select Restart to restart your MySQL.