Document Purpose

This document captures the instructions for configuring connectivity to SQL Server in a Microsoft Enterprise deployment.  

JDBC Connectivity

The preferred method for connecting to a SQL Server database is via JDBC using the JDBC Query, JDBC Store and JDBC Execute nodes.  Although ODBC will work, JDBC is preferred over ODBC as ODBC will require a system administrator to create a DSN for each database connection thus increasing the maintenance required to administer the deployment. 

In order for the JDBC nodes to run, the following steps must be completed. 
The actual JAR file may vary for very old versions or very recent versions of SQL Server. 

1. Navigate to Microsoft’s JDBC Driver download
2. Scroll down to the driver download section
3. Choosing a version higher than 4.1 will ensure you have the necessary file downloaded for our software.
   NOTES:
   1. Be sure to scroll down to the correct version, as you'll likely only see up to v4.0 by default
   2. Be sure to note the location to which you extract the files.
   3. Files of type tar.GZ may be opened with freely available third party tools such as 7-zip.
      
      
      
4. Navigate to the extracted location and copy the sqljdbc41.jar file
5. Place the jar file into the <LAE Install>/lib/java/ext directory.

Configuring the JDBC Nodes

1. Configure the DbURL.
   e.g. DbURL=jdbc:sqlserver://hostnamOrIPAdddress\Instance;Database=DatabaseName

2. Configure the DbDriver
   e.g. microsoft.sqlserver.jdbc.SQLServerDriver

Windows Authentication Mode

In some situations, a client may require Windows Authentication to gain access to the database using predefined credentials.  In addition to the steps captured in the section “JDBC Connectivity”, 2 additional steps must be completed to complete the setup.   Follow the steps below:

1. Extract sqljdbc_auth.dll from the previously retrieved zip file in: \enu\auth\x64
2. Place the file in: <Install Directory>/lib/windows-x86-64
3. Add the additional parameter “integratedSecurity=true” to the DbURL parameter
   
   e.g. DbURL=jdbc:sqlserver://hostnamOrIPAdddress\Instance;Database=DatabaseName;integratedSecurity=true

SQL Server Authentication Modes

The configuration of the SQL server database can be checked by going into SQL server administration and searching on security properties.  If only Windows authentication is supported, the first bullet will be selected. 

In Windows Authentication mode, the server will pass the credentials of the username and password used to start the LAE service.  For example, in the picture below, building39\prasmussen will be assumed to be the pre-authenticated username sent to SQL server.

The user name can then be validated by reviewing the access permissions for the specified user.  Windows Authenticated users will be denoted by a “\” in the login.