MSSQL Windows Authentication: Setup Ephesoft Database without SQL Server Authentication

Applies to:

v4.0.2.x

Ephesoft will work with windows authentication for it’s primary databases. Fuzzydb Windows Authentication is not supported at this time.

Download:

http://download.ephesoft.com/Ephesoft_Product/Wiki_links/MSSQL_windowsAuthentication.zip

Note:

DB configuration files have been changed in v4.0.3.0 – Please Click this link for the updated instructions

Installation Steps:

Note: Please take back up of the all the files before modifying or deleting them.

1. Stop the Ephesoft server.
2. Rename the attachment to .zip from .abc extension and extract the zip (MS SQL Server Windows Authentication fix.zip) into some temporary location.
3. Copy extracted ntlmauth.dll file to <Ephesoft Installation directory>\Application\native\ directory. Use the 32 or 64 bit version depending on your sql version.

HINT: sql query to determine 32 or 64 bit version:
SELECT SERVERPROPERTY(‘Edition’)

4. Take backup and then edit below properties in dcma-db.properties file under <Ephesoft Installation directory>\Application\WEB-INF\classes\META-INF\dcma-data-access\ directory.

dataSource.driverClassName=net.sourceforge.jtds.jdbc.Driver
dataSource.dialect=com.ephesoft.dcma.da.common.EphesoftSQLServerDialect
dataSource.url=jdbc\:jtds\:sqlserver\://${dataSource.serverName};databaseName\=${dataSource.databaseName};domain\=${dataSource.domain}

5. Comment out below two property from dcma-db.properties file by prefixing the line with # as shown below. No need to give any user name and password here.

#dataSource.password=password
#dataSource.username=sa

6. Create a new property dataSource.domain in dcma-db.properties file as shown below.

dataSource.domain=<domain name>

7. To get domain name of a machine. Open a command prompt and type “set” and press enter. It will list out some property. Find ‘USERDOMAIN’ property in that list.
8. Take backup and then delete dcma-dao.xml file from <Ephesoft Installation directory>\Application\WEB-INF\classes\META-INF\dcma-data-access\.
9. Copy the extracted dcma-dao.xml to Ephesoft Installation directory>\Application\WEB-INF\classes\META-INF\dcma-data-access\.

If you prefer to manually make changes to your dcma-dao.xml file, you will need to comment out the following:

<!– <bean id=”passwordDecryptor” class=”com.ephesoft.dcma.encryption.core.PasswordDecryptor”>

<property name=”encryptedString” value=”${dataSource.password}”></property>

</bean> –>

<!– <prop key=”user”>${dataSource.username}</prop>

<prop key=”password”>#{passwordDecryptor.decryptedString}</prop> –>

10. Save and close the file. Start the Ephesoft Server.

To enable windows authentication for Ephesoft reporting please follow following steps:

1.      Stop the Ephesoft server or service.

2.      Take backup and then edit following properties in hibernate.cfg.xml file under <Ephesoft Installation directory>\Application\WEB-INF\classes\META-INF\dcma-reporting directory as mentioned below.

          <property name=”connection.url”>jdbc:jtds:sqlserver://localhost:1433;databaseName=report11111;domain=xyz;</property>

          <!– comment following–>

          <!–<property name=”connection.url”>jdbc:jtds:sqlserver://localhost:1433;databaseName=report11111;user=ephesoft;password=Password##;                       </property>

          <property name=”connection.username”>ephesoft</property>

          <property name=”connection.password”>Password##</property>–>

3.      Take backup and then edit following properties in hibernate-dcma.cfg.xml file under <Ephesoft Installation directory>\Application\WEB-INF\classes\META-INF\dcma-reporting directory as mentioned below.

<property name=”connection.url”>jdbc:jtds:sqlserver://localhost:1433;databaseName=ephesoft11111;domain=xyz;</property>

  <!– comment following–>

<!–<property name=”connection.url”>jdbc:jtds:sqlserver://localhost:1433;databaseName=ephesoft11111;user=ephesoft;password=Password##;

</property>

<property name=”connection.username”>ephesoft</property>

<property name=”connection.password”>Password##</property>–>

4.       Take backup and then edit following properties in dcma-report-db.properties  file under <Ephesoft Installation directory>\Application\WEB-INF\classes\META-INF\dcma-performance-reporting directory as mentioned below.

                          I.            comment out following line by adding hashtag(#) in front of the line.

#hibernate.connection.password=Password##

#hibernate.connection.username=ephesoft

                         II.            Modify hibernate.connection.url by appending domain component in the string.

hibernate.connection.url=jdbc:jtds:sqlserver://localhost:1433;databaseName= report11111;domain=xyz; 

5.      Start the Ephesoft Server or service

Following are the screenshots of hibernate.cfg.xml and hibernate-dcma.cfg.xml :

Edit hibernate.cfg.xml and hibernate-dcma.cfg.xml files as mentioned in above steps/screenshot and specify proper domain name as highlighted.