Thanks to Nick Couchman's advice, I think we solved this issue. After enabling DEBUG level logging, as Nick suggested, the following appeared in catalina.out:
*### Error querying database. Cause: java.sql.SQLException: The server time zone value 'CEST' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.### The error may exist in org/apache/guacamole/auth/jdbc/user/UserMapper.xml### The error may involve org.apache.guacamole.auth.jdbc.user.UserMapper.selectOne### The error occurred while executing a query### Cause: java.sql.SQLException: The server time zone value 'CEST' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.* Setting system timezone from CEST to UTC on both Guacamole and the MySQL database server solves the issue: Guacamole can now connect to the remote mysql instance and authenticate users from there. Everything works as it should. I am not sure if there is actually an error in the *UserMapper.xml* file included in *guacamole-auth-jdbc-mysql-0.9.14.jar*, or if this is the intended behaviour. In any case, for anyone who may run the same problem, issuing the command "sudo timedatectl set-timezone UTC" on both machines should provide at least a temporary solution. Thanks again Nick Couchman for your advice. On 28 May 2018 at 16:42, Nick Couchman <[email protected]> wrote: > On Mon, May 28, 2018 at 10:34 AM, riccardopiccoli < > [email protected]> wrote: > >> Hello everyone. >> I am experiencing an issue using remote MySQL database authentication on >> Guacamole 0.9.14. >> I am using Guacamole 0.9.14, compiled from source on ubunutu 18.04 LTS. My >> servlet container is Tomcat 8, and I am using MySQL as authentication >> database. As long as Guacamole (and guacd) and the MySQL instance are on >> the >> same machine (let's call it the "Guacamole Server"), everything is fine. >> The problem is, as soon as I try to use MySQL db on a different machine >> (let's call it a "DB Server") I get blank page on >> [guacamole-server]:8080/Guacamole/#/ (where the login form should be) and >> nothing else. Tomcat's webapp manager says that guacamole.war is working >> alright, and there are no errors from guacd on syslog. The only error >> message I get is a "500 Internal Server Error" (I pasted the full text at >> the end of this mail), which appears on catalina.out whenever I try to >> access (or refresh) [guacamole-server]:8080/Guacamole/#/. >> If I go back to using the local MySQL db, the login page and everything >> beyond it are good again. >> The only change I make in my configuration between the two scenarios is in >> /etc/guacamole/guacamole.properties, where I change "mysql-hostname:" from >> "loacalhost" to the static IP of my "DB Server", or vice versa. >> In either scenario, I can manually login to the remote MySQL instance of >> guacamole_db using -u guacamole_user -h [address-of-db-server]. >> "Guacamole Server" and "DB Server" are on the same subnet and can ping >> each >> other. The "DB Server" is listening on 3306 and tcpdump shows mysql >> messages >> coming and going on both ends when I attempt to connect (and the login >> page >> stays blank). >> I have already tried rebuilding remote db from schemas (it was initially >> imported from dump), rebuild guacamole on a fresh ubuntu installation, >> setting blank password for guacamole_user, giving him ALL PRIVILEGES ON >> *.*, >> identifying him as 'guacamole_user'@'%' (instead of providing a >> restrictive >> source ip) and copying Connector/J to "DB Server", but everytime I get the >> same error. >> I think a similiar issue has already been documented in this thread >> ( >> https://www.mail-archive.com/[email protected]/msg02356.html >> ) >> but it looks like no solution was found at the time. >> If anyone has any idea on what could be causing this, please let me know. >> >> The error code in catalina.out is: >> > > You might need to provide a more complete error log, or put your Guacamole > instance into DEBUG or TRACE logging (described in the manual, using the > logback.xml file). > > I would check a couple of things: > - On the system running Guacamole, using the MySQL client to try to > connect to your remote database server, using the same credentials you > expect Guacamole to use. > - When you say DB server is listening on 3306, make sure it's listening on > all interfaces and not just on the localhost interface. > - Make sure no network firewalls are preventing or dropping the traffic. > > -Nick >
