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 to collate.

Configure your openkm.properties

Edit the file $TOMCAT_HOME/openkm.properties

spring.jpa.hibernate.ddl-auto=create-only
spring.jpa.properties.hibernate.dialect=com.openkm.db.dialect.SQLServerDialect

Configure Tomcat data sources

Edit the file $TOMCAT_HOME/openkm.properties and enable the resources

spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.url=jdbc:sqlserver://localhost:1433;databaseName=okmdb
spring.datasource.username=openkm
spring.datasource.password=*secret*
spring.datasource.validationQuery=SELECT 1

Configure application login

Edit the file $TOMCAT_HOME/openkm.xml

authentication.openkm.database=true

Run application

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

Configure integrated security

You must add the "integratedSecurity=true" parameter to the JDBC connection URL to enable Integrated Security. For example:

spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.url=jdbc:sqlserver://localhost:1433;databaseName=okmdb;autoReconnect=true;integratedSecurity=true
spring.datasource.username=
spring.datasource.password=

You must also download the driver package from https://go.microsoft.com/fwlink/?linkid=2122434 and copy the sqljdbc_auth.dll file to TOMCAT_HOME/bin folder.

More info at:

• Microsoft SQL Server documentation

Troubleshooting error

Can't connect to SQL Server

Ensure you have TCP protocol enabled ( by default is disabled )

The connection was lost after some time.

We have observed in some SQL Server editions what seems to be new behavior that passed some hours; the connection is closed (it might be some extra configuration parameter in the latest SQL server editions that comes enabled by default or similar ). There's a new configuration attribute name autoReconnect that solves it.

spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.url=jdbc:sqlserver://localhost:1433;databaseName=okmdb;autoReconnect=true
spring.datasource.username=openkm
spring.datasource.password=*secret*
spring.datasource.validationQuery=SELECT 1

Not able to login into OpenKM

When first-time OpenKM login fails with the correct username and password ( okmAdmin / admin ), you should check the database configuration.

Check if the database is configured to be case-sensitive; it might be one reason you cannot log in.

Not able to connect TCP/ IP to localhostSQL, port 1433 when using instance in the JDBC connection URL

If you use the database instance name in the connection, you must use "\\" in the spring.datasource.URL configuration parameter. The sample below it use a database instance named SampleSQL.

spring.datasource.url=jdbc:sqlserver://localhost\\SampleSQL;databaseName=okmdb;autoReconnect=true

Deadlock issues

These errors appear in the log this way:

Modify the database to avoid these issues:

ALTER DATABASE okmdb SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE

 Source How to Solve Database Deadlock on Microsoft SQL Server.

SSL connection error

If you see this error:

The problem can be fixed adding the "encrypt=false" parameter to the JDBC URL:

This is due to a change in latest version of SQL Server Java JDBC driver.

More info at:

• Understanding encryption support
• Connecting with encryption

The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption

That may happen when the  SQL Server only allows  SSL connections.

 The problem can be fixed by adding the "encrypt=true" and "trustServerCertificate=true" parameters to the JDBC URL:

SQL Server alter compatibility level

To set SQL Server compatibility level 2012

ALTER DATABASE okmdb SET COMPATIBILITY_LEVEL = 110;