[GUACAMOLE-1253] Hitting 2100 SQLServer query parameter limit with large user base

• Type: Bug
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Fix Version/s: 1.5.2
• Component/s: guacamole-auth-jdbc, guacamole-auth-jdbc-sqlserver
• Environment: docker
• Assignee: Mike Jumper
• Reporter: Starke Gardner
• Created: 06/Jan/21 14:08
• Updated: 21/Jul/23 22:42
• Resolved: 15/May/23 20:11

Description

We have a large user count ~1500 and use SQL Server. When trying to load the user settings on the web interface the page will never load and we get this error:

SELECT [guacamole_user].user_id, [guacamole_entity].entity_id, [guacamole_entity].name, 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] JOIN [guacamole_entity] ON [guacamole_user].entity_id 
 [guacamole_entity].entity_id WHERE [guacamole_entity].name IN ( ? , ? , ? ,.......... ? ) AND [guacamole_entity].type = 'USER'; SELECT [guacamole_user_attribute].user_id [guacamole_user_attribute].attribute_name, [guacamole_user_attribute].attribute_value FROM [guacamole_user_attribute] JOIN [guacamole_user] ON [guacamole_user].user_id 
 [guacamole_user_attribute].user_id JOIN [guacamole_entity] ON [guacamole_user].entity_id = [guacamole_entity].entity_id WHERE [guacamole_entity].name IN ( ? , ? ,........ ? ) AND [guacamole_entity].type = 'USER';
### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.

I truncated the name IN statement as there was a "?" for each of the 1500 users two times in the select statement.

Issue Links

• causes: GUACAMOLE-1789 SQLSERVER_BATCH_SIZE defined twice in SQLServerGuacamoleProperties
• causes: GUACAMOLE-1833 Merge conflict markers left in guacamole-manual source
• is duplicated by: GUACAMOLE-1422 Admin login failes with sql backend
• is related to: GUACAMOLE-1796 Default batch size for SQL Server is too large