Configure Kofax RPA to connect to a Microsoft SQL Database using Windows Authentication

Issue

Is it possible to connect Kofax RPA to a Microsoft SQL Server by using Windows Authentication?

Solution

yes, starting with version 8.1.7, it is possible to connect Kofax RPA to a Microsoft SQL Server running in Windows Authentication mode. Windows Authentication works by obtaining a security token from the current user and passing this token to SQL Server for validation. In the context of RPA the “current user” is the user who is running the program that is connecting to the SQL Server; this is especially important when running RoboServer as a service.

The following steps have to be followed to configure RPA to use Windows Authentication:

1. Create a new Database Type in Management Console
   The assisted connection properties (i.e. the database types already created in the Management Console) cannot be used for a connection using Windows Authentication. Create a new Database Type (a copy of the Microsoft SQL Server connection) then use the JDBC URL connection properties and make sure to add ;integratedSecurity=trueto the JDBC URL.
   See the attached sample screenshot (the settings are the same for any version of Management Console).
2. If a sqljdbc driver (jar file) has been uploaded to Management Console -> Admin -> Settings -> Database drivers, remove it. Drivers for other types of databases don’t have to be removed.
3. Copy the sqljdbc driver (jar file) to the lib folder of the RPA installation folder on all machines that will be using the Windows Authentication connection (machines where Design Studio, RoboServer or a Management Console in embedded mode might be running).
4. Obtain the sqljdbc_auth.dll file DLL that corresponds to the version of RPA you're using.
   The sqljdbc_auth.dll file is distributed by Microsoft with their JDBC driver. In the SQL Server JDBC Driver package Microsoft distributes, there is an auth\ directory containing three subdirectories, IA64\, x64\, and x86\. You should copy the sqljdbc_auth.dll file from the appropriate directory (depending on the RPA version you’re using and implementation) to the specific folder as follows:
   • For all RoboServers --> nativelib\directory in the RPA installation directory.
   • For all Design Studio --> nativelib\directory in the RPA installation directory.
   • If Management Console runs in embedded mode -->nativelib\directory in the RPA installation directory.
   • If Management Console runs on a standalone Tomcat --> bin\directory of the Tomcat installation directory
      
5. In Management Console > Admin > RoboServers, open Cluster settings and create a new mapping using the new type (created at step 1 above).

NOTE: When using Windows authentication to connect to a SQL database, the user and password entered in the mapping configuration are not used. RPA uses the user running the process to connect to the database. E.g. in the case of a RoboServer or Design Studio, it will be the user running the RoboServer service or Design Studio. in the case of the Management Console deployed into Tomcat it will be the user running the Tomcat process (service, if any).

You can verify you have the appropriate version installed using the "Test Connection" button. If other settings for your SQL Server connection are correctly configured but you have the authentication DLL installed for the wrong architecture, you'll see an error message saying "This driver is not configured for integrated authentication".

Keep in mind that this message might occur also when the authentication DLL is correct but there’s a sqljdbc driver file in Management Console (when step #2 above was skipped).
In this case, you will also see the error below in the Management Console /RoboServer logs:

Native Library <Path>\sqljdbc_auth.dll already loaded in another classloader.

The case of Management Console deployed into Tomcat using a Windows Authentication to connect to the platform database

Tomcat can also be configured to connect to the Microsoft SQL platform database using Windows Authentication. This is done using ; integratedSecurity=true in the url connection string in the Management Console XML context file.

Sqljdbc jar file has to be placed in Tomcat’s lib folder (or the application’s lib folder).

Sqljdbc_auth.dll has to be placed in Tomcat’s bin folder.

For more information about configuring Tomcat to use Windows Authentication, please consult Apache Tomcat’s documentation. And for more information about deploying RPA Management Console in Tomcat, please consult the Administrator Guide for your version of RPA.

Even when Management Console is deployed into Tomcat, it is very important to follow the steps above so that the RoboServer(s) and Design Studio(s) will be able to use the database mappings created in Management Console.

Keywords: Kapow, Robotic Process Automation, SQL, Windows authentication, RPA

 

Level of Complexity 

Easy

 

Applies to  

Product	Version	Build	Environment	Hardware
Kofax RPA	all

References

Add any references to other internal or external articles

​