Secure DB2 Connections: Best Practices for Storing Connection Strings in Host Integration Server

Table of Contents

This article provides a comprehensive guide on how to securely store DB2 connection strings within Enterprise Single Sign-On (ESSO), leveraging the capabilities outlined in KB 2499828 for Microsoft Host Integration Server (HIS) 2010, or subsequent Cumulative Updates. Effectively managing and securing connection strings is paramount for robust and secure database access, especially in environments utilizing Host Integration Server. By utilizing ESSO, organizations can centralize and protect sensitive connection information, enhancing security and simplifying administration.

Summary¶

This guide details the process of saving DB2 connection strings within Enterprise Single Sign-On, utilizing hotfix KB 2499828 for HIS 2010, or 2010 Cumulative Update 1. This enhanced functionality seamlessly integrates with existing tools and Data Providers for DB2. The procedure encompasses a series of essential steps designed to establish a secure and efficient connection management system. These steps are crucial for ensuring that DB2 connections are not only readily available but also protected against unauthorized access.

The fundamental steps involved are as follows:

1. Add ESSO to an HIS 2010 installation: Integrate Enterprise Single Sign-On into your existing Host Integration Server 2010 infrastructure.
2. Configure ESSO for use with HIS 2010: Establish the necessary configurations to enable ESSO to function effectively within the HIS 2010 environment.
3. Apply HIS 2010 hotfix KB 2499828/Cumulative Update 1: Install the required update to enable the functionality for secure DB2 connection string storage in ESSO.
4. Define an ESSO Affiliate Application for Windows-initiated SSO: Create an Affiliate Application within ESSO to facilitate Windows-initiated Single Sign-On.
5. Define a DB2 connection string for Windows-initiated SSO: Configure a DB2 connection string specifically for use with Windows-initiated SSO.
6. Define an ESSO Affiliate Application for storing DB2 connection string: Establish a dedicated ESSO Affiliate Application designed for the secure storage of DB2 connection strings.
7. Define a DB2 connection that retrieves connection string from SSO: Configure a DB2 connection to dynamically retrieve its connection string from the securely stored location within ESSO.
8. Using SSO Command Line to automate SSO configuration: Explore the use of the SSO Command Line interface to streamline and automate the configuration of Enterprise Single Sign-On.

Prerequisites¶

Before commencing with the outlined procedures, ensure that the following prerequisites are in place:

• HIS 2010: Microsoft Host Integration Server 2010 must be installed and operational within your environment.
• hotfix KB 2499828: The specific hotfix KB 2499828 or a later Cumulative Update for HIS 2010 is required to enable the described functionality.
• Enterprise Single Sign-On V5: Enterprise Single Sign-On version 5 or a compatible later version must be installed and accessible.
• IBM DB2 server: An operational IBM DB2 server instance that you intend to connect to is necessary for testing and implementation.

Adding ESSO to an HIS 2010 Installation¶

To leverage Windows-initiated SSO and securely store DB2 connection details for shared access across multiple client programs and users, access to an ESSO installation is essential. If ESSO was not initially included during the HIS 2010 installation, it can be readily added by rerunning the HIS 2010 setup program. This process is straightforward and allows for seamless integration of ESSO into the existing HIS environment.

Follow these steps to add ESSO to your HIS 2010 installation:

1. Locate Installation Files: Open Windows Explorer and navigate to the folder containing the HIS 2010 installation files. This is typically the location where you initially extracted or downloaded the HIS 2010 installation media.
2. Run Setup.exe: Double-click the Setup.exe file. When the User Account Control prompt appears, click Yes to grant administrative privileges and proceed with the installation process. The Host Integration Server Setup Autorun dialog will be displayed.
3. Launch Installation Wizard: Within the Installation section of the Autorun dialog, click the hyperlink representing the HIS product name. This action will launch the HIS 2010 Installation Wizard, guiding you through the component modification process.
4. Select Modify Option: In the Program Maintenance dialog, choose the Modify option and then click Next. This indicates that you intend to add or remove components rather than performing a fresh installation or removal.
5. Choose Enterprise Single Sign-On: In the Component Installation dialog, expand the Server items listed under the Available components. Scroll down the list until you locate Enterprise Single Sign-On. Click the checkbox next to Enterprise Single Sign-On to select it for installation, and then click Next.
6. Review Summary: The Summary dialog will appear, displaying the ESSO Prerequisites (ESSO runtime Server and Administration tools) and the HIS 2010 Components to be installed (Enterprise Single Sign-On feature set). Carefully review this information to ensure that you are adding the correct components. Click Install to proceed with the installation.
7. Monitor Installation Progress: The Installation Progress dialog will display the status of the installation. Monitor this process for any errors. If any errors occur during the installation, the Installation Failed dialog will be displayed. In case of failure, click the Logfile hyperlink to access the installation log file. Provide this log file to your network administrator or client administrator for troubleshooting.
8. Complete Installation: Upon successful installation, the Installation Completed dialog will appear. Optionally, you can click Check for Updates to ensure you have the latest updates. Verify that the checkbox for Launch Configuration Wizard is selected if you intend to configure ESSO immediately after installation. Click Finish to close the installation wizard.

Configuring ESSO for Use with HIS 2010¶

Configuring Enterprise Single Sign-On as a feature within HIS 2010 is a crucial step for enabling Windows-initiated SSO and securely storing DB2 connection strings for shared use. This configuration process establishes the necessary infrastructure for ESSO to operate effectively within the HIS environment, ensuring secure and centralized management of credentials.

Follow these steps to configure ESSO for use with HIS 2010:

1. Access HIS 2010 Configuration: Launch the HIS 2010 Configuration Tool. This can typically be found in the Start menu under All Programs, then HIS 2010, and finally Configuration Tool. When prompted by User Account Control, click Yes to grant administrative privileges. The HIS 2010 Configuration dialog will appear.
2. Select Enterprise SSO: In the scope pane on the left side of the HIS 2010 Configuration dialog, locate and click Enterprise SSO. This will display the configuration options for Enterprise Single Sign-On.
3. Enable Enterprise Single Sign-On: In the Enterprise Single Sign-On dialog, click the checkbox labeled Enable Enterprise Single Sign-On. After enabling, warning glyphs may appear, indicating that ESSO requires configuration of the data store (SQL Server database) and Windows service instance. These warnings are expected at this stage and will be addressed in subsequent steps.
4. Configure Data Store and Service Account: Select the option Create a new SSO system. Enter the name of the SQL Server that will host the ESSO Data store in the Server Name field. Click the ellipse button (…) to specify a Windows service Account that the ESSO runtime service instance will run as.
5. Specify Service Account Credentials: In the User Credentials dialog, enter the User name and Password for the Windows account that will be used to run the ESSO runtime service instance. Click OK to save the credentials. It is recommended to use a dedicated service account with appropriate permissions for this purpose.
6. Navigate to Secret Backup: In the scope pane of the HIS 2010 dialog, click Enterprise SSO Secret Backup. This section is crucial for securing the ESSO master secret key.
7. Configure Secret Backup: In the Enterprise SSO Secret Backup dialog, enter a strong password in both the Secret backup password and Confirm password edit boxes. This password is essential for protecting the backup of the ESSO master secret. Optionally, you can enter a Password reminder to aid in remembering the password. You can also change the default Backup file location if desired.
8. Apply Configuration: In the Actions menu of the HIS 2010 Configuration dialog, click Apply Configuration. This initiates the configuration process based on the settings you have specified.
9. Review Configuration Summary: The Summary dialog will appear, outlining the components that will be configured, including Enterprise SSO. Review this summary and click Next to proceed with the configuration application.
10. Monitor Configuration Progress: The Configuration Progress dialog will display the status of the configuration process. Monitor this for any errors. If errors occur, the Configuration Failed dialog will be displayed. Click the Logfile hyperlink to access the configuration log file and provide it to your administrator for troubleshooting.
11. Verify Configuration Result: Upon successful configuration, the Configuration Result dialog will indicate Success. Verify this and click Finish to complete the configuration process.
12. Exit Configuration Tool: In the HIS 2010 Configuration dialog, click File, and then click Exit to close the configuration tool.

Applying HIS 2010 Hotfix KB 2499828/Cumulative Update 1¶

Applying HIS 2010 hotfix KB 2499828 or Cumulative Update 1 is a mandatory step to enable the functionality for Windows-initiated SSO and secure storage of DB2 connection strings. This update introduces the necessary enhancements and fixes to support these features within Host Integration Server 2010. Without this update, the subsequent steps for configuring and utilizing SSO for DB2 connections will not function as expected.

The process for applying the hotfix is straightforward:

1. Download the Hotfix Package: Obtain the hotfix package KB 2499828 or the latest Cumulative Update for HIS 2010 from the Microsoft Support website or your organization’s software distribution channel. Ensure you download the correct package for your HIS 2010 version and system architecture.
2. Install the Hotfix: Execute the downloaded hotfix package on all HIS 2010 computers within your environment where you intend to utilize the SSO features for DB2 connections. Follow the on-screen instructions provided by the hotfix installer to complete the installation process. A system restart may be required to fully apply the hotfix.

Defining an ESSO Affiliate Application for Windows-Initiated SSO¶

To utilize Windows-initiated Single Sign-On for accessing DB2 resources, it is essential to define an ESSO Affiliate Application. This Affiliate Application acts as a container for managing the mappings between Windows user accounts and the corresponding external credentials required to access the DB2 system. Creating an Affiliate Application is a fundamental step in establishing the SSO framework for DB2 connectivity.

Follow these steps to define an Affiliate Application for Windows-initiated SSO:

1. Launch SSO Administration Console: Open the SSO Administration console. Navigate to the Start menu, then All Programs, then Enterprise Single Sign-On, and click SSO Administration. When prompted by User Account Control, click Yes to grant administrative privileges.
2. Create New Affiliate Application: In the Enterprise Single Sign-On scope pane of the ENTSSO MMC snap-in, expand the root node if necessary. Right-click on the Affiliate Applications folder, or select the folder and click Create Application in the Action menu. This will launch the Enterprise Single Sign-On Application Wizard.
3. Welcome Dialog: In the Enterprise Single Sign-On Application Wizard Welcome dialog, click Next to proceed to the next step.
4. General Application Settings: In the General dialog, enter a descriptive Application name (e.g., “SYS1”). Select the checkbox for Allow local accounts for access accounts. Choose Use SSO Affiliate Admin Accounts for Application Admin accounts. Click Next to continue. These settings configure the basic properties of the Affiliate Application.
5. Add Application Users: In the Accounts dialog, under the Application Users list, click Add. This allows you to specify which Windows user accounts will be authorized to use this Affiliate Application for SSO.
6. Select Users or Groups: In the Select Users or Groups dialog, enter a user account in the format DOMAIN\username (e.g., DOMAIN\username). Click Check Names to validate the user account. Once validated, click OK. Verify that the account is added to the Application Users list in the Accounts dialog. Click Next.
7. Configure Options: In the Options dialog, ensure that the Affiliate Application is Enabled. Verify that Allow Windows Initiated SSO is selected. Click Next. These options enable the Affiliate Application and specify its SSO behavior.
8. Define Fields: In the Fields dialog, confirm that the Number of fields is set to the default value of two (2). These fields are typically used to store the external user ID and password. Click Create to create the Affiliate Application.
9. Completion Dialog: In the Completing the Application Wizard dialog, verify that the application was created successfully. Click Finish. If any errors occurred during creation, consult your network administrator or client administrator for assistance.
10. Create New Mapping: In the Enterprise Single Sign-On scope pane, navigate to the Affiliate Applications folder and select the newly created Affiliate Application (e.g., “SYS1”). Right-click on the application, or select it and click New Mapping in the Action menu.
11. Specify Mapping Details: In the Create New Mapping dialog, enter a Windows user account (e.g., DOMAIN\username). Enter an External user account (e.g., “HISDEMO”) that is authorized to access the target DB2 server (“SYS1”). Select the checkbox Set credentials for new mapping. Click OK. This establishes the mapping between the Windows user and the external DB2 user account.
12. Set Credentials: In the Set Credentials dialog, enter the valid DB2 server password (e.g., “HISDEMO”) in both the Password and Confirm edit boxes. Click OK. This securely stores the DB2 password associated with the mapping.
13. Verify Affiliate Application Status: In the Enterprise Single Sign-On scope pane, navigate to the Affiliate Applications folder. Verify that the newly created Affiliate Application (e.g., “SYS1”) is Enabled. Select the Affiliate Application and check the results pane (on the right side of the dialog) to confirm that the listed Windows user is also enabled.

Defining a DB2 Connection String for Windows-Initiated SSO¶

To effectively utilize Windows-initiated SSO, a properly configured DB2 connection string is essential. This connection string specifies the parameters required to connect to the DB2 database, leveraging the SSO mechanism for authentication. Several methods exist for defining DB2 connection strings, including using the ADO.NET Data Provider for DB2 ConnectionString Builder, manual string creation, OLE DB Data Links, or the Data Source Wizard (DSW) within the Data Access Tool (DAT) or Visual Studio Server Explorer. This guide will utilize the DSW in the DAT approach.

Follow these steps to define a DB2 connection string for Windows-initiated SSO using the Data Access Tool:

1. Launch Data Access Tool: Open the Data Access Tool. Navigate to the Start menu, then All Programs, then HIS 2010, and click Data Access Tool. When prompted by User Account Control, click Yes to grant administrative privileges.
2. Create New Data Source: In the Data Access Tool, click Data Sources in the left pane. Click the File menu, and then select New Data Source. This will launch the Data Source Wizard.
3. Welcome Dialog: In the Welcome dialog of the Data Source Wizard, click Next to proceed.
4. Select Data Source Platform: In the Data Source dialog, select DB2/MVS from the Data source platform list. Choose TCP/IP as the connection type. Click Next.
5. TCP/IP Network Connection Details: In the TCP/IP Network Connection dialog, enter the Address or alias of the DB2 server (e.g., “SYS1”). Enter the Port number for the DB2 listener (e.g., “446”). Click Next.
6. DB2 Database Details: In the DB2 Database dialog, enter the Initial Catalog (database name, e.g., “DSN1D037”). Specify the Package collection (e.g., “HISDEMO”), Default schema (e.g., “HISDEMO”), and Default qualifier (e.g., “HISDEMO”). Click Next.
7. Locale Settings: In the Locale dialog, select the appropriate Host CCSID (e.g., “EBCDIC - U.S./Canada [37]”) and PC code page (e.g., “ANSI - Latin I [1252]”). Click Next.
8. Security Configuration: In the Security dialog, select Single sign-on from the Security method list. Choose the newly created Affiliate Application (e.g., “SYS1”) from the Affiliate application list. Click Next.
9. Advanced Options: In the Advanced Options dialog, you can optionally select Connection pooling if desired. Click Next.
10. All Properties: In the All Properties dialog, you can optionally review and verify the properties in the list. Click Next.

11. Validation and Test Connection: In the Validation dialog, click Connect to test the connection. Verify the Output of the test connection. A successful connection message will be displayed, including server class and version information.

    Successfully connected to data source 'New Data Source'.
    Server class: DB2/MVS
    Server version: 09.01.0005
    

    
    12. Package Creation (Optional): In the Validation dialog, optionally click Packages to initiate the package creation process. Verify the Output of the package creation. When prompted by the Warning dialog, click Continue.

    Connected to data source 'New Data Source'.
    READ COMMITTED package has been created.
    READ UNCOMMITTED package has been created.
    SERIALIZABLE package has been created.
    REPEATABLE READ package has been created.
    The package creation process has completed successfully.
    

    
    13. Sample Query (Optional): In the Validation dialog, optionally click Sample Query to execute a sample query and verify the results in the Grid. Click Next.
    14. Saving Information: In the Saving Information dialog, enter a Data source name (e.g., DB2_IP_SYS1_DSN1D037_SSO). Select both Universal data link and Initialization string file options. Click Next.
    15. Completing the Wizard: In the Completing the Data Source Wizard dialog, review the summary of your configurations. Click Finish.
    16. Copy Connection String: In the Data Access Tool, locate and click the newly created DB2 OLE DB UDL file (e.g., DB2_IP_SYS1_DSN1D037_SSO). Copy the Connection String displayed at the bottom of the Data Link Tool dialog to the Windows clipboard. This connection string can be used in your applications to connect to the DB2 database using Windows-initiated SSO.

    Provider=DB2OLEDB;Initial Catalog=DSN1D037;Network Transport Library=TCPIP;Host CCSID=37;PC Code Page=1252;Network Address=SYS1;Network Port=446;Package Collection=HISDEMO;Default Schema=HISDEMO;Process Binary as Character=False;Connect Timeout=15;Units of Work=RUW;Default Qualifier=HISDEMO;DBMS Platform=DB2/MVS;Affiliate Application=SYS1;Use Early Metadata=False;Defer Prepare=False;DateTime As Char=False;Rowset Cache Size=0;Max Pool Size=100;Datetime As Date=False;AutoCommit=True;Authentication=Server;Integrated Security=SSPI;Persist Security Info=False;Cache Authentication=False;Connection Pooling=True;Derive Parameters=False;
    

Defining an ESSO Affiliate Application for Storing DB2 Connection Strings¶

For enhanced security and centralized management, it’s beneficial to store the complete DB2 connection string itself within ESSO. This approach prevents hardcoding connection strings in applications and provides a single point of control for managing and updating connection details. This requires creating a dedicated ESSO Affiliate Application specifically designed for storing connection strings.

Follow these steps to define an Affiliate Application for storing DB2 connection strings:

1. Launch SSO Administration Console: Open the SSO Administration console as described previously.
2. Create New Affiliate Application: In the Enterprise Single Sign-On scope pane, right-click on the Affiliate Applications folder and select Create Application.
3. Welcome Dialog: In the Enterprise Single Sign-On Application Wizard Welcome dialog, click Next.
4. General Application Settings: In the General dialog, enter an Application name specifically for storing connection strings (e.g., SYS1_DSN1D037_ConnectionString). Select Allow local accounts for access accounts and Use SSO Affiliate Admin Accounts for Application Admin accounts. Click Next.
5. Add Application Users: In the Accounts dialog, add the necessary Windows user accounts or groups that will be authorized to access the stored connection string. Click Add, select users/groups, and click OK, then Next.
6. Configure Options: In the Options dialog, ensure Affiliate Application is Enabled and Allow Windows Initiated SSO is selected. Click Next.
7. Define Fields (Crucial Step): In the Fields dialog, change the Number of fields to 3 (three). Locate the third field item labeled “Field 2” and change its label to ConnString. The first two fields can retain their default names (e.g., Field 0, Field 1) or be renamed as needed for other potential data. Click Create. Important: Setting the number of fields to 3 and renaming “Field 2” to “ConnString” is essential for storing the connection string correctly.
8. Completion Dialog: In the Completing the Application Wizard dialog, verify successful application creation and click Finish.
9. Create New Mapping: In the Enterprise Single Sign-On scope pane, select the newly created Affiliate Application (e.g., SYS1_DSN1D037_ConnectionString). Right-click and select New Mapping.
10. Specify Mapping Details: In the Create New Mapping dialog, enter a Windows user account. Enter an External user name (e.g., “HISDEMO” – this is somewhat arbitrary for connection string storage but still required). Select Set credentials for new mapping and click OK.
11. Set Credentials (Store Connection String): In the Set Credentials dialog, enter a dummy password in the Password and Confirm edit boxes (as a password is required by the wizard, but not relevant for connection string storage). Crucially, paste the DB2 connection string you copied from the Data Access Tool into the ConnString edit box. Click OK. This stores the complete DB2 connection string securely within ESSO, associated with this mapping.
12. Verify Affiliate Application Status: In the Enterprise Single Sign-On scope pane, verify that the newly created Affiliate Application (e.g., SYS1_DSN1D037_ConnectionString) is Enabled and the associated Windows user is also enabled in the results pane.

Defining a DB2 Connection that Retrieves Connection String from SSO¶

To utilize the securely stored connection string from ESSO, you need to define a DB2 connection that is configured to retrieve its connection parameters dynamically from the designated ESSO Affiliate Application. This ensures that applications connecting to DB2 leverage the centralized and secure connection string management provided by ESSO. Again, we will use the Data Source Wizard in the Data Access Tool for this purpose.

Follow these steps to define a DB2 connection that retrieves the connection string from ESSO:

1. Launch Data Access Tool: Open the Data Access Tool.
2. Create New Data Source: In the Data Access Tool, click Data Sources, then File, then New Data Source.
3. Welcome Dialog: In the Data Source Wizard, click Next.
4. Select Data Source Platform: In the Data Source dialog, select DB2/MVS and TCP/IP. Click Next.
5. TCP/IP Network Connection (Placeholder Values): In the TCP/IP Network Connection dialog, enter a placeholder value like x for Address or alias. Click Next. Note: These values will be overridden by the connection string retrieved from SSO.
6. DB2 Database (Placeholder Values): In the DB2 Database dialog, enter placeholder values like x for Initial Catalog and Package collection. Click Next. Note: These values will also be overridden.
7. Locale Dialog: In the Locale dialog, click Next to accept the defaults.
8. Security Configuration (SSO with Connection String Affiliate App): In the Security dialog, select Single sign-on from the Security method list. Crucially, select the newly created Affiliate Application specifically for connection string storage (e.g., SYS1_DSN1D037_ConnectionString) from the Affiliate application list. Click Next.
9. Advanced Options Dialog: In the Advanced Options dialog, click Next to accept defaults.
10. All Properties Dialog: In the All Properties dialog, click Next.

11. Validation and Test Connection: In the Validation dialog, click Connect to test the connection. Verify the Output of the test connection. A successful connection indicates that the connection string is being retrieved correctly from ESSO and the connection is established.

    Successfully connected to data source 'New Data Source'.
    Server class: DB2/MVS
    Server version: 09.01.0005
    

    
    12. Package Creation (Optional): In the Validation dialog, optionally click Packages and Continue when prompted by the warning dialog. Verify the package creation output.

    Connected to data source 'New Data Source'.
    READ COMMITTED package has been created.
    READ UNCOMMITTED package has been created.
    SERIALIZABLE package has been created.
    REPEATABLE READ package has been created.
    The package creation process has completed successfully.
    

    
    13. Sample Query (Optional): Optionally click Sample Query and verify the results. Click Next.
    14. Saving Information: In the Saving Information dialog, enter a Data source name (e.g., DB2_IP_SYS1_DSN1D037_ConnectionString). Select Universal data link and Initialization string file. Click Next.
    15. Completing the Wizard: In the Completing the Data Source Wizard dialog, review and click Finish.
    16. Copy Connection String (Note Placeholder Values): In the Data Access Tool, select the newly created DB2 OLE DB UDL file (e.g., DB2_IP_SYS1_DSN1D037_ConnectionString). Copy the Connection String. Observe that placeholder values like “x” are present for Network Address and Initial Catalog, as these are intended to be retrieved from the SSO-stored connection string.

    DB2OLEDB;Initial Catalog=x;Network Transport Library=TCPIP;Host CCSID=37;PC Code Page=1252;Network Address=x;Network Port=446;Package Collection=x;Process Binary as Character=False;Units of Work=RUW;DBMS Platform=DB2/MVS;Affiliate Application=SYS1_DSN1D037_ConnectionString;Use Early Metadata=False;Defer Prepare=False;DateTime As Char=False;Rowset Cache Size=0;Datetime As Date=False;Auto-Commit=True;Authentication=Server;Decimal As Numeric=False;FastLoad Optimize=False;Derive Parameters=True;Integrated Security=SSPI;Persist Security Info=False;Cache Authentication=False;Connection Pooling=False;
    

Important Note:

The Data Source Wizard automatically populates default values in the Universal Data Link (UDL) and connection string files. When utilizing SSO-stored DB2 connection strings, you primarily need to specify the following properties in your client application’s connection configuration:

• Provider=DB2OLEDB (Required for OLE DB connections)
• Integrated Security=SSPI (Specifies Windows Integrated Security)
• Affiliate Application=SYS1_DSN1D037_ConnectionString (Points to the ESSO Affiliate Application storing the connection string)
• Network Address=x; (Placeholder - value from SSO string will override)
• Initial Catalog=x; (Placeholder - value from SSO string will override)
• Package Collection=x; (Placeholder - value from SSO string will override)

A minimal connection string in your application would resemble:

Provider=DB2OLEDB;Integrated Security=SSPI;Initial Catalog=x;Network Address=x; Package Collection=x;Affiliate Application=SYS1_DSN1D037_ConnectionString

By following these steps, you can effectively secure and manage your DB2 connection strings using Enterprise Single Sign-On within Host Integration Server 2010, enhancing the security and maintainability of your database access infrastructure.

---

Do you have any questions or comments about storing DB2 connection strings in Enterprise Single Sign-On? Share your thoughts below!