Enabling Windows Authentication for SQL Server with SCM Web Interface (Harweb)

Products

• CA Harvest Software Change Manager - OpenMake Meister
• CA Harvest Software Change Manager

Issue/Introduction

The SCM documentation states that "if your DBMS is SQL Server, the web interface uses SQL Server Authentication for its database connection because the SQL Server JDBC driver supports SQL Server Authentication ONLY" (see Find the SQL Server Authentication User for Database User). Microsoft has created a work-around for this limitation which can be implemented with SCM to allow you to use Windows Authentication to access your SQL Server database with the SCM Web Interface (Harweb).

How to enable Windows Authentication for SQL Server with SCM Web Interface (Harweb)?

Environment

Harvest Software Change Manager 12.1 and higher
Windows OS, SQL Server database

Resolution

This example scenario shows how to do this when the Harvest broker, SQL Server, and Tomcat hosting Harweb are all on the same server, and in hdbsetup, the Harvest database connection was set up using Windows authentication.  The key to this configuration is to use the same Windows account when configuring how Harvest will connect to the database, and when configuring the userid that will be used to start the Tomcat and Harvest Broker services.

1. Determine which userid on the Windows server will be used for the Windows authentication.
2. In SQL Server Management Studio, add a new Login id for this "Windows authenticated" user
   On the "Server Roles" tab, ensure the "sysadmin" role is selected
   
   On the "User Mapping" tab ensure the user is mapped to the "harvest" database, and that the database roles "harvest_group" and "harvest_rep" are selected.
   
3. In Administrative Tools -> Services window, view the properties for both the Apache Tomcat service and the SCM Broker service.  Navigate to the "Log On" tab and configure to Log on as "This account" specifying the same Windows account and corresponding password.
   
4. Add the SQL Server JDBC's auth folder to the system path
   
5. Reinstall Harweb.  When providing the credentials for the CA Harvest SCM database tables, provide the same Windows account credentials.
   
6. In %CATALINA_HOME%\webapps\harweb\WEB-INF\harweb.cfg, in the JDBCConnectionURL, set "integratedSecurity=true" and add "trustServerCertificate=true;"
   

Additional Information

Microsoft has provided the following work-around to implement Windows Authentication to SQL Server via the JDBC 3.0 Driver. Details can be found here under the title "Building the Connection URL".  See especially the section on Connecting with integrated authentication on Windows.

More on installing Harweb is found here: Install CA Harvest SCM Web Interface

More information on hdbsetup is found here: The hdbsetup Database Configuration Utility