Difference between revisions of "SQL Server - OpenKM 6.2"

From OpenKM Documentation
Jump to: navigation, search
(→‎More info)
 
(10 intermediate revisions by 2 users not shown)
Line 7: Line 7:
 
{{Note|'''OpenKM 6.2''' has been verified to run with '''SQL Server 2005''' using the JDBC driver '''sqljdbc4.jar''' under tomcat-7.0.27}}
 
{{Note|'''OpenKM 6.2''' has been verified to run with '''SQL Server 2005''' using the JDBC driver '''sqljdbc4.jar''' under tomcat-7.0.27}}
  
 +
In this OpenKM release you can create the databases automatically configuring the '''hibernate.dialect''' and '''hibernate.hbm2ddl''' properties in [[OpenKM.cfg]].
 +
 +
<source lang="java">
 +
hibernate.dialect=org.hibernate.dialect.SQLServerDialect
 +
hibernate.hbm2ddl=create
 +
</source>
 +
 +
More info about this at:
 +
* [http://docs.jboss.org/hibernate/core/3.3/reference/en/html/session-configuration.html#configuration-optional-dialects Hibernate: SQL Dialects]
 +
 +
== Database creation ==
 +
Starting with OpenKM 6.0, only one databases is needed:
 +
okmdb
 +
 +
[http://www.microsoft.com/downloads/details.aspx?FamilyID=c243a5ae-4bd1-4e3d-94b8-5a0f62bf7796&DisplayLang=en Start SQL Server Management Studio Express] and create the database called okmdb.
 +
 +
 +
[[file:Mssql1.jpg|center]]
 +
 +
 +
Select your appropiate database collate.
 +
 +
 +
[[file:Mssql2.jpg|center]]
 +
 +
== Tomcat datasources ==
 +
Edit file $TOMCAT_HOME/conf/server.xml
 +
 +
<source lang="xml">
 +
<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"/>
 +
</source>
 +
 +
== Login configuration ==
 +
Edit file $TOMCAT_HOME/OpenKM.xml
 +
 +
<source lang="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>
 +
</source>
 +
 +
 +
== Troubleshooting, error you can not connect to MS SQL Server ==
 +
Ensure you've got TCP protocol enabled ( by default is disabled )
 +
 +
[[file:mssql5.jpg|center]]
 +
 +
== More info ==
 +
* [http://blogs.msdn.com/b/walzenbach/archive/2010/04/14/how-to-enable-remote-connections-in-sql-server-2008.aspx How to enable remote connections in SQL Server 2008?]
 +
* [http://kbase.gfi.com/showarticle.asp?id=KBID002804 How do I configure SQL Server 2005/2008 to accept SQL Authentication?]
  
 
[[Category: Installation Guide]]
 
[[Category: Installation Guide]]

Latest revision as of 19:38, 6 March 2014

Download SQL Server JDBC driver from Miscrosof SQL Server Driver Page and move it to $TOMCAT_HOME/lib


Nota clasica.png Other JDBC driver for MS SQLServer we've tested is jdts.


Nota clasica.png OpenKM 6.2 has been verified to run with SQL Server 2005 using the JDBC driver sqljdbc4.jar under tomcat-7.0.27

In this OpenKM release you can create the databases automatically configuring the hibernate.dialect and hibernate.hbm2ddl properties in OpenKM.cfg. 

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

More info about this at:

Database creation

Starting with OpenKM 6.0, only one databases is needed: 

okmdb

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


Mssql1.jpg


Select your appropiate database collate.


Mssql2.jpg

Tomcat datasources

Edit file $TOMCAT_HOME/conf/server.xml 

<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"/>

Login configuration

Edit 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>


Troubleshooting, error you can not connect to MS SQL Server

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

Mssql5.jpg

More info

Retrieved from "https://www.openkm.com/wiki/index.php?title=SQL_Server_-_OpenKM_6.2&oldid=11325"