Re: MS SQL Extension Broken?

[Mike Jumper]

On Fri, Feb 9, 2018 at 5:33 AM, Nick Couchman  wrote:

> On Fri, Feb 9, 2018 at 5:55 AM, sebastian.k  > wrote:
>
>> Hi there,
>>
>> I've setup guacamole on centos 7.4/rhel with the new MS-SQL extension from
>> version 0.9.14.
>> The problem is that users get "corrupted". After a fresh install I can
>> login
>> once or twice with the guacadmin user without problems. But on the third
>> or
>> forth login attempt I'm getting. "Unexpected internal error". I logged out
>> correctly every time. I also tried installing MS-SQL locally but the
>> results
>> are the same.
>> ...
>> It would be great if someone could tell me what I'm doing wrong.
>> Is somebody using MS SQL 2016 with latest Guacamole 0.9.14 without any
>> trouble?
>>
>> Thanks in advance
>>
>> Sebastian
>>
>
> It's definitely possible there is an issue with the MS-SQL Extension, but
> I have not run into this particular behavior.
>

I just managed to reproduce this myself. The problem is within the query
returning user information for a single account. From my test instance's
logs:

09:43:47.642 [http-nio-8080-exec-18] DEBUG
o.a.g.a.j.user.UserMapper.selectOne - ==>  Preparing: SELECT
[guacamole_user].user_id, [guacamole_user].username, password_hash,
password_salt, password_date, disabled, expired, access_window_start,
access_window_end, valid_from, valid_until, timezone, full_name,
email_address, organization, organizational_role, ( SELECT MAX(start_date)
FROM [guacamole_user_history] WHERE [guacamole_user_history].user_id =
[guacamole_user].user_id ) AS last_active FROM [guacamole_user] LEFT JOIN
[guacamole_user_history] ON [guacamole_user_history].user_id =
[guacamole_user].user_id WHERE [guacamole_user].username = ?
09:43:47.642 [http-nio-8080-exec-18] DEBUG
o.a.g.a.j.user.UserMapper.selectOne - ==> Parameters: guacadmin(String)
09:43:47.650 [http-nio-8080-exec-18] DEBUG
o.a.g.a.j.user.UserMapper.selectOne - <==  Total: 2
09:43:47.656 [http-nio-8080-exec-18] ERROR o.a.g.rest.RESTExceptionWrapper
- Unexpected internal error: Expected one result (or null) to be returned
by selectOne(), but found: 2
09:43:47.672 [http-nio-8080-exec-18] DEBUG o.a.g.rest.RESTExceptionWrapper
- Unexpected error in REST endpoint.
org.apache.ibatis.exceptions.TooManyResultsException: Expected one result
(or null) to be returned by selectOne(), but found: 2

The new login history tracking is resulting in multiple rows being returned
for a query which *should* be returning only one row, causing result sanity
checks to fail. I'll have to retest against MySQL and PostgreSQL to make
sure this isn't affecting those versions of the database extension, but I
believe this is isolated to SQL Server. The problem is that stray LEFT
JOIN, which should have been removed as part of the following commit:

https://github.com/apache/guacamole-client/commit/394a289879dba9273f976a9174ad4eec45b674c2

New issue in JIRA:

https://issues.apache.org/jira/browse/GUACAMOLE-505

- Mike

Re: MS SQL Extension Broken?

[Nick Couchman]

On Fri, Feb 9, 2018 at 5:55 AM, sebastian.k 
wrote:

> Hi there,
>
> I've setup guacamole on centos 7.4/rhel with the new MS-SQL extension from
> version 0.9.14.
> The problem is that users get "corrupted". After a fresh install I can
> login
> once or twice with the guacadmin user without problems. But on the third or
> forth login attempt I'm getting. "Unexpected internal error". I logged out
> correctly every time. I also tried installing MS-SQL locally but the
> results
> are the same.
> ...
> It would be great if someone could tell me what I'm doing wrong.
> Is somebody using MS SQL 2016 with latest Guacamole 0.9.14 without any
> trouble?
>
> Thanks in advance
>
> Sebastian
>

It's definitely possible there is an issue with the MS-SQL Extension, but I
have not run into this particular behavior.  I doubt very much that the
user - or at least the user information in the database - is actually
getting corrupted.  It's more likely some sort of connection error between
the extension or JDBC driver and SQL Server.  So, a few questions for you...
- What version of Java are you using?
- What version of Tomcat are you using?
- What make/model/version of the MS-SQL JDBC driver are you using?
- If you restart Tomcat, does the problem go away?
- If you create a new user while you're able to log in, does that user
account work when guacadmin does not?
- Are there any system-level messages - anything in dmesg output, or
/var/log/messages, etc., that indicates any other sort of problem (OOM,
SELinux, etc.)?

Also, if you could, could you configure the Guacamole Client for either
debug or trace logging and post the output (preferably in a PasteBin or
something like that, and not in the e-mail)?  This can be done by creating
a logback.xml file in GUACAMOLE_HOME, and you can find instructions for
what that file needs to contain here:

http://guacamole.apache.org/doc/gug/configuring-guacamole.html#webapp-logging

-Nick