Using SQL Server with Windows Authentication for DevTest Solutions

Products

• CA Application Test
• CA Continuous Application Insight (PathFinder)
• Service Virtualization

Issue/Introduction

For Windows Authentication, you need an additional file called sqljdbc_auth.dll in the LISA_HOME/jre/bin folder

This comes with the JDBC SQL Server drivers download you get from Microsoft.  

The dll you use depends on the JVM you are running, 32-bit or 64-bit.

Cause

N/A

Environment

All supported DevTest releases and platforms.

Resolution

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

You also need to add the integratedSecurity=true option at the end of your URL connection string.

The SQL Server Instance is set for Windows Authentication only, if needing SQL Server and Windows Authentication mode, choose the other Server authentication.  Need to determine what authentication is needed before the database instance is created.

The user and password is your Windows logon and you will have to specify your domain in the userid.

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://NUNMA04W7A.ca.com:1433;databaseName=ED801DB;integratedSecurity=true
dradis.db.pool.dradis.user=TANT-A01\\nunma04
dradis.db.pool.dradis.password=<password>

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://NUNMA04W7A:1433;databaseName=REG801DB;integratedSecurity=true
lisadb.pool.common.user=TANT-A01\\nunma04
lisadb.pool.common.password=<password>

 

 

Additional Information

The use of SQL server is complicated by many factors over and above other databases.

If you wish to start the DevTest components as services and use Windows authentication then you need the following to be true

1) the service account user MUST be network authenticated (that is it must be an AD user) 
2) the SQL Server must be in the same domain as the user, or one that a trust relationship exists 
3) The JDBC connection string must have the form jdbc:sqlserver://;servername=server_name;integratedSecurity=true

If points 1 and 2 cannot be met then you will need to use username/password authentication. To do this, the database must allow "SQL Server and Windows Authentication mode" - otherwise known as mixed mode. See https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/change-server-authentication-mode?view=sql-server-2017&viewFallbackFrom=sql-server-2014

In this case the integratedSecurity clause will be omitted from the JDBC connection string, and the username and password will need to be specified.

You will need to start the all the DevTest services with the service account since Local System account will not be recognized by the SQL Server.

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.