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

In the first part of this mini-series blog post, we discussed how to create a URI connection to connect to the Oracle database without using tnsnames.ora along with the advantages and limitations of this approach. In this second part, we will explain how you can establish a connection to the Oracle database using the tnsnames.ora.

Connection URI to Connect 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.

This approach is a little bit more complex to do. First, you still need to understand all the necessary configurations that you need to configure to connect to Oracle, at least the first time. You also need to perform additional configurations outside the BizTalk Server Administration Console: setting up the tnsnames.ora file.

• In this approach, the net service name in the tnsnames.ora file, and the actual tnsnames.ora file itself need to be present on the BizTalk Server machines.
  • • For that, you need to have installed on the BizTalk machines the Oracle Database administration components. The easy way to do this, is on the Oracle Database Client installation to choose the Runtime installation type.

• By doing that, you will install all the necessary components. And after that, you will find on your ORACLE HOME\NETWORK\ADMIN directory. There you need to put your tnsnames.ora file, containing the configuration to establish the connection to Oracle
  
  • ORACLE HOME is by default something like: D:\app\client\<user>\product\12.1.0\client_1

  • By default, the tnsnames.ora file is located in the ORACLE_HOME/network/admin directory. Oracle Net will check the other directories for the configuration file. For example, the order checking the tnsnames.ora file is as follows:

    • The directory can be specified by the TNS_ADMIN environment variable. If the file is not found in the specified directory, then it is assumed that the file does not exist.

    • If the TNS_ADMIN environment variable is not set, then Oracle Net checks the ORACLE_HOME/network/admin directory.

  TNSNAMES.ORA is a SQL*Net configuration file that defines databases addresses for establishing connections to them. It contains network service names mapped to connect descriptors for the local naming method, or net service names mapped to listener protocol addresses.

  • A net service name is an alias mapped to a database network address contained in a connect descriptor.
  • A connect descriptor contains the location of the listener through a protocol address and the service name of the database to which to connect. 

  Note: Due to an Oracle Client limitation, the DataSourceName parameter (net service name) in the connection URI cannot contain more than 39 characters if you are performing operations in a transaction. Therefore, make sure that the value specified for the DataSourceName parameter is less than or equal to 39 characters if you will be performing operations in a transaction.

  The basic syntax for a tnsnames.ora file is:
  <net_service_name>=
     (DESCRIPTION =
        (ADDRESS_LIST =
           (ADDRESS = (PROTOCOL = TCP)(Host = <hostname>)(Port = <port>))
           )
          (CONNECT_DATA =
             (SERVICE_NAME = <service_name>)
           )
     )

In the preceding example:

• DESCRIPTION contains the connect descriptor;
• ADDRESS contains the protocol address;
• CONNECT_DATA contains the database service identification information.

Here is a complete example:

ORACLE_APP_QA=
   (DESCRIPTION =
      (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(Host = 192.168.1.1)(Port = 1524))
         )
        (CONNECT_DATA =
           (SERVICE_NAME = APPQA)
        )
   )

The Oracle Database adapter connection URI adheres to this basic format, and is implemented as follows:

• oracledb://[NET_SERVICE_NAME]?PollingId=[POLLING_ID]

Or, in more advanced scenarios:

• oracledb://User=[USER_NAME];Password=[PASSWORD]@[NET_SERVICE_NAME]?PollingId=[POLLING_ID]

Where:

• [USER_NAME] is the user name to use for authentication on the Oracle database, such as SCOTT. You must set the AcceptCredentialsInUri binding property to true to specify the user name and password in the connection URI. The AcceptCredentialsInUri binding property isn’t available in the BizTalk Server Binding tab.
  • Note: The Oracle Database adapter preserves the case of the value that you enter for the user name when it opens a connection on the Oracle database. User names on the Oracle database are case-sensitive. You should ensure that you provide Oracle user names to the Oracle Database adapter in the case expected by your Oracle database. Typically, this means that the user name in the SCOTT/TIGER credential should be upper case: “SCOTT”.
• [PASSWORD] is the password to use for authentication on the Oracle database, such as TIGER. You must set the AcceptCredentialsInUri binding property to true to specify the user name and password in the connection URI. The AcceptCredentialsInUri binding property isn’t available in the BizTalk Server Binding tab.
  • Note: The Oracle Database adapter preserves the case of the value that you enter for the password when it opens a connection on the Oracle database. For release 10g and earlier, passwords on the Oracle system are not case-sensitive.
• [NET_SERVICE_NAME] is a net service name that is specified in the tnsnames.ora file on the computer where the Oracle Database adapter is installed. For more information about net service names and tnsnames.ora, see Configure the Oracle Client for the Oracle Database adapter.
• [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.

Once again, 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.

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 

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

• 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://ORACLE_APP_QA

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