Service Virtualization

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

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

    Posted 08-20-2015 09:40 AM

    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.