Using Integrated Windows Authentication when recording data with a Microsoft SQL Server database

Integrated Windows Authentication (IWA) refers to a set of authentication protocols that are used by Windows clients and servers. You can use IWA with IBM® App Connect Enterprise to provide transport-level security when you are recording data with a Microsoft SQL Server database.

Before you begin

Read the following topics:
Note: Choosing V2 or legacy schema for the DataCaptureStore tables.

Most users of IBM App Connect Enterprise must use the schemas that are defined in DataCaptureSchema_V2.sql file.

It contains up-to-date schemas that use the integration server hostname and integration node hostname to distinguish records inserted on behalf of other processes. In IBM Integration Bus 10.0 and earlier, integration nodes and integration servers use universally unique identifiers (UUIDs) to identify other processes and the DataCaptureSchema.sql file contains table schemas that include these elements for compatibility reasons.

Since IBM App Connect Enterprise does not use UUIDs, records originating from IBM App Connect Enterprise contain null values for these fields.

About this task

You can use IWA to specify a Windows user account to be used for authentication when using Microsoft SQL Server to record and replay data. The following steps describe how to create the SQL Server database, create a Windows user account, and give that user account permission to access the SQL Server.

For more information about how to set up Microsoft SQL Server for record and replay without using IWA, see Creating and configuring a Microsoft SQL Server database for recording data.

Procedure

  1. Use the script that is provided with IBM App Connect Enterprise to create and configure an SQL Server database to store your recorded data. Note that the script creates some tables that are not currently used, and are reserved for future use, such as WMB_EVENT_FIELDS and WMB_EVENT_TYPES.
    1. Locate the script at install_dir\server\ddl\sqlServer\DataCaptureSchema.sql, where install_dir is the location of your IBM App Connect Enterprise installation.
    2. Optional: Customize the provided DataCaptureSchema script.
    3. To run the script, at a command line, navigate to the script location and enter the following command: 
      sqlcmd -d databaseName -i DataCaptureSchema.sql
  2. Create a Windows user account to be used for authentication.
    1. Ensure that you are logged on as the administrator, then create a new user account with non-administrator privileges. Provide a user name and password, ensuring that the user name has fewer than 12 characters.
    2. Add this user to the mqbrkrs group by using the mqsisetsecurity command.
  3. Create an integration node and set the serviceUserId and servicePassword to the user name and password of the authenticating Windows user account.
  4. Ensure that you are logged on as the SQL Server administrator, then give the Windows user account permission to access the SQL Server instance.
    1. Start the SQL Server Management Studio.
      For more information, see your SQL Server documentation.
    2. Connect to the database instance for which you want to set up authentication.
    3. In the instance tree, navigate to Security > Logins, right-click Logins, and select New Login.
    4. Ensure that Windows Authentication is selected.
    5. Click Search, locate the user ID to use, then click OK.

      The user ID typically has the format DOMAIN/USERNAME. To search the instance location, click Advanced, then click Find Now. You can then select the name from the list of possible options.

    6. To map the user ID to the databases that are used for recording and replaying data, navigate to the User Mapping page in the instance tree. Select the relevant databases and ensure that the correct DOMAIN/USERNAME combination and default database schema are specified.
    7. To create the user login, accept all other default values and click OK.
  5. Create the necessary ODBC connections that target the databases that are used for recording and replaying data. This step involves defining the data source that the record and replay store uses.
    1. Open the Windows Control Panel and navigate to System and Security > Administrative Tools > Data Sources (ODBC).
    2. On the System DSN tab, create a new data source by clicking Add.
    3. Select SQL Server Native Client, then click Finish.
    4. Provide a name for your ODBC data source, select the relevant SQL Server, then click Next.
    5. Ensure that SQL Server is set to verify authenticity by using Integrated Windows authentication, then click Next.
    6. Change the default database to use the database that is used as the store for recording and replaying data, then click Next.
    7. Accept the remaining default value, then click Finish.
    8. Test that the data source connects successfully to the SQL Server database by clicking Test Data Source.

      If the test is successful, a success message is issued. To complete setup, click OK.

What to do next

Create the record and replay store by following the steps in Recording data. Make sure that the data source that you created in this task is used as the target for the record and replay store.