[ https://issues.apache.org/jira/browse/GUACAMOLE-525?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Michael Jumper updated GUACAMOLE-525: ------------------------------------- Description: New installation with 0.9.14 authenticating against MSSQL server 2012. guacamole_db set to compatibility mode "SQL Server 2005 (90)". No problem logging in as guacadmin, but when trying to create a user, I get "Incorrect syntax near 'LIMIT'". Validation of the query shown in catalina.out by debugging in SQL management studio confirms the error. The list of keywords for MSSQL server 2008 and newer does not include LIMIT. Catalina.out error message: {code:none} 10:59:06.579 [http-nio-8080-exec-9] ERROR o.a.g.rest.RESTExceptionWrapper - Unexpected internal error: ### Error querying database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'LIMIT'. ### The error may exist in org/apache/guacamole/auth/jdbc/user/PasswordRecordMapper.xml ### The error may involve defaultParameterMap ### The error occurred while setting parameters ### SQL: SELECT [guacamole_user_password_history].user_id, [guacamole_user_password_history].password_hash, [guacamole_user_password_history].password_salt, [guacamole_user_password_history].password_date FROM [guacamole_user_password_history] JOIN [guacamole_user] ON [guacamole_user_password_history].user_id = [guacamole_user].user_id WHERE [guacamole_user].username = ? ORDER BY [guacamole_user_password_history].password_date DESC LIMIT ? ### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'LIMIT'. Mar 12, 2018 10:59:06 AM com.sun.jersey.spi.container.ContainerResponse logException SEVERE: Mapped exception to response: 500 (Internal Server Error) org.apache.guacamole.rest.APIException at org.apache.guacamole.rest.RESTExceptionWrapper.invoke(RESTExceptionWrapper.java:202) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60) at com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$TypeOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:185) at com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:75) at com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:302) at com.sun.jersey.server.impl.uri.rules.SubLocatorRule.accept(SubLocatorRule.java:137) at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147) at com.sun.jersey.server.impl.uri.rules.SubLocatorRule.accept(SubLocatorRule.java:137) at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147) at com.sun.jersey.server.impl.uri.rules.SubLocatorRule.accept(SubLocatorRule.java:137) at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147) at com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:108) at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147) at com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:84) at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1511) at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1442) at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1391) at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1381) at com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:416) at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:538) at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:716) at javax.servlet.http.HttpServlet.service(HttpServlet.java:729) at com.google.inject.servlet.ServletDefinition.doService(ServletDefinition.java:263) at com.google.inject.servlet.ServletDefinition.service(ServletDefinition.java:178) at com.google.inject.servlet.ManagedServletPipeline.service(ManagedServletPipeline.java:91) at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:62) at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:118) at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:113) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79) at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:522) at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1095) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:672) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1520) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1476) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Thread.java:748) {code} was: New installation with 0.9.14 authenticating against MSSQL server 2012. guacamole_db set to compatibility mode "SQL Server 2005 (90)". No problem logging in as guacadmin, but when trying to create a user, I get "Incorrect syntax near 'LIMIT'". Validation of the query shown in catalina.out by debugging in SQL management studio confirms the error. The list of keywords for MSSQL server 2008 and newer does not include LIMIT. Catalina.out error message: 10:59:06.579 [http-nio-8080-exec-9] ERROR o.a.g.rest.RESTExceptionWrapper - Unexpected internal error: ### Error querying database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'LIMIT'. ### The error may exist in org/apache/guacamole/auth/jdbc/user/PasswordRecordMapper.xml ### The error may involve defaultParameterMap ### The error occurred while setting parameters ### SQL: SELECT [guacamole_user_password_history].user_id, [guacamole_user_password_history].password_hash, [guacamole_user_password_history].password_salt, [guacamole_user_password_history].password_date FROM [guacamole_user_password_history] JOIN [guacamole_user] ON [guacamole_user_password_history].user_id = [guacamole_user].user_id WHERE [guacamole_user].username = ? ORDER BY [guacamole_user_password_history].password_date DESC LIMIT ? ### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'LIMIT'. Mar 12, 2018 10:59:06 AM com.sun.jersey.spi.container.ContainerResponse logException SEVERE: Mapped exception to response: 500 (Internal Server Error) org.apache.guacamole.rest.APIException at org.apache.guacamole.rest.RESTExceptionWrapper.invoke(RESTExceptionWrapper.java:202) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60) at com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$TypeOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:185) at com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:75) at com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:302) at com.sun.jersey.server.impl.uri.rules.SubLocatorRule.accept(SubLocatorRule.java:137) at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147) at com.sun.jersey.server.impl.uri.rules.SubLocatorRule.accept(SubLocatorRule.java:137) at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147) at com.sun.jersey.server.impl.uri.rules.SubLocatorRule.accept(SubLocatorRule.java:137) at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147) at com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:108) at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147) at com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:84) at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1511) at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1442) at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1391) at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1381) at com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:416) at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:538) at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:716) at javax.servlet.http.HttpServlet.service(HttpServlet.java:729) at com.google.inject.servlet.ServletDefinition.doService(ServletDefinition.java:263) at com.google.inject.servlet.ServletDefinition.service(ServletDefinition.java:178) at com.google.inject.servlet.ManagedServletPipeline.service(ManagedServletPipeline.java:91) at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:62) at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:118) at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:113) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79) at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:522) at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1095) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:672) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1520) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1476) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Thread.java:748) > Creating user results in "Incorrect syntax near 'LIMIT'" > -------------------------------------------------------- > > Key: GUACAMOLE-525 > URL: https://issues.apache.org/jira/browse/GUACAMOLE-525 > Project: Guacamole > Issue Type: Bug > Components: guacamole-auth-jdbc-sqlserver > Affects Versions: 0.9.14 > Environment: Linux <hostname> 4.4.0-87-generic #110-Ubuntu SMP Tue > Jul 18 12:55:35 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux > Guacamole 0.9.14 > Database for auth: MSSQL 2012 - guacamole_db runs in 2005 compatibility mode. > > Reporter: Tommy Larsen > Priority: Major > > New installation with 0.9.14 authenticating against MSSQL server 2012. > guacamole_db set to compatibility mode "SQL Server 2005 (90)". > No problem logging in as guacadmin, but when trying to create a user, I get > "Incorrect syntax near 'LIMIT'". Validation of the query shown in > catalina.out by debugging in SQL management studio confirms the error. > The list of keywords for MSSQL server 2008 and newer does not include LIMIT. > Catalina.out error message: > {code:none} > 10:59:06.579 [http-nio-8080-exec-9] ERROR o.a.g.rest.RESTExceptionWrapper - > Unexpected internal error: > ### Error querying database. Cause: > com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near > 'LIMIT'. > ### The error may exist in > org/apache/guacamole/auth/jdbc/user/PasswordRecordMapper.xml > ### The error may involve defaultParameterMap > ### The error occurred while setting parameters > ### SQL: SELECT [guacamole_user_password_history].user_id, > [guacamole_user_password_history].password_hash, > [guacamole_user_password_history].password_salt, > [guacamole_user_password_history].password_date FROM > [guacamole_user_password_history] JOIN [guacamole_user] ON > [guacamole_user_password_history].user_id = [guacamole_user].user_id > WHERE [guacamole_user].username = ? ORDER BY > [guacamole_user_password_history].password_date DESC LIMIT ? > ### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax > near 'LIMIT'. > Mar 12, 2018 10:59:06 AM com.sun.jersey.spi.container.ContainerResponse > logException > SEVERE: Mapped exception to response: 500 (Internal Server Error) > org.apache.guacamole.rest.APIException > at > org.apache.guacamole.rest.RESTExceptionWrapper.invoke(RESTExceptionWrapper.java:202) > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) > at > sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) > at > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) > at java.lang.reflect.Method.invoke(Method.java:498) > at > com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60) > at > com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$TypeOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:185) > at > com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:75) > at > com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:302) > at > com.sun.jersey.server.impl.uri.rules.SubLocatorRule.accept(SubLocatorRule.java:137) > at > com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147) > at > com.sun.jersey.server.impl.uri.rules.SubLocatorRule.accept(SubLocatorRule.java:137) > at > com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147) > at > com.sun.jersey.server.impl.uri.rules.SubLocatorRule.accept(SubLocatorRule.java:137) > at > com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147) > at > com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:108) > at > com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147) > at > com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:84) > at > com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1511) > at > com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1442) > at > com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1391) > at > com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1381) > at > com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:416) > at > com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:538) > at > com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:716) > at javax.servlet.http.HttpServlet.service(HttpServlet.java:729) > at > com.google.inject.servlet.ServletDefinition.doService(ServletDefinition.java:263) > at > com.google.inject.servlet.ServletDefinition.service(ServletDefinition.java:178) > at > com.google.inject.servlet.ManagedServletPipeline.service(ManagedServletPipeline.java:91) > at > com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:62) > at > com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:118) > at > com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:113) > at > org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240) > at > org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207) > at > org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213) > at > org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106) > at > org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502) > at > org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141) > at > org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79) > at > org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616) > at > org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88) > at > org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:522) > at > org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1095) > at > org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:672) > at > org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1520) > at > org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1476) > at > java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) > at > java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) > at > org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) > at java.lang.Thread.run(Thread.java:748) > {code} -- This message was sent by Atlassian JIRA (v7.6.3#76005)