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® Integration Bus to provide transport-level security when you are recording data with a Microsoft SQL Server database.

1. Use the script that is provided with IBM Integration Bus 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 Integration Bus installation.
   2. Optional: Customize the provided DataCaptureSchema script.

      If you modify the SQL to specify a particular schema, you must also set the same schema name in the DataCaptureStore configurable service.

   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 data capture 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.