Using Guacamole with a SQL Server Database
This documentation assumes that you already have access to a SQL Server database, either on a server of your own or hosted elsewhere, and that Guacamole has already been installed using Keeper Connection Manager. If you do not already a SQL Server database ready, please set up SQL Server before proceeding. If you do not already have Guacamole installed, please see the installation instructions.

Creating and initializing the Guacamole database

If you haven’t already done so, a database specific to Guacamole needs to be created within SQL Server. The database can be called anything you like and there are no specific requirements for the size of the database, collation, etc.; all that matters is that the database be dedicated to Guacamole, and not shared by different applications:
SQL Server Management Studio
SQL
1
CREATE DATABASE guacamole_db;
2
GO
Copied!
Guacamole will not automatically initialize the database with the required schema. You will need to do this yourself using the SQL scripts provided with the kcm-guacamole-auth-jdbc-sqlserver package, which are located within the /opt/keeper/share/guacamole-auth-jdbc-sqlserver/schema directory:
Filename
Description
001-create-schema.sql
Creates all tables and indexes which are required for the SQL Server authentication extension to function.
002-create-admin-user.sql
Creates a default administrative user, “guacadmin”, with password “guacadmin”. These credentials will need to be changed once SQL Server authentication is confirmed to be working.
The above scripts must be run in sequence. The first script, 001-create-schema.sql, creates the database schema:
SQL Server Management Studio
Command line (sqlcmd)
1
$ sqlcmd -S sqlserver_database_hostname -U sa -d guacamole_db -i /opt/keeper/share/guacamole-auth-jdbc-sqlserver/schema/002-create-admin-user.sql
Copied!

Creating a Database User for Guacamole

To execute queries against the database, Guacamole will need its own database user with sufficient privileges. Because Guacamole does not automatically apply or update its own schema, the required privileges are minimal, dealing only with creation and maintenance of data within already-defined tables and indexes. Permission to SELECT, INSERT, UPDATE, and DELETE from all tables in the database is sufficient and exactly covered by SQL Server's db_datareader and db_datawriter roles.
The login to be used by Guacamole must use SQL Server authentication, with a password which will eventually be stored within /etc/guacamole/guacamole.properties:
SQL Server Management Studio
SQL
1
CREATE LOGIN guacamole_user WITH PASSWORD = 'some_password';
2
GO
Copied!
The login must then be associated with a user specific to the Guacamole database:
SQL Server Management Studio
SQL
1
CREATE USER guacamole_user;
2
GO
Copied!
To grant the necessary SELECT, INSERT, UPDATE, and DELETE permissions for all tables in the Guacamole database, the user must be added to the database's db_datareader and db_datawriter roles:
SQL Server Management Studio
SQL
1
ALTER ROLE db_datawriter ADD MEMBER guacamole_user;
2
ALTER ROLE db_datareader ADD MEMBER guacamole_user;
3
GO
Copied!

Connecting Guacamole to SQL Server

Simple and Custom Docker Install Methods

If KCM was installed using the simple docker method, or the custom docker method, use these steps to connect to SQL Server.
Using the simple or custom docker method requires modification of docker-compose.yml file. As root, edit your docker-compose.yml file and find the "guacamole" section. Here, add the SQL Server parameters shown below.
1
guacamole:
2
image: keeper/guacamole:2
3
environment:
4
ACCEPT_EULA: "Y"
5
GUACD_HOSTNAME: "guacd"
6
SQLSERVER_HOSTNAME: "db"
7
SQLSERVER_DATABASE: "guacamole_db"
8
SQLSERVER_USERNAME: "guacamole_user"
9
SQLSERVER_PASSWORD: "xxxxxxx"
Copied!
To apply the configuration changes, Guacamole must be restarted:
Simple Install:
1
sudo ./ksm-setup.run stop
2
sudo ./ksm-setup.run upgrade
Copied!
The containers should restart after the upgrade. If not run:
1
sudo ./kcm-setup.run start
Copied!

Advanced Linux Install Method

Keeper Connection Manager packages Guacamole’s SQL Server support within the kcm-guacamole-auth-jdbc-sqlserver package. This package must be installed before creating Guacamole’s database within SQL Server, as it includes the SQL scripts necessary for doing so:
1
$ sudo yum install kcm-guacamole-auth-jdbc-sqlserver
Copied!
Once the database and database user have been prepared, Guacamole’s main configuration file, /etc/guacamole/guacamole.properties, must be modified to specify the credentials of that user and to point the SQL Server instance and database:
1
$ sudo vi /etc/guacamole/guacamole.properties
Copied!
The guacamole.properties file provided with Keeper Connection Manager is organized into sections documented with blocks of comments and example properties. The first section which must be modified is marked “JDBC-1” and defines the TCP connection information for the database in use. Uncomment the sqlserver-hostname and sqlserver-port properties, modifying their values to point to your instance of SQL Server:
1
##
2
## [JDBC-1] Database TCP connection information
3
##
4
## The TCP connection details for the PostgreSQL, MySQL / MariaDB, or SQL
5
## Server database.
6
##
7
8
#mysql-hostname: localhost
9
#mysql-port: 3306
10
11
#postgresql-hostname: localhost
12
#postgresql-port: 5432
13
14
sqlserver-hostname: localhost
15
sqlserver-port: 1433
Copied!
The “JDBC-2” section, which defines the database name and associated credentials, must also be modified to specify the correct database name, username, and password. These values are given with the sqlserver-database, sqlserver-username, and sqlserver-password properties respectively:
1
##
2
## [JDBC-2] Database name and credentials
3
##
4
## The name of the database to use, as well as the credentials to use when
5
## connecting to the database. THESE PROPERTIES ARE REQUIRED if one of the
6
## database authentication extensions will be used.
7
##
8
9
#mysql-database: guacamole_db
10
#mysql-username: guacamole_user
11
#mysql-password: some_password
12
13
#postgresql-database: guacamole_db
14
#postgresql-username: guacamole_user
15
#postgresql-password: some_password
16
17
sqlserver-database: guacamole_db
18
sqlserver-username: guacamole_user
19
sqlserver-password: some_password
Copied!
Guacamole will generally only load new extensions and reread guacamole.properties during the startup process. To apply the configuration changes, Guacamole must be restarted:
1
$ sudo systemctl restart guacamole
Copied!
If you do not have a standalone "guacamole" service
You will not have a standalone "guacamole" service if you have not deployed Guacamole automatically with the "kcm-guacamole-standalone" package. This will be the case if:
  • You have chosen to manually deploy Guacamole under your own install of Apache Tomcat or JBoss, rather than use the provided version of Tomcat.
  • You are maintaining a deployment of Glyptodon Enterprise that was originally installed before the 2.5 release (2021-09-16).
You will instead need to manually restart your install of Tomcat:
1
$ sudo systemctl restart tomcat
Copied!
If you are using SELinux (the default on both CentOS and RHEL), you must also configure SELinux to allow Tomcat to connect to the database over the network:
1
$ sudo setsebool -P tomcat_can_network_connect_db 1
Copied!
If Guacamole is not accessible after the Tomcat service has been restarted, verify that you have indeed configured SELinux to allow Tomcat to connect to the database and check the SELinux audit logs (/var/log/audit/audit.log) for AVC denials.
Updating SELinux is not necessary if using the version of Tomcat bundled with the kcm-guacamole-standalone package.
To make sure everything is working as expected, you should also visit your Keeper Connection Manager instance with a web browser (most likely at http://HOSTNAME:8080/guacamole/, where “HOSTNAME” is the hostname or IP address of your server). If all is working correctly, you should see a login screen with a username/password prompt, and you will be able to log in using the default account created with the 002-create-admin-user.sql script:
Username:
guacadmin
Password:
guacadmin
Once you have verified that you can log in successfully, you should immediately change the password. While logged into Keeper Connection Manager, you can access the built-in password changing interface by clicking on your username in the upper-right corner of the screen and selecting “Settings”.