So, I found out why the ticketRegistryCleaner didn't want to work with SQL jdbc driver. Every time the Cleaner ran, Jdbc was throwing SQLServerException: Cannot insert an explicit value into a timestamp column (in the tomcat7-stdout log). The reason for this was that the LOCK table I created in the ticketRegistry database had a column EXPIRATION_DATE with a timestamp type where it should be a datetime type (since MSSQL timestamp is not the same as MySQL timestamp). As soon as I changed EXPIRATION_DATE to the datetime, ticketRegistryCleaner started working as expected.
Hope this helps. Kostya -----Original Message----- From: Harry Hoffman [mailto:[email protected]] Sent: Thursday, April 28, 2011 10:12 AM To: Makarov, Konstantin V. Subject: RE: [cas-user] CAS 3.4.6 and JpaTicketRegistry w/MSSQL Hi Kostya, I finally did get things working. I started from scratch and used the cas-server-uber-webapp-3.4.7.war which seems to contain everything that cas supports (including the versions needed to get the jdbc connectors working correctly). I had to copy commons-dbcp-1.4.jar, commons-pool-1.5.5.jar, and sqljdbc4.jar (from microsoft's site) to WEB-INF/lib under cas. In WEB-INF/cas.properties I added the following lines: database.hibernate.dialect=org.hibernate.dialect.SQLServer ticket.cleaner.database.platform=SQLServer And in WEB-INF/spring-configuration/ticketRegistry.xml I have: <?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans"; xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; xmlns:p="http://www.springframework.org/schema/p"; xmlns:tx="http://www.springframework.org/schema/tx"; xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.0.xsd";> <description> Configuration for the default TicketRegistry which stores the tickets in-memory and cleans them out as specified intervals. </description> <!-- Ticket Registry --> <bean id="ticketRegistry" class="org.jasig.cas.ticket.registry.JpaTicketRegistry"> <constructor-arg index="0" ref="entityManagerFactory" /> </bean> <bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"> <property name="dataSource" ref="dataSource"/> <property name="jpaVendorAdapter"> <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"> <property name="generateDdl" value="true"/> <property name="showSql" value="true" /> </bean> </property> <property name="jpaProperties"> <props> <prop key="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</prop> <prop key="hibernate.hbm2ddl.auto">update</prop> </props> </property> </bean> <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager" p:entityManagerFactory-ref="entityManagerFactory" /> <tx:annotation-driven transaction-manager="transactionManager"/> <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" p:driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" p:url="jdbc:sqlserver://sql-2005-mydomain.edu:3291;databaseName=CAS" p:password="password" p:username="user" /> <!--Quartz --> <!-- TICKET REGISTRY CLEANER --> <!-- <bean id="ticketRegistryCleaner" class="org.jasig.cas.ticket.registry.support.DefaultTicketRegistryCleaner" p:ticketRegistry-ref="ticketRegistry" p:lock-ref="cleanerLock" /> <bean id="cleanerLock" class="org.jasig.cas.ticket.registry.support.JdbcLockingStrategy" p:uniqueId="cas.mydomain.edu" p:platform="SQLServer" p:applicationId="cas-ticket-registry-cleaner" p:dataSource-ref="dataSource" /> <bean id="ticketRegistryCleanerJobDetail" class="org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryB ean" p:targetObject-ref="ticketRegistryCleaner" p:targetMethod="clean" /> <bean id="periodicTicketRegistryCleanerTrigger" class="org.springframework.scheduling.quartz.SimpleTriggerBean" p:jobDetail-ref="ticketRegistryCleanerJobDetail" p:startDelay="20000" p:repeatInterval="1800000" /> --> <!-- <bean id="jobDetailTicketRegistryCleaner" class="org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryB ean" p:targetObject-ref="ticketRegistryCleaner" p:targetMethod="clean" /> <bean id="triggerJobDetailTicketRegistryCleaner" class="org.springframework.scheduling.quartz.SimpleTriggerBean" p:jobDetail-ref="jobDetailTicketRegistryCleaner" p:startDelay="20000" p:repeatInterval="5000000" /> --> </beans> I still have not been able to get the ticketRegistryCleaner working so it's commented out for the moment. Good luck, and let me know if you have any questions. This has been a real PITA ;-) Cheers, Harry -----Original Message----- From: Makarov, Konstantin V. [mailto:[email protected]] Sent: Thursday, April 28, 2011 10:58 AM To: [email protected] Subject: RE: [cas-user] CAS 3.4.6 and JpaTicketRegistry w/MSSQL Harry, Did you get the JpaTicketRegistry working with MSSQL? I'm starting on the same path and was wondering where are you at? Thank you, Kostya -----Original Message----- From: Harry Hoffman [mailto:[email protected]] Sent: Monday, April 18, 2011 11:52 AM To: [email protected] Subject: [cas-user] CAS 3.4.6 and JpaTicketRegistry w/MSSQL Hi All, I'm going through the docs [1] on using JpaTicketRegistry with MSSQL and I'm wondering if anyone has a working example they can share? Or can I just 's/MySQL/MSSQL/' for the examples and expect everything to work? Cheers, Harry [1] https://wiki.jasig.org/display/CASUM/JpaTicketRegistry -- You are currently subscribed to [email protected] as: [email protected] To unsubscribe, change settings or access archives, see http://www.ja-sig.org/wiki/display/JSG/cas-user -- You are currently subscribed to [email protected] as: [email protected] To unsubscribe, change settings or access archives, see http://www.ja-sig.org/wiki/display/JSG/cas-user
