Configuring DEVTEST to use an External SQL Server Database (also with integratedSecurity)

Products

• Service Virtualization

Issue/Introduction

When upgrading to 10.6 or 10.7 version and trying to set up DEVTEST to use an External SQL Server Database (with integrated Security), what are the necessary steps need to take?

Environment

All supported DevTest releases,

Resolution

Here is what need to be changed for IAM or DEVTEST to use a SQL Server database instead of the Embedded database:

1. DBA will have to create a new database without any tables for IAM and DevTest components ( ED and Registry)
2. For 10.6 IAM, Create a new folder DEVTEST_HOME/IdentityAccessManager/database/drivers. Copy  sqljdbc4-4.0.jar from DEVTEST_HOME/lib/shared folder.  For 10.7, the jars are available in DEVTEST_HOME/IdentityAccessManager/database/drivers folder. 
3. Update DEVTEST_HOME/IdentityAccessManager/iam.properties file to point the  iam.db.jdbc.driver.path to the jdbc jar location in step 2.
4. Add these lines to the file: (USER must have db_owner to create the tables)

 iam.properties file 

iam.db.vendor=mssql
iam.db.url=jdbc:sqlserver://[HOSTNAME]:1433;databaseName=[DatabaseName]
iam.db.user=[USER]

iam.db.password=[PASSWORD]iam.db.jdbc.driver.class=com.microsoft.sqlserver.jdbc.SQLServerDriver

 iam.db.jdbc.driver.path=${IAM_HOME}/database/drivers/sqljdbc4-4.0.jar  for 10.6

 iam.db.jdbc.driver.path=${IAM_HOME}/database/drivers/mssql-jdbc-8.2.0.jre8.jar  for 10.7

Note: When using Windows Integrated Security the URL should look like: jdbc:sqlserver://[HOSTNAME]:1433;integratedSecurity=true;databaseName=[DatabaseName].  

*** When using Integrated Security, do not comment out  'iam.db.password'  , just provide a dummy password 

5. For Enterprise Dashboard,  the jar file is available in DEVTEST_HOME/lib/dradis/sqljdbc4-4.0.jar folder or mssql-jdbc-8.2.0.jre8.jar (10.7). 

Here is an example of database details for the Enterprise Dashboard:  (dradis.properties)
## ==================================================================================
## MS SQL Server using Windows Authentication
## ==================================================================================
dradis.db.pool.dradis.driverClass=com.microsoft.sqlserver.jdbc.SQLServerDriver
dradis.db.pool.dradis.url=jdbc:sqlserver://[HOSTNAME]:1433;databaseName=[DBNAME];integratedSecurity=true
dradis.db.pool.dradis.user=[USER]
dradis.db.pool.dradis.password=<password>

6. For Registry, the jar file is available in DEVTEST_HOME/lib/Shared  sqljdbc4-4.0.jar (10.6) or or mssql-jdbc-8.2.0.jre8.jar (10.7). 

Details for the Registry:  (site.properties)

## ==================================================================================
## MS SQL Server using Windows Authentication
## ==================================================================================
lisadb.pool.common.driverClass=com.microsoft.sqlserver.jdbc.SQLServerDriver
lisadb.pool.common.url=jdbc:sqlserver://[HOSTNAME]:1433;databaseName=[DBNAME];integratedSecurity=true
lisadb.pool.common.user=[USER]
lisadb.pool.common.password=<password>

The dll you use depends on the JVM you are running, 32-bit or 64-bit.  The dll needs to be copied to DEVTEST_HOME/jre/bin folder or devtest\lib\native

For DevTest 10.6 do the below:

- Download sqljdbc_auth.dll from any Microsoft site. 
- Copy sqljdbc_auth.dll into the java library classpath:  (i.e. java.library.path = c:\program files\ca\devtest\jre\bin => Identified based on server.log)

 

 

For DevTest 10.7 do the below:

- Download mssql-jdbc_auth-8.2.0.x64.dll from any Microsoft site. 
- Copy mssql-jdbc_auth-8.2.0.x64.dll into the java library classpath:  (i.e. java.library.path = c:\program files\ca\devtest\jre\bin => Identified based on server.log)

7. Stop all DevTest components.
8. Delete all the log files in DEVTEST_HOME/IdentityAccessManager/standalone/log  and DEVTEST_HOME/lisatmp10.x folder
9. Start all DevTest components.
10. Login to IAM ,ED and Portal  to verify.  

Where DEVTEST_HOME is the home folder of where DevTest is installed.

If the variable ${IAM_HOME} is not recognized, replace it with the direct patch of DEVTEST_HOME, where sqljdbc4-4.0.jar/mssql-jdbc-8.2.0.jre8.jar are the SQL Server JDBC drivers we deliver with DevTest.

Additional Information

There is no version of the integrated authentication dll that will work from Linux. Currently integrated authentication is Windows only.  Will have to do Server Authentication only.