BizTalk Server: Create a connection to the Oracle Database (Part I)

Despite not being an expert in ORACLE, this is a system that is quite common to find, and there are many integrations that need to interact with the Oracle database. Fortunately for me, I have met people who are very experienced in Oracle, and with that, I have learned a little more every day.

The goal of this two-post series is to help or clarify a bit better how we can configure the communication with the Oracle database using the WCF-OracleDB adapter.

The Oracle Database adapter supports two ways of establishing a connection to the Oracle database:

  • Using tnsnames.ora – In this approach, the connection URI provided by the adapter client contains only the net service name specified in the tnsnames.ora file. The adapter extracts the connection parameters such as server name, service name, port number, etc. from the net service name entry in the file. To use this approach, the computer running the Oracle client must be configured to include the net service name for the Oracle database in the tnsnames.ora file.
  • Without using tnsnames.ora – In this approach, the adapter clients specify the connection parameters directly in the connection URI. This does not require the net service name to be present in the tnsnames.ora file on the BizTalk Server machines. This approach does not even require the tnsname.ora file to be present on the BizTalk Server machines.

In this first part, we will be describing how you can configure a connection URI to connect to the Oracle Database without using tnsnames.ora.

Connection URI to connect to the Oracle database without using tnsnames.ora

This approach is the most simple to do if you understand all the necessary configurations that you need to configure to connect to Oracle because it doesn’t require any additional configurations outside the BizTalk Server Administration Console:

  • In this approach, the net service name in the tnsnames.ora file, or the actual tnsnames.ora file itself does not need to be present on the BizTalk Server machines.

However, this approach has some limitations:

  • This mode of connectivity is not supported if you are performing operations in a transaction. This is due to a limitation of the Oracle Client.

A typical Oracle Database adapter connection URI  is represented as: oracledb://[SERVER_ADDRESS]:[PORT_NUMBER]/[SERVICE_NAME]/[SERVICE_TYPE]?PollingId=[POLLING_ID] , where:

  • [SERVER_ADDRESS] is the name or IP address of the server on which the Oracle database is running. This is mandatory if you are using this type of connectivity
  • [PORT_NUMBER] is the Oracle Net Listener port. If no value is specified, the adapter takes the default value 1521
  • [SERVICE_NAME]  is the name of the Oracle database service. This is mandatory if you are using this type of connectivity
  • [SERVICE_TYPE] is the type of Oracle service. The possible values are Dedicated or Shared. The default is Dedicated.
    • A dedicated service uses a dedicated server process to serve only one user process.
    • A shared service uses a shared server process that can serve multiple user processes. 
  • [POLLING_ID] is an optional string that should be appended by the adapter to the standard namespace of the POLLINGSTMT operation.
    • This enables you to specify a unique namespace for each polling operation when a project contains multiple polling operations. You do not have to specify a PollingId string if your project contains only one POLLINGSTMT operation.

As a sample, when we are generating Oracle schemas to our project via Add > Add Generated Items… from our Visual Studio solution, and select Consume Adapter Service, this will open a Consume Adapter Service window where we perform the following actions:

  • From the Select a binding combo box, select the option oracleDBBinding
  • On the Configure a URI, click the Configure… button
  • On the Configure Adapter window on the Security tab, we can choose what type of configuration, the most typical is Username 

wcf oracle db adapter in biztalk

  • On the URI Properties, we can define all the properties we mentioned above

wcf oracle db adapter in biztalk

  • On the Binding Properties, you can define settings like timeouts, transition, and many more that I’m not going to discuss in detail here
  • When you press OK this will translate the settings you defined to an Oracle URI that you need to use: 
  • oracledb://11.103.XXX.XXX:1524/QLT/Dedicated

Consume Adapter service

If everything is ok, you can connect and navigate through Oracle to generate the correct artifacts.

It is not common to find these scenarios but in the past, there were certain programming scenarios that require the credentials to be present in the connection URI. For that kind of cases, there are two additional properties that you can specify on the URI:

  • [USER_NAME], the user name to use for authentication with the Oracle database
    • User names on the Oracle database are case-sensitive
  • [PASSWORD], the password to use for authentication with the Oracle database
    • For release 10g and earlier, passwords on the Oracle system are not case-sensitive

The URI template should be like: oracledb://User=[USER_NAME];Password=[PASSWORD]@[SERVER_ADDRESS]:[PORT_NUMBER]/[SERVICE_NAME]/[SERVICE_TYPE]?PollingId=[POLLING_ID]

However, if you try to specify this type of URI, by default, the Oracle Database adapter throws an exception when the Oracle database credentials are specified in the connection URI. This is because these credentials are represented as plain text in the connection URI, and this poses a security risk

To allow this type of URI you need to set the AcceptCredentialsInUri binding property to control whether the connection URI can contain credentials for the Oracle database.

  • If the AcceptCredentialsInUri property is false, the Oracle Database adapter throws an exception if the connection URI contains Oracle database credentials
  • If the property is true, no exception is thrown

However, the AcceptCredentialsInUri binding property is not available in BizTalk Server in the Binding tab while configuring a WCF-Custom or WCF-OracleDB receive or send port. To set the value of the AcceptCredentialsInUri binding property, you must open the adapter bindings file (XML file) that is created after you have generated metadata using the Consume Adapter Service Add-in, and then locate this binding property in the file. Specify an appropriate value for this binding property, save the binding file, and then import the binding file in BizTalk Server.

Note: It is not recommended that you do set AcceptCredentialsInUri to true.