How to enable DevTest 10.8 to use Windows authentication/Integrated Security when connecting to SQL Server databases

Products

• Service Virtualization

Issue/Introduction

What steps are required to use Windows authentication (Integrated Security) to allow proper connection to the SQL Server external databases for IAM, Enterprise dashboard, and Registry?

Environment

Service Virtualization 10.8

Resolution

When configuring DevTest to use external databases, the DBA must create three separate databases/schemas. One database/schema for the following components:

• Identity Access Manager
• Enterprise Dashboard
• Registry

The same Windows Account can be used to access all three databases. 

When using a Windows Account (integrated security), the Windows services for the DevTest Components, must be configured to start under the same Windows Account used to access the external SQL Server databases. It's recommended to configure the DevTest Identity Access Manager service, and the DevTest Enterprise Dashboard service to start Automatically. All other DevTest services should be configured to use the Automatic (Delayed Start).

Identity Access Manager:

1. In order to use the Windows Integrated Security/Windows authentication for DevTest 10.8 IAM, place a copy of the mssql-jdbc_auth-12.4.2.x64.dll file inside the %DevTest_Home%\jre\bin directory.
   
   
2. In the %DevTest_Home%\IdentityAccessManager\iam.properties file, make sure the following properties for the MS SQL Server connection are correct:

   iam.db.vendor=mssql
   
   iam.db.url=jdbc:sqlserver://<hostname>:<port>;databaseName=<IAM database name>;integratedSecurity=true;encrypt=false;trustServerCertificate=true
   
   iam.db.user=<host or domain name>\<username>
   
   iam.db.password=<leave this property blank>

3. Once the proper authentication DLL file is in place, and the SQL Server connection profile is properly configured, start the Identity Access Manager service, and check the %DevTest_Home%\IdentityAccessManager\data\log\server.log to ensure the IAM service connected to the SQL Server database, and started without error.

Enterprise Dashboard:

1. In order to use the Windows Integrated Security/Windows authentication for DevTest 10.8 Enterprise Dashboard, place a copy of the mssql-jdbc_auth-8.2.0.x64.dll file inside the %DevTest_Home%\jre\bin directory.
   
   
2. In the %DevTest_Home%\dradis.properties file, make sure the following properties are properly set:

   dradis.db.internal.enabled=false

   For the MS SQL Server connection:

   dradis.db.url=jdbc:sqlserver://<hostname>:<port>;databaseName=<Enterprise Dashboard database name>;integratedSecurity=true;encrypt=false;trustServerCertificate=true
   
   dradis.db.user=<host or domain name>\<username>
   
   dradis.db.password=<leave this property blank>

3. Once the proper authentication DLL file is in place, and the SQL Server connection profile is properly configured, start the Enterprise Dashboard service, and check the %DevTest_Home%\lisatmp_10.8.0\enterprisedashboard.log to ensure the Enterprise Dashboard service connected to the SQL Server database, and started without error.

Registry:

1. The Registry uses the same Windows authentication DLL file as Enterprise dashboard; mssql-jdbc_auth-8.2.0.x64.dll file, which should already be in the %DevTest_Home%\jre\bin directory.
   
   
2. In the %DevTest_Home%\site.properties file, make sure the following properties are properly set:

   lisadb.internal.enabled=false

   For the MS SQL Server connection:

   lisadb.pool.common.url=jdbc:sqlserver://<hostname>:<port>;databaseName=<Registry database name>;integratedSecurity=true;encrypt=false;trustServerCertificate=true
   lisadb.pool.common.user=<host or domain name>\<username>
   lisadb.pool.common.password=<leave this property blank>

3. Once the proper authentication DLL file is in place, and the SQL Server connection profile is properly configured, start the Registry service, and check the %DevTest_Home%\lisatmp_10.8.0\registry.log to ensure the Registry service connected to the SQL Server database, and started without error.

After the IAM, Enterprise Dashboard, and Registry services are running, the remaining DevTest components can be started as you normally would do.

NOTE: When running multiple DevTest servers, all servers need to have a copy of the mssql-jdbc_auth-8.2.0.x64.dll file in the %DevTest_Home%\jre\bin directory. Otherwise, the connection to the Registry will time out due to the database connection failing. 

Additional Information

Note: For your convenience, the required authentication DLL files have been attached to this KB.