Configuring the loader controller on Windows for Kerberos-enabled Microsoft SQL Server

Configuring the loader controller on a Windows host with IBM® Spectrum Symphony Advanced Edition for a Kerberos-enabled Microsoft SQL Server involves configuring Microsoft SQL Server, and installing and configuring the loader controller JDBC. If you expect to use native authentication as the authentication scheme, also update the loader controller and purger configuration.

Before you begin

Microsoft Active Directory (AD) must be installed and configured on a Windows management host in your cluster. Ensure that AD is configured as the Kerberos Key Distribution Center (KDC).
Microsoft SQL Server must be installed and configured on a Windows management host.
IBM Spectrum Symphony Advanced Edition and Kerberos must be installed and configured on a Windows management host, and Kerberos authentication must be enabled for Microsoft SQL Server.

Procedure

Configure Microsoft SQL Server:
1. Configure the Microsoft SQL Server service to register SPNs dynamically or register SPNs manually. Refer to step 3 in article 319723 of the Microsoft support database.
2. Add the expected domain account in AD to Microsoft SQL Server as Windows authentication.
3. Ensure that you are using Kerberos as a Windows authentication method when you create a remote connection to an instance of Microsoft SQL Server. Refer to article 909801 in the Microsoft support database.
Install and configure the loader controller JDBC driver:
1. Download and install JDBC driver 4.0 for Microsoft SQL Server. The JDBC driver (sqljdbc41.jar for JRE 1.7 and sqljdbc42.jar for JRE 1.8) are available at http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774.
2. Copy the JDBC .jar file to the reporting framework library path (%PERF_TOP%\%PERF_VERSION%\lib).
3. Ensure that the Windows registry entry AllowTgtSessionKey is added to the following registry sub-key, with a value of 1:
```
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\Kerberos\Parameters
```
4. Initialize the ticket-granting-ticket (TGT) cache by using the kinit command or by logging on to the domain.
If you expect to use native authentication as the authentication scheme, update the loader controller and purger configuration as follows:
1. Log on the Windows loader controller host as the domain account you expect to use and ensure that the loader controller and purger are configured to use the domain account in advanced workload execution mode.
2. Stop the loader controller and purger services on EGO:
```
% egosh service stop plc purger
```
3. Disable data collection for your MapReduce data loaders by editing the MapReduce loader controller configuration file (%EGO_CONFDIR%\..\..\perf\conf\plc\plc_pmr.xml) and setting Enable="false" for the following data loaders:
```
<DataLoader Name="hostmetricsloader" Interval="600" Enable="false" LoadXML="dataloader/pmrresourcemetrics.xml" /> 
<DataLoader Name="pmrmetrcisloader" Interval="60" Enable="false" LoadXML="dataloader/mapreducemetrcis.xml" /> 
<DataLoader Name="pmrtaskcountersloaderhist" Interval="300" Enable="false" LoadXML="dataloader/pmrtaskcountershist.xml" /> 
<DataLoader Name="pmrsessioncountersloaderhist" Interval="300" Enable="false" LoadXML="dataloader/pmrsessioncountershist.xml" />
```
4. Use the dbconfig tool to configure and verify the connection parameters for Microsoft SQL Server.
```
% dbconfig
```
5. Enter the following values in the Data Source Properties window:
  - User ID: Leave empty.
  - Password: Leave empty.
  - Cipher: Leave to the default (DES56).
  - JDBC driver: Set to com.microsoft.sqlserver.jdbc.SQLServerDriver.
  - JDBC URL: Set to jdbc:sqlserver://host[:port];databaseName=database_name;integratedSecurity=true;authenticationScheme=NativeAuthentication. For example:
    jdbc:sqlserver://db:1433;databaseName=app;integratedSecurity=true;authenticationScheme=NativeAuthentication
6. Click Test.
7. If the connection test passes, click OK.
8. Start EGO services:
```
% egosh service start plc purger
```