How to use Windows Authentication to SQL Server from a Jasper Server running on Linux

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 are 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 name="jdbc/sqlserver"
             auth="Container"
             type="javax.sql.DataSource"
             driverClassName="net.sourceforge.jtds.jdbc.Driver"
             url="jdbc:jtds:sqlserver://REMOTE_HOST:1433;DatabaseName=jasper;useCursors=false;domain=COM;username=Administrator;password=XXXXXXX;user=Administrator"
             user="Administrator"
             password="XXXXXXX"
             maxActive="20"
             maxIdle="20"
             maxWait="60000"
             removeAbandoned="true"
             logAbandoned="true"         
             removeAbandonedTimeout="300"
             validationQuery="SELECT 1"/>

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 var="rs" dataSource="jdbc/sqlserver">
  select test from test
</sql:query>

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

    <c:forEach var="i" items="${rs.rows}">
      Text Message: <c:out value="${i.test}"/><br>
    </c:forEach>
  </body>
</html>

8) 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.

9) Simply create a jasper server JNDI datasource by going to: Datasources -> New Data source -> Type: JNDI data source. -> JNDI lookup is: /jdbc/sqlserver

Create your tables and test your report.

 

Fuente:

http://stackoverflow.com/questions/11402628/how-to-use-windows-authentication-to-sql-server-from-a-jasper-server-running-on

Publicado el 15 octubre, 2015 en jaspersoft. Añade a favoritos el enlace permanente. Deja un comentario.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: