How to use IBM App Connect with Microsoft SQL Server

Connecting to Microsoft SQL Server

Complete the connection fields that you see in the App Connect Designer Connect > Applications and APIs page or flow editor. If necessary, work with your Microsoft SQL Server administrator to obtain these values.

Database hostname

Specify the fully qualified hostname or the IP address of the Microsoft SQL Server database server. For example, myserver.abc.com or 192.0.2.24.

For more information, see HOST_NAME (Transact-SQL) on the Microsoft Documentation page.

Database port

Specify the port on which the Microsoft SQL Server database server listens for incoming client connections.

For more information, see Configure SQL Server to listen on a specific TCP port on the Microsoft Documentation page.

Database name

Specify the name of the Microsoft SQL Server database that you want to connect to.

Username

Specify a username that is authorized to access the database, in the case used in your Microsoft SQL Server instance.

Password

Specify the password for the database user.

Schema

Specify the schema name for the database objects to be accessed, in the case used in your Microsoft SQL Server instance. If you leave this field blank, the default schema that is associated with the user is used.

For more information, see Create a database schema on the Microsoft Documentation page.

Maximum pool size

Specify the maximum number of database connections that are allowed within a single connection pool. If unspecified, the value defaults to 0 (zero).

Additional parameters

Specify extra parameters to control how to connect to the database. Use name-value pairs (with a semicolon separator) in the following format: name1=value1;name2=value2.

Example: autoCommit=true;maxActive=20

By default, SQL Server Authentication is used as the authentication mode, and requires no additional parameters. To connect through Windows Authentication, you must specify either of these additional parameters to indicate which NTLM version the endpoint supports.

• AuthenticationMethod=ntlm2java
• AuthenticationMethod=ntlmjava

Private network connection

Select the name of a private network connection that App Connect uses to connect to your private network. This list is populated with the names of private network connections that are created from the Private network connections page in the Designer instance. You see this field only if a switch server is configured for this Designer instance. For more information, see Connecting to a private network from App Connect Designer. (In App Connect Designer 12.0.10.0-r1 or earlier instances that include this field, the display name is shown as Agent name.)

To connect to a Microsoft SQL Server endpoint from the App Connect Designer Applications and APIs page for the first time, expand Microsoft SQL Server, then click Connect.

General considerations

Before you use App Connect Designer with Microsoft SQL Server, take note of the following considerations:

• (General consideration) You can see lists of the trigger events and actions that are available on the Applications and APIs page of the App Connect Designer.

  For some applications, the events and actions depend on the environment and whether the connector supports configurable events and dynamic discovery of actions. If the application supports configurable events, you see a Show more configurable events link under the events list. If the application supports dynamic discovery of actions, you see a Show more link under the actions list.

• (General consideration) If you are using multiple accounts for an application, the set of fields that is displayed when you select an action for that application can vary for different accounts. In the flow editor, some applications always provide a curated set of static fields for an action. Other applications use dynamic discovery to retrieve the set of fields that are configured on the instance that you are connected to. For example, if you have two accounts for two instances of an application, the first account might use settings that are ready for immediate use. However, the second account might be configured with extra custom fields.

Events and actions

Microsoft SQL Server events

These events are for changes in this application that trigger a flow to start completing the actions in the flow.

Show more configurable events: Events that are shown by default are pre-configured by using optimized connectivity. More items are available after you configure events that can trigger a flow by polling this application for new or updated objects. For more information about configurable events, see Configuring polled events to trigger flows.

Microsoft SQL Server actions

Your flow completes these actions on this application.

Available items are listed after you connect App Connect to Microsoft SQL Server.