I have set up a new user source to authenticate against AD, and to use a database for role information. I filled in all the AD information with the same data that we have in another user source, so that should be fine. I also verified the database connection is not faulted. The database is in Azure SQL DB, and I am connecting using the Microsoft SQLServer JDBC Driver. The Ignition Gateway is version 7.9.6.
I filled in the fields in the user source for “User Properties Query,”“Role List Query,” and “User’s Roles Query” with stored procedure calls in the format: EXEC [sproc_name] N’{“Username”:"?"}’ and when I execute this code in SSMS (replacing the ? with my username) I get the expected result.
When I use the page “Verify Authentication Profile” with my AD credentials, it fails and generates the below error in the log. I thought perhaps my Ignition user needed db_owner permissions on the database, so I added that, but I get the same error. Could anyone please point me in the direction of what I might be doing wrong?
…com.inductiveautomation.ignition.gateway 09Jul2018 14:55:45 java.lang.Exception: Error while loading roles from database.
java.lang.Exception: Error while loading roles from database.
at com.inductiveautomation.ignition.gateway.authentication.impl.ADtoDBHybridAuthenticator.createInternalUser(ADtoDBHybridAuthenticator.java:150)
at com.inductiveautomation.ignition.gateway.authentication.impl.ADtoDBHybridAuthenticator.authenticateAD(ADtoDBHybridAuthenticator.java:122)
at com.inductiveautomation.ignition.gateway.authentication.impl.SsoHelper.authenticate(SsoHelper.java:39)
at com.inductiveautomation.ignition.gateway.authentication.impl.ADtoDBHybridAuthenticator.authenticate(ADtoDBHybridAuthenticator.java:93)
at com.inductiveautomation.ignition.gateway.authentication.UserSourceWrapper.authenticate(UserSourceWrapper.java:200)
at com.inductiveautomation.ignition.gateway.web.pages.config.AuthenticationTestPage$1.onSubmit(AuthenticationTestPage.java:51)
at org.apache.wicket.markup.html.form.Form$9.component(Form.java:1248)
at org.apache.wicket.markup.html.form.Form$9.component(Form.java:1242)
at org.apache.wicket.util.visit.Visits.visitPostOrderHelper(Visits.java:274)
at org.apache.wicket.util.visit.Visits.visitPostOrder(Visits.java:245)
at org.apache.wicket.markup.html.form.Form.delegateSubmit(Form.java:1241)
at org.apache.wicket.markup.html.form.Form.process(Form.java:923)
at org.apache.wicket.markup.html.form.Form.onFormSubmitted(Form.java:769)
at org.apache.wicket.markup.html.form.Form.onFormSubmitted(Form.java:702)
at sun.reflect.GeneratedMethodAccessor319.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.wicket.RequestListenerInterface.internalInvoke(RequestListenerInterface.java:258)
at org.apache.wicket.RequestListenerInterface.invoke(RequestListenerInterface.java:216)
at org.apache.wicket.core.request.handler.ListenerInterfaceRequestHandler.invokeListener(ListenerInterfaceRequestHandler.java:240)
at org.apache.wicket.core.request.handler.ListenerInterfaceRequestHandler.respond(ListenerInterfaceRequestHandler.java:226)
at org.apache.wicket.request.cycle.RequestCycle$HandlerExecutor.respond(RequestCycle.java:814)
at org.apache.wicket.request.RequestHandlerStack.execute(RequestHandlerStack.java:64)
at org.apache.wicket.request.cycle.RequestCycle.execute(RequestCycle.java:253)
at org.apache.wicket.request.cycle.RequestCycle.processRequest(RequestCycle.java:210)
at org.apache.wicket.request.cycle.RequestCycle.processRequestAndDetach(RequestCycle.java:281)
at org.apache.wicket.protocol.http.WicketFilter.processRequest(WicketFilter.java:188)
at org.apache.wicket.protocol.http.WicketFilter.doFilter(WicketFilter.java:245)
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1668)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:581)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:548)
at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:226)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1160)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:511)
at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1092)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:134)
at org.eclipse.jetty.server.Server.handle(Server.java:518)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:308)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:244)
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:273)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:95)
at org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:93)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:654)
at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:572)
at java.lang.Thread.run(Unknown Source)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The index 1 is out of range.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:170)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setterGetParam(SQLServerPreparedStatement.java:698)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setValue(SQLServerPreparedStatement.java:707)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setString(SQLServerPreparedStatement.java:1015)
at org.apache.commons.dbcp.DelegatingPreparedStatement.setString(DelegatingPreparedStatement.java:132)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRPreparedStatement.setString(SRConnectionWrapper.java:1068)
at com.inductiveautomation.ignition.gateway.authentication.impl.DatabaseExpertUserSource.findRoles(DatabaseExpertUserSource.java:158)
at com.inductiveautomation.ignition.gateway.authentication.impl.ADtoDBHybridAuthenticator.createInternalUser(ADtoDBHybridAuthenticator.java:131)
… 48 common frames omitted