Configuring SQL Server

Database Creation

Create a database and user

Start SQL Server Management Studio Express and create a database called okmdb.

Select your appropriate database collate.

We checked with Modern_Spanish_CI_AI_KS, where CI indicates case-insensitive collation and AS indicates accent-sensitive collation.

To ensure the correct OpenKM functionality case-insensitive should be selected.

Configure your OpenKM.cfg

This configuration property should be set before the database creation. Once the database has been initialized don't modify it because it can damage your installation.

If your OpenKM installation has already been configured with another database you can't switch to another database by simply changing this property.

Edit the file $TOMCAT_HOME/OpenKM.cfg

hibernate.dialect=org.hibernate.dialect.SQLServerDialect
hibernate.hbm2ddl=create

Configure Tomcat data sources

Edit the file $TOMCAT_HOME/conf/server.xml and enable the resource named jdbc/OpenKMDS

<Resource name="jdbc/OpenKMDS" auth="Container" type="javax.sql.DataSource"
          maxActive="100" maxIdle="30" maxWait="10000" validationQuery="select 1"
          username="sa" password="***" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
          url="jdbc:sqlserver://localhost:1433;databaseName=okmdb"/>

Configure the attributes values named:

userName.
password.
URL (change to your hosts and port).

Can be only one resource named jdbc/OpenKMDS.

Configure application login

Edit the file $TOMCAT_HOME/OpenKM.xml

<security:authentication-manager alias="authenticationManager">
  <security:authentication-provider>
    <security:password-encoder hash="md5"/>
    <security:jdbc-user-service 
        data-source-ref="dataSource"
        users-by-username-query="select usr_id, usr_password, 1 from OKM_USER where usr_id=? and usr_active='T'"
        authorities-by-username-query="select ur_user, ur_role from OKM_USER_ROLE where ur_user=?"/>
  </security:authentication-provider>
</security:authentication-manager>

Check for the JDBC Driver

Go to $TOMCAT_HOME/lib and check for the JDBC sqljdbc4.jar Driver.

If it is not present, download the SQL Server JDBC driver from Microsoft SQL Server Driver Page and move it to $TOMCAT_HOME/lib.

The driver version number may be different due to an update.

Other JDBC driver for MS SQL Server we've tested is jdts.

Run the application

As a process in the OpenKM startup, the application will automatically create an empty database structure.

Once the tables are created, OpenKM will automatically change the hibernate.hbm2ddl property from create to none.

Troubleshooting

You can not connect to MS SQL Server

Ensure you've got TCP protocol enabled (this is disabled by default)

Connection lost after some time

We have observed a new behaviour in some SQL Server editions where the connection is closed after a few hours (This may be some extra configuration parameter in the latest SQL server editions that comes enabled by default or something similar). There's a new configuration attribute named autoReconnect to solve it.

<Resource name="jdbc/OpenKMDS" auth="Container" type="javax.sql.DataSource"
          maxActive="100" maxIdle="30" maxWait="10000" validationQuery="select 1"
          username="sa" password="***" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
          url="jdbc:sqlserver://localhost\SQL;databaseName=okmdb;autoReconnect=true"/>