Connecting to MS SQL using Windows Authentication when JasperServer is on Linux

Table of Contents 

Assumptions:

REMOTE_IP is the ip address sql server is bound to port 1433 listening for tcp/ip traffic. And database name is jasper.

Instead of using the plain ole sqljdbc.jar driver to connect to SQL Server, we will utilize a product called jTDS to connect to the SQL Server. This is because it allows JDBC to connect to SQL server using Windows Authentication mode instead of SQL mode.

Steps:

  1. Install jasper server 4.5.x
  2. Get jTDS 1.2.5 (or whatever is latest should work).

    You may have noticed that there is a few DLLs in that jTDS package. Don't worry! The driver still works in Linux. You just cannot do single-sign on in Linux of course like Windows can.

    Extract jtds-*.jar to $jasperserver/apache-tomcat/lib

  3. Edit $jasperserver/apache-tomcat/conf/Catalina/localhost/jasperserver.xml and add the following element before </Context>

    <resource auth="Container" driverclassname="net.sourceforge.jtds.jdbc.Driver" logabandoned="true" maxactive="20" maxidle="20" maxwait="60000" name="jdbc/test" password="XXXXXXX" removeabandoned="true" removeabandonedtimeout="300" type="javax.sql.DataSource" url="jdbc:jtds:sqlserver://REMOTE_HOST:1433;DatabaseName=jasper;useCursors=false;domain=COM;username=Administrator;password=XXXXXXX;user=Administrator" user="Administrator" validationquery="SELECT 1"></resource>
  4. Edit $jasperserver/apache-tomcat/webapps/jasperserver/WEB-INF/web.xml and add a new <resource-ref> for the new datasource:

    <resource-ref>
       <description>Connection Description</description>
       <res-ref-name>jdbc/sqlserver</res-ref-name>
       <res-type>javax.sql.DataSource</res-type>
       <res-auth>Container</res-auth>
    </resource-ref>
  5. Create a new database name "jasper" and grant read* permissions to the Active Directory account you desire to use.

  6. Run this script for test purposes:

    create table test (test varchar(50) NULL) insert into test values ('1') insert into test values ('2') insert into test values ('3')

  7. Create a new JSP file $jasperserver/apache-tomcat/webapps/jasperserver/test.jsp

    <%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %> <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>

    <sql:query datasource="jdbc/sqlserver" var="rs"> select test from test </sql:query>

    <html>
    <head>
       <title>DB Test</title>
     </head>
     <body>

Results

<c:forEach var="i" items="${rs.rows}">
     Text Message: <c:out value="${i.test}"/>
   </c:forEach>
 </body>
</html>
  1. Start jasperserver's tomcat. Login, and test http://localhost:8080/jasperserver/test.jsp
    You should see the rows 1, 2 and 3 from the test table.
    If it doesn't work... review the previous steps and see what went wrong.
  2. Simply create a jasper server JNDI datasource by going to: Datasources -> New Data source -> Type: JNDI data source. -> JNDI lookup is: /jdbc/sqlserver
  3. Create your tables and test your report.

Feedback
randomness