I am trying to configure tomcat to authenticate using SQL Server. I have modified server.xml
with following:
<Realm className="org.apache.catalina.realm.LockOutRealm">
<!-- This Realm uses the UserDatabase configured in the global JNDI
resources under the key "UserDatabase". Any edits
that are performed against this UserDatabase are immediately
available for use by the Realm. -->
<Realm className="org.apache.catalina.realm.UserDatabaseRealm" resourceName="UserDatabase"/>
<!--This is only applicable for Tomcat 8.5 and higher version with SQL server authentication-->
<Realm className="org.apache.catalina.realm.JDBCRealm"
driverName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
connectionURL="jdbc:sqlserver://xxx.xx.xxx.xxx:1433;databaseName=xxxx" connectionName="user" connectionPassword="password" userTable="xx" userNameCol="username" userCredCol="password"/>
</Realm>
However I get the following error everytime I try to authenticate:
SEVERE: Exception performing authentication com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'null'. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1535) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:467) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:409) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2478) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:219) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:199) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:331) at org.apache.catalina.realm.JDBCRealm.getRoles(JDBCRealm.java:601) at org.apache.catalina.realm.JDBCRealm.authenticate(JDBCRealm.java:404) at org.apache.catalina.realm.JDBCRealm.authenticate(JDBCRealm.java:326) at org.apache.catalina.realm.CombinedRealm.authenticate(CombinedRealm.java:193) at org.apache.catalina.realm.LockOutRealm.authenticate(LockOutRealm.java:158) at org.apache.catalina.authenticator.BasicAuthenticator.authenticate(BasicAuthenticator.java:84) at org.apache.catalina.authenticator.AuthenticatorBase.authenticate(AuthenticatorBase.java:700) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:599) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79) at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:620) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:349) at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:1110) at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:785) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1425) at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Unknown Source)
Sep 21, 2017 12:00:59 PM org.apache.catalina.realm.JDBCRealm getRoles SEVERE: Exception performing authentication com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'null'. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1535) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:467) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:409) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2478) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:219) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:199) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:331) at org.apache.catalina.realm.JDBCRealm.getRoles(JDBCRealm.java:601) at org.apache.catalina.realm.JDBCRealm.authenticate(JDBCRealm.java:404) at org.apache.catalina.realm.JDBCRealm.authenticate(JDBCRealm.java:326) at org.apache.catalina.realm.CombinedRealm.authenticate(CombinedRealm.java:193) at org.apache.catalina.realm.LockOutRealm.authenticate(LockOutRealm.java:158) at org.apache.catalina.authenticator.BasicAuthenticator.authenticate(BasicAuthenticator.java:84) at org.apache.catalina.authenticator.AuthenticatorBase.authenticate(AuthenticatorBase.java:700) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:599) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79) at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:620) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:349) at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:1110) at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:785) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1425) at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Unknown Source)
I have put in the mssql-jdbc-6.2.1.jre8.jar
in the lib
folder.