tomcat中的JDBC领域配置

Question

<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>

但是，每次尝试验证时都会出现以下错误：

严重：执行身份验证时出现异常com.microsoft.sqlserver.jdbc.sqlserverexception：关键字“null”附近的语法不正确。在com.microsoft.sqlserver.jdbc.sqlserverexception.makefromdatabaseError（sqlserverexception.java:258)在com.microsoft.sqlserver.jdbc.sqlserverstatement.getnextresult（sqlserverstatement.java:1535)在com.microsoft.sqlserver.jdbc.sqlserverpreparedstatement.doexecutePreparedStatement.doexecutePreparedStatement.467在dscommand.execute(iobuffer.java:7151)在com.microsoft.sqlserver.jdbc.sqlserverstatement.executeCommand（sqlserverstatement.java:2478)在com.microsoft.sqlserver.jdbc.sqlserverstatement.executeCommand（sqlserverstatement.java:219)在com.microsoft.sqlserver.jdbc.sqlserverstatement.executeStatement(sqlserverstatement.java:199)在m.getRoles(jdbcrealm.java:601)在org.apache.catalia.realm.jdbcrealm.authenticate(jdbcrealm.java:404)在org.apache.catalina.realm.jdbcrealm.authenticate(jdbcrealm.java:326)在org.apache.catalina.realm.combinedrealm.authenticate(combinedrealm.java:193)在org.apache.catalina.realm.lockinedrealm.authenticate(lockinedrealm.java:158)在authenticatorbase.invoke（authenticatorbase.java:599)在org.apache.catalina.core.standardhostvalve.invoke（standardhostvalve.java:140)在org.apache.catalina.valves.errorreportvalve.invoke（errorreportvalve.invoke（errorreportvalve.java:79)在org.apache.catalina.valves.abractAccessLogvalve.invoke（阿切。科约te.http11.http11processor.service(http11processor.java:1110)在org.apache.coyote.abstractProcessorlight.process（abstractProcessorlight.java:66)在org.apache.coyote.abstractProtocol.process（abstractProcessor.java:785)在org.apache.tomcat.util.net.nioEndpoint$socketProcessor.dorun（nioEndpoint.java:1425)在readPoolExecutor$worker.run（来源未知）位于org.apache.tomcat.util.threads.taskthread$wrappingrunnable.run(taskthread.java:61)位于java.lang.thread.run（来源未知）

2017年9月21日下午12:00:59 org.apache.catalina.realm.jdbcrealm getRoles严重：执行身份验证时出现异常com.microsoft.sqlserver.jdbc.sqlserverexception：关键字“null”附近语法不正确。在com.microsoft.sqlserver.jdbc.sqlserverexception.makefromdatabaseError（sqlserverexception.java:258)在com.microsoft.sqlserver.jdbc.sqlserverstatement.getnextresult（sqlserverstatement.java:1535)在com.microsoft.sqlserver.jdbc.sqlserverpreparedstatement.doexecutePreparedStatement.doexecutePreparedStatement.467在dscommand.execute(iobuffer.java:7151)在com.microsoft.sqlserver.jdbc.sqlserverstatement.executeCommand（sqlserverstatement.java:2478)在com.microsoft.sqlserver.jdbc.sqlserverstatement.executeCommand（sqlserverstatement.java:219)在com.microsoft.sqlserver.jdbc.sqlserverstatement.executeStatement(sqlserverstatement.java:199)在m.getRoles(jdbcrealm.java:601)在org.apache.catalia.realm.jdbcrealm.authenticate(jdbcrealm.java:404)在org.apache.catalina.realm.jdbcrealm.authenticate(jdbcrealm.java:326)在org.apache.catalina.realm.combinedrealm.authenticate(combinedrealm.java:193)在org.apache.catalina.realm.lockinedrealm.authenticate(lockinedrealm.java:158)在authenticatorbase.invoke（authenticatorbase.java:599)在org.apache.catalina.core.standardhostvalve.invoke（standardhostvalve.java:140)在org.apache.catalina.valves.errorreportvalve.invoke（errorreportvalve.invoke（errorreportvalve.java:79)在org.apache.catalina.valves.abractAccessLogvalve.invoke（阿切。科约te.http11.http11processor.service(http11processor.java:1110)在org.apache.coyote.abstractProcessorlight.process（abstractProcessorlight.java:66)在org.apache.coyote.abstractProtocol.process（abstractProcessor.java:785)在org.apache.tomcat.util.net.nioEndpoint$socketProcessor.dorun（nioEndpoint.java:1425)在readPoolExecutor$worker.run（来源未知）位于org.apache.tomcat.util.threads.taskthread$wrappingrunnable.run(taskthread.java:61)位于java.lang.thread.run（来源未知）

我已经将MSSQL-JDBC-6.2.1.jre8.jar放入lib文件夹中。

Answer 1

您的问题似乎在于您没有为用户分配具有角色的指定表。根据有关JDBC领域的Tomcat文档：

• 必须有一个表，下面引用为user roles表，该表包含分配给特定用户的每个有效角色的一行。用户拥有零个、一个或多个有效角色是合法的。
• 用户角色表必须至少包含两列（如果现有应用程序需要，它可以包含更多列）：
  • Tomcat要识别的用户名（与users表中指定的值相同）。
  • 与此用户关联的有效角色的角色名称

  异常堆栈也证实了这样的猜测

  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)
  

  <Realm className="org.apache.catalina.realm.JDBCRealm"
         driverName="org.gjt.mm.mysql.Driver"
         connectionURL="jdbc:mysql://localhost/authority?user=dbuser&amp;
         password=dbpass"
         userTable="users" userNameCol="user_name" userCredCol="user_pass"
         userRoleTable="user_roles" roleNameCol="role_name"/>