Debian 8.6, guacamole; install and configure mysql back-end

In previous posts I have covered a basic installation of guacamole, using a user-mapping.xml file to define users and connections, I have also covered how to publish guacamole using apache2 over HTTPS whist protecting your deployment with fail2ban and ufw.

In this post I outline the steps required to move your guacamole installation to a mysql back-end.

First, lets install and configure mysql – you’ll need a root password and a password for the “guacamole” mysql user:

# Install mysql server
apt-get install mysql-server

# you'll be prompted for a root user account password
<password>
    
# review /etc/mysql/my.cnf for bindings, by default mysql will listen on 127.0.0.1 only.
vi /etc/mysql/my.cnf

# secure your mysql deployment
mysql_secure_installation

# prepare the database and user needed for guacamole
mysql -u root -p
    CREATE DATABASE guacamole;
    CREATE USER 'guacamole'@'localhost' IDENTIFIED BY '<password>';
    GRANT SELECT,INSERT,UPDATE,DELETE ON guacamole.* TO 'guacamole'@'localhost';
    FLUSH PRIVILEGES;
    quit

Download and deploy the mysql extension for guacamole (this must match the version of guacamole-client you are using):

cd ~
wget http://netix.dl.sourceforge.net/project/guacamole/current/extensions/guacamole-auth-jdbc-0.9.9.tar.gz
tar -zxvf guacamole-auth-jdbc-0.9.9.tar.gz
cd ~/guacamole-auth-jdbc-0.9.9/mysql
cp *.jar /etc/guacamole/extensions/
cd..

Now prepare the guacmole database using the supplied scripts:

cd ~/guacamole-auth-jdbc-0.9.9/mysql
cat schema/*.sql | mysql -u root -p guacamole

Next, download and deploy the mysql java connection library:

cd ~
wget https://cdn.mysql.com//Downloads/Connector-J/mysql-connector-java-5.1.40.tar.gz
tar -zxvf mysql-connector-java-5.1.40.tar.gz
cd mysql-connector-java-5.1.40
cp mysql-connector-java-5.1.40-bin.jar /etc/guacamole/lib/

Finally, you can clear the contents of your guacamole.properties file and add only the lines below, be sure to change the mysql user password accordingly – note you will need to recreate you user mappings via the guacamole admin console.

vi /etc/guacamole/guacamole.properties

# MySQL properties
mysql-hostname: localhost
mysql-port: 3306
mysql-database: guacamole
mysql-username: guacamole
mysql-password: <password>

Now restart tomcat8 (using “systemctl restart tomcat8” and browse to your guacamole deployment – you should be able to login using the credentials guacamole / guacamole.

As an aside, I did run into an issue where, having built the guacamole-client via git clone, I was unable to login using guacamole / guacamole credentials.

I was able to find an error in catalina.out:

cat /var/log/tomcat8/catalina.out | grep guacamole-auth-jdbc-mysql-0.9.9.jar

ERROR o.a.g.extension.ExtensionModule – Extension “guacamole-auth-jdbc-mysql-0.9.9.jar” could not be loaded: Authentication provider class cannot be loaded (wrong version of API?)

My solution was to stop tomcat8, remove the guacamole.war file, guacamole tomcat8 directory and then re-dploy the guacamole client, using the commands below:

# stop tomcat8
systemctl stop tomcat8

# remove guacamole files/ directoryrm /var/lib/tomcat8/webapps/guacamole.war
rm -r /var/lib/tomcat8/webapps/guacamole/

# download the pre-built guacamole-client
wget -O guacamole.war http://downloads.sourceforge.net/project/guacamole/current/binary/guacamole-0.9.9.war

# deploy and create symlink under tomcat8
cp /guacamole/target/guacamole.war /etc/guacamole/
ln -s /etc/guacamole/guacamole.war /var/lib/tomcat8/webapps/

# start tomcat8
systemctl start tomcat8