Database setup for SQL Server#

To use Guacamole with a SQL Server database, you will need:

An instance of the SQL Server database server.
Sufficient permission to create new databases, to create new users, and to grant those users permissions.
Network access to the database from the Guacamole server.

If this is not the case, you will need to install SQL Server before continuing or use a different database. Guacamole additionally supports:

Warning

You will need to restart the Guacamole web application in order to complete configuration. Doing this will disconnect all active users, so please:

Do this only at a time that you can tolerate service unavailability, such as a scheduled maintenance window.
Keep in mind that configuration errors may prevent Guacamole from starting back up.

Creating the Guacamole database#

It is best practice to use a dedicated database and user for the Guacamole web application, and these instructions cover only this method.

To create the database within SQL Server, execute a CREATE DATABASE command with the sqlcmd client:

$ /opt/mssql-tools/bin/sqlcmd -S localhost -U SA
Password:
1> CREATE DATABASE guacamole_db;
2> GO
1> quit

Initializing the database#

Native Webapp (Tomcat)

The schema scripts necessary to initialize the SQL Server version of Guacamole’s database are provided within the sqlserver/schema/ directory of guacamole-auth-jdbc-1.6.0.tar.gz, which must be downloaded from the release page for Apache Guacamole 1.6.0 and extracted first.

Running each the two scripts in the sqlserver/schema/ directory against the newly created database will initialize it with Guacamole’s schema. You can run these scripts using the standard sqlcmd client:

$ /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -d guacamole_db -i schema/001-create-schema.sql
Password:
Rule bound to data type.
The new rule has been bound to column(s) of the specified user data type.
Rule bound to data type.
The new rule has been bound to column(s) of the specified user data type.
$ /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -d guacamole_db -i schema/002-create-admin-user.sql
Password:

(1 rows affected)

(3 rows affected)

(5 rows affected)
$

Container (Docker)

The schema scripts necessary to initialize the SQL Server version of Guacamole’s database are provided within the /opt/guacamole/extensions/guacamole-auth-jdbc/sqlserver/schema directory of the guacamole/guacamole image.

Additionally, an initdb.sh script is provided at /opt/guacamole/bin/initdb.sh that can be used to extract the required schema initialization script:

$ docker run --rm guacamole/guacamole /opt/guacamole/bin/initdb.sh --sqlserver > initdb.sql

The resulting script can then be run using the sqlcmd client:

$ /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -d guacamole_db -i initdb.sql

Granting Guacamole access to the database#

For Guacamole to be able to execute queries against the database, you must create a new user for the database and grant that user sufficient privileges to manage the contents of all tables in the database.

The user created for Guacamole needs only SELECT, UPDATE, INSERT, and DELETE permissions on all tables in the Guacamole database. These can permissions can be easily granted in SQL Server using the db_datawriter and db_datareader roles:

$ /opt/mssql-tools/bin/sqlcmd -S localhost -U SA
Password:
1> CREATE LOGIN guacamole_user WITH PASSWORD = 'some_password';
2> GO
1> USE guacamole_db;
2> GO
1> CREATE USER guacamole_user;
2> GO
1> ALTER ROLE db_datawriter ADD MEMBER guacamole_user;
2> ALTER ROLE db_datareader ADD MEMBER guacamole_user;
3> GO
1> quit
$

Upgrading an existing Guacamole database#

If you are upgrading from a version of Guacamole older than 1.6.0, you may need to run one or more database schema upgrade scripts located within the sqlserver/schema/upgrade/ directory of guacamole-auth-jdbc-1.6.0.tar.gz (available from the release page for Apache Guacamole 1.6.0).

Each of these scripts is named upgrade-pre-VERSION.sql where VERSION is the version of Guacamole where those changes were introduced. They need to be run when you are upgrading from a version of Guacamole older than VERSION.

If there are no upgrade-pre-VERSION.sql scripts present in the schema/upgrade/ directory which apply to your existing Guacamole database, then the schema has not changed between your version and the version your are installing, and there is no need to run any database upgrade scripts.

These scripts are incremental and, when relevant, must be run in order. For example, if you are upgrading an existing database from version 0.9.13-incubating to version 1.0.0, you would need to run the upgrade-pre-0.9.14.sql script (because 0.9.13-incubating is older than 0.9.14), followed by the upgrade-pre-1.0.0.sql script (because 0.9.13-incubating is also older than 1.0.0).

Installing/Enabling support for SQL Server#

Guacamole is configured differently depending on whether Guacamole was installed natively or using the provided Docker images. The documentation here covers both methods.

Native Webapp (Tomcat)

Native installations of Guacamole under Apache Tomcat or similar are configured by modifying the contents of GUACAMOLE_HOME (Guacamole’s configuration directory), which is located at /etc/guacamole by default and may need to be created first:

You should have a copy of guacamole-auth-jdbc-1.6.0.tar.gz from earlier when you created and initialized the database.
Create the GUACAMOLE_HOME/extensions and GUACAMOLE_HOME/lib directories, if they do not already exist.
Copy sqlserver/guacamole-auth-jdbc-sqlserver-1.6.0.jar within GUACAMOLE_HOME/extensions.
Copy the JDBC driver for your database to GUACAMOLE_HOME/lib. Any of the following TDS-compatible JDBC drivers are supported for connecting Guacamole to SQL Server:
- Microsoft JDBC Driver for SQL Server
- jTDS
- Progress DataDirect’s JDBC Driver for SQL Server
- Microsoft SQL Server 2000 JDBC Driver (legacy)
If you do not have a specific reason to use one driver over the other, it’s recommended that you use the JDBC driver provided by your database vendor.
Configure Guacamole to use database authentication, as described below.

Note

Download and documentation links for all officially supported extensions for a particular version of Guacamole are always provided in the release notes for that version. The copy of the documentation you are reading now is from Apache Guacamole 1.6.0.

If you are using a different version of Guacamole, please locate that version within the release archives and consult the documentation for that release instead.

Container (Docker)

Docker installations of Guacamole include a bundled copy of Apache Tomcat and are configured using environment variables. The startup process of the Docker image automatically populates GUACAMOLE_HOME (Guacamole’s configuration directory) based on the values of these variables.

If deploying Guacamole using Docker Compose:

You will need to add at least one relevant environment variable to the environment section of your guacamole/guacamole container, such as the SQLSERVER_ENABLED environment variable:

SQLSERVER_ENABLED: "true"

If instead deploying Guacamole by running docker run manually:

The same environment variable(s) will need to be provided using the -e option. For example:

$ docker run --name some-guacamole \
    -e SQLSERVER_ENABLED="true" \
    -d -p 8080:8080 guacamole/guacamole

If SQLSERVER_ENABLED is set to false, the extension will NOT be installed, even if other related environment variables have been set. This can be used to temporarily disable usage of an extension without needing to remove all other related configuration.

You don’t strictly need to set SQLSERVER_ENABLED if other related environment variables are provided, but the extension will be installed only if at least one related environment variable is set.

Required configuration#

Additional configuration options must be specified for Guacamole to properly connect to your database. These options are specific to the database being used, and must be set correctly for authentication to work.

The options absolutely required by the database authentication extension are relatively few and self-explanatory, describing only which database will be used and how Guacamole will authenticate when querying that database:

Native Webapp (Tomcat)

If deploying Guacamole natively, you will need to add a section to your guacamole.properties that looks like the following:

sqlserver-database: guacamole_db
sqlserver-username: guacamole_user
sqlserver-password: some_password

The properties that must be set in all cases for any Guacamole installation using this extension are:

sqlserver-database: The name of the database that you created for Guacamole. This is given as “guacamole_db” in the examples given in this chapter.
sqlserver-username: The username of the user that Guacamole should use to connect to the database. This is given as “guacamole_user” in the examples given in this chapter.
sqlserver-password: The password Guacamole should provide when authenticating with the database. This is given as “some_password” in the examples given in this chapter.

Container (Docker)

If deploying Guacamole using Docker Compose, you will need to add a set of environment variables to the environment section of your guacamole/guacamole container that looks like the following:

SQLSERVER_DATABASE: 'guacamole_db'
SQLSERVER_USERNAME: 'guacamole_user'
SQLSERVER_PASSWORD: 'some_password'

If instead deploying Guacamole by running docker run manually, these same environment variables will need to be provided using the -e option. For example:

$ docker run --name some-guacamole \
    -e SQLSERVER_DATABASE="guacamole_db" \
    -e SQLSERVER_USERNAME="guacamole_user" \
    -e SQLSERVER_PASSWORD="some_password" \
    -d -p 8080:8080 guacamole/guacamole

The environment variables that must be set in all cases for any Docker-based Guacamole installation using this extension are:

SQLSERVER_DATABASE: The name of the database that you created for Guacamole. This is given as “guacamole_db” in the examples given in this chapter.
SQLSERVER_USERNAME: The username of the user that Guacamole should use to connect to the database. This is given as “guacamole_user” in the examples given in this chapter.
SQLSERVER_PASSWORD: The password Guacamole should provide when authenticating with the database. This is given as “some_password” in the examples given in this chapter.

Hint

Double-check these values. You will not be able to sign into Guacamole after installation if these parameters do not match the correct database name, username, and password.

Completing installation#

Native Webapp (Tomcat)

Guacamole will only reread its configuration and load newly-installed extensions during startup, so Tomcat will need to be restarted before these changes can take effect. Restart Tomcat and give the new functionality a try.

You do not need to restart guacd.

Hint

If Guacamole does not come back online after restarting Tomcat, check the logs. Configuration problems may prevent Guacamole from starting up, and any such errors will be recorded in Tomcat’s logs.

Container (Docker)

The environment variables that configure the behavior of Docker can only be set at the time the Docker container is created. To apply these configuration changes, you will need to recreate the container.

If your Guacamole container was deployed using Docker Compose:: Simply making the desired changes to your docker-compose.yml and running docker compose up is sufficient. Docker Compose will automatically recognize that the environment variables of the container have changed and recreate it.
If your Guacamole container was deployed manually (using docker run):: You wll need to manually use docker rm to remove the old container and then manually recreate it with docker run and the new environment variables.

Hint

If Guacamole does not come back online after recreating the container, check the Docker logs. Configuration problems may prevent Guacamole from starting up, and any such errors will be recorded in the Docker logs for the Guacamole container.

Logging in#

The default Guacamole user created by the provided SQL scripts is “guacadmin”, with a default password of “guacadmin”. Once you have verified that the database authentication is working, you should change your password immediately.

Once you have successfully logged in and changed your password, you can begin using the web UI to create other users, groups, and connections. More detailed instructions for doing this are given in Guacamole’s administrative interface.

Database setup for SQL Server

Contents

Database setup for SQL Server#

Creating the Guacamole database#

Initializing the database#

Granting Guacamole access to the database#

Upgrading an existing Guacamole database#

Installing/Enabling support for SQL Server#

Required configuration#

Additional configuration (optional)#

Enforcing password policies#

Password complexity#

Password age / expiration#

Preventing password reuse#

Concurrent use of Guacamole connections#

External users and connections#

Access window enforcment#

Completing installation#

Logging in#