MarcyNunns

Tech Tips: Using SQL Server with Windows Authentication for DevTest Solutions

Discussion created by MarcyNunns Employee on Aug 20, 2015

For Windows Authentication, you need an additional file called sqljdbc_auth.dll in the java.library.path.  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.  The dll needs to be copied to DEVTEST_HOME\lib\native. 

 

You can get this dll from the SQL Server JDBC driver:

https://www.microsoft.com/en-us/download/details.aspx?id=11774

 

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.

 

WA_image001.jpg

 

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>

WA_image002.jpg

 

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>

 

WA_image003.jpg

 

Additional Notes:

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.

 

 

 

 

Outcomes