How to use App Connect with Microsoft SQL Server

Microsoft SQL Server is a Relational Database Management System (RDBMS). It is a database platform that allows businesses to store and manage large amounts of data efficiently and securely.

Connecting to Microsoft SQL Server

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

Microsoft SQL Server connection fields:
Account name
Provide a meaningful name to your account that helps you to identify it.
BASIC authentication connection fields
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 agent that App Connect uses to connect to your private network. This list is populated with the names of private network agents that are created on the Private networks page. For more information, see Connecting to a private network.

SSL authentication connection fields (available only on IBM® App Connect Enterprise Designer running on-premises)
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.
Database server CA certificate: Specify the CA or server certificate in PEM format only when the server uses a self‑signed certificate, or if the certificate is not already trusted (private CA).
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

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. For more information, see Managing accounts.

Tip:

Before you use the account that is created in App Connect in a flow, rename the account to something meaningful that helps you to identify it. To rename the account on the Applications and APIs page, select the account, open its options menu (⋮), then click Rename Account.

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.

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.