Deploying a CentOS7 VM with MariaDB for Remote Desktop Manager

At my workplace we use the Devolutions Remote Desktop Manager (RDM) application. The application allows you to build a database with the IT resources you manage. If you haven’t tried it already I suggest you give the Free Edition or Enterprise Trial a go. In our case we assist many customers remotely and this application allows us to easily connect to the network via VPN and then we can connect to the resource where we need to make the changes/troubleshooting. All the resources are grouped per customer so it is easy to organise and manage. It is also allows you to keep credentials stored securely.

We used to us an SQLlite database file on a server share and with the help of a powershell script, we copied the database, vpn phone book and other add-ons to the local computer to keep an offline copy of the database should we be out of the office. Although this was working well, it was limited. For example the need to manually sync the offline content, manually create the vpn entries in the phonebook with a text editor and the inability to control who has access to the content was a great concern.

We decided to purchase the Enterprise license for the product to provide us with the possibility to use a database system which could store more details like vpn phonebook and documentation like Visio diagrams or PDFs within the database with the possibility to have an up-to-date offline copy for a restricted amount of time. This would also allow us to grant certain rights to users which for example would not allow them to export the database entries or modify them.

As a central database we opted to use MariaDB. There was not a specific reason for this except that it supports all the features we need, like MySQL does, and most importantly the name is the same of my future daughter Maria 🙂

We wanted to host the database on a virtual machine with CentOS7 installed. I am going to describe how I installed MariaDB and configure the database for use with RDM. The VM was deployed using This guide which explains the process very well if you are not familiar, which I wasn’t to a certain extent. Thanks goes to Simon Davies for it. I stopped at installing the Open-VM-Tools package. Since I am not creating a template, I do not need to install the deployment tools.

Installing MariaDB

To get started we need to add the MariaDB repository to the yum repository list within CentOS. I suggest going to the Repository Configuration Tool site. Here you can generate the details that will be inserted in the MariaDB.repo file.

As you can see from the below screenshot, I have generated the repository settings for MariaDB 10.1

Use your preferred editor, nano or vi, to add a file in /etc/yum.repos.d/MariaDB.repo and add the content as seen in the screenshot. You may want to use SSH to copy/paste it.
Once that is done, execute yum update to update the packages list.

Type Y and Enter to download the repository packages.

Type Y and Enter again to import the GPG key

Next we will need to :

• Install MariaDB server and client packages.
• Configure the firewall to allow connections to the database.
• Start/Restart the required services.
• Execute the mysql_secure_installation.

For this we will run the below concatenated (&&) command:

yum -y install mariadb-server mariadb-client && systemctl start mariadb && systemctl enable mariadb && firewall-cmd --permanent --add-service mysql && systemctl restart firewalld.service && mysql_secure_installation

Once that is done, you will be prompted by the mysql_secure_installation wizard to configure mysql security starting with the root password.

• Press Enter when required to enter the current password. Since this is the default installation, there is no root password.
• Then press Y to set a root password and set a new password.
• It is suggested to remove Anonymous users so press Y when asked to remove Anonymous Users.
• If you need to access the database remotely using th root username press N when asked to Disallow root login remotely. This is not suggested and you should press Y to disallow it.
• Press Y to remove any test databases if you are not going to use them. We will configure our own for RDM
• Press Y to reload privilege tables.

Now we have secured MariaDB. Below is the output of the above wizard for reference:

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
 
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
 
Enter current password for root (enter for none):
OK, successfully used password, moving on...
 
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
 
Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!
 
 
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
 
Remove anonymous users? [Y/n] y
... Success!
 
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
 
Disallow root login remotely? [Y/n] y
... Success!
 
By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
 
Remove test database and access to it? [Y/n] y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
 
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
 
Reload privilege tables now? [Y/n] Y
... Success!
 
Cleaning up...
 
All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.
 
Thanks for using MariaDB!

Creating the Database

To create the database we need to connect to MariaDB. To do this you need to type mysql -uroot -p and enter the newly created password.

To create a database with the name RDM_DB, execute the below command.

CREATE DATABASE RDM_DB;

We are going to create a user, admin_rdm, which will be used to manage the database. We will assign a password ‘Password’.

CREATE USER 'admin_rdm'@'192.168.%' IDENTIFIED BY 'Password';

The ‘192.168.%’  is used to allow access to the user from the different subnets we have. % is a wildcard.

Next we need to assign permissions to the admin_rdm user on the RDM_DB database.

GRANT ALL ON RDM_DB.* TO 'admin_rdm'@'192.168.%' WITH GRANT OPTION;

If you are going to create different users to access the database, from the RDM administration tools, you will need to provide CREATE USER and SELECT permissions to the admin_rdm user.

GRANT SELECT ON mysql.user TO 'admin_rdm'@'192.168.%';

GRANT CREATE USER ON *.* TO 'admin_rdm'@'192.168.%';

Now we need to FLUSH the PRIVILEGES to enforce the new permissions

FLUSH PRIVILEGES;

Connecting RDM to the Database

The database has been created and now we need to create a data source in RDM.

To create a data source click on File and then click on Data Sources.

Now click on the New Data Source icon on the top left the tool bar.

In the ‘Add a new data source’ window select MariaDB and click OK

Fill in the details to connect to the database host and in the Schema section click on the … button. This will list the databases that are available on the host. Select RDB_DB and click OK

If you click on Test Schema you will find that it will report an error that Table ‘RDM_DB.Connecions’ doesn’t exist. Click OK

Click on the Upgrade tab and the click on Update Schema. This will create the required schema for RDM to operate properly.

You will notice a button named Create Schema. You can use that to create a new database and its schema on MariaDB. This will require the admin_rdm to have elevated privileges. I chose to avoid that and create the database manually.

Now you can import your sessions from the Free or Enterprise Trial editions of Remote Desktop Manager. I think the Iport/Export options will also allow you to change from one Data source to another.

I hope that this guide is useful to someone, and if any MySQL/MariaDB gurus want to add any suggestions please go ahead.