Securing connectivity between WebSphere Cast Iron Database connector and a database

This article describes how to configure a Database connector in WebSphere Cast Iron Studio for secure SSL connection, how to import secure certificates from a DB2 database, and how to execute the orchestration securely.

Share:

Vinod A. Valecha (vinod.valecha@in.ibm.com), Software Engineer, IBM India

Photo of Vinod A. ValechaVinod Valecha is a Software Developer with the WebSphere Cast Iron team at IBM India Software Labs. He is currently working on development and customer support of Cast Iron and Adapters. He has been with IBM for over six years working with various Java technologies, including Java Connector Architecture (JCA). He holds a Bachelor of Technology degree from the College of Engineering, Pune, India.



18 June 2014

Also available in Chinese Russian

Introduction

IBM® WebSphere® Cast Iron® Studio contains a variety of connectors that provide seamless integration capabilities. You can use these connectors to integrate applications without any programming. The Database connector provides integration to databases and is widely used in business data monitoring in enterprise application and service integration scenarios. The Database connector supports creating, updating, deleting, and retrieval of records. It also supports polling of records at a database table and execution of stored procedures.

Because of the critical nature of business data, most customers want any private data communicated from the application to the database endpoint to be secured. Highly sensitive information requires additional data processing to secure it. You can easily enable this level of security with standard cryptographic protocols, Secure Sockets Layer (SSL) and Transport Layer Security (TLS), which are configured at the Database connector and the database end.

SSL provides an encrypted link between the sender and the receiver. It uses a digital certificate and asymmetric cryptography to authenticate the entity they are talking with and to exchange a session key, which is used to encrypt data flowing between the sender and receiver. The session key allows for the secure sending of data over an open networked environment, protecting the data from being compromised by an outside party.

Prerequisites

  • IBM WebSphere Cast Iron Studio V7.0
  • IBM DB2® database V9.7
  • Private key and certificate files to be used in an SSL operation

This article covers the following topics:

Configuring SSL for the DB2 database

The DB2 database system supports SSL and TLS to enable a client application to authenticate to a server and provide private communication between the client and server by use of encryption. Authentication is performed by the exchange of digital certificates.

Without encryption, packets of information travel through networks in full view of anyone who has access. You can use SSL to protect data in transit on all networks that use TCP/IP (you can think of an SSL connection as a secured TCP/IP connection). A client and server establish a secure SSL connection by performing an SSL handshake.

During an SSL handshake, a public-key algorithm, such as RSA, is used to securely exchange digital signatures and encryption keys between a client and server. This identity and key information is used to establish a secure connection for the session between the client and server. After the secure session is established, data transmission between the client and server is encrypted using a symmetric algorithm, such as Advanced Encryption Standard (AES).

The SSL handshake between a client and server consists of the following steps:

  1. The client requests an SSL connection and lists its supported cipher suites.
  2. The server responds with a selected cipher suite.
  3. The server sends its digital certificate to the client.
  4. The client verifies the validity of the server certificate, for authentication purposes. It can do this by checking with the trusted certificate authority that issued the server certificate or by checking in its own key database.
  5. The client and server securely negotiate a session key and a message authentication code (MAC).
  6. The client and server securely exchange information using the key and MAC.

For information on enabling SSL for DB2 on Linux, Unix or Windows, see the topic Configuring Secure Sockets Layer (SSL) support in a DB2 instance in the Knowledge Center.

For information on enabling SSL for a DB2 database at the server side on Windows, see the following developerWorks article DB2 technical tip: Set up Secure Sockets Layer (SSL) for DB2 on Windows.

After configuring SSL for the DB2 database, refer to the Knowledge Center topic on Importing an end-entity certificate.

Importing the CA certificates from the database

Before you create the orchestration for the database activities and test the endpoint, you import the certification authority (CA) certificates from the database into the Cast Iron Studio workstation.

To import the CA certificates from the DB2 database into Cast Iron Studio, follow these steps:

  1. Locate the ca-cert file stored on the database server.
  2. Copy the ca-cert file and then navigate to the workstation where Studio is installed.
  3. Using the command window, paste the ca-cert copy in the home directory of Studio:
    C:\Program Files\IBM\WebSphere Cast Iron Studio x.x.x
  4. Navigate to the security directory of Studio:
    C:\Program Files\IBM\WebSphere Cast Iron Studio x.x.x\security.
  5. Run the following command to import the ca-cert file into Studio:
    ..\jre\bin\keytool.exe -import -v -keystore cacerts -storepass changeit -file 
    ..\castiron_ca_cert.pem -alias alias_name
       
       Where alias is the alias of the database CA certificate.
  6. When you are prompted for a yes or no, type yes (Y) and press Enter.
  7. Exit the command window.

Creating and executing orchestration with SSL security configuration

Orchestration is a function in Cast Iron Studio that creates a sequence of activities that perform a task. The orchestration consists of an Insert activity and Schedule Job activity. The Insert activity inserts the data as a row into the specified database table. The Schedule Job activity starts an orchestration job at the specified time interval.

To create and execute an orchestration for SSL configuration, follow these steps:

  1. Create a new project in Cast Iron Studio. Click Create Project.
    Figure 1. Create new project
    Create new project
  2. Enter a project name in the Create New Project dialog box and click OK.
    Figure 2. Project name
    Create Project name
  3. Click the Activities tab and drag and drop the Insert Rows activity onto the orchestration area as shown in Figure 3. The Activities tab displays all the available connectors (listed as folders) and activities supported by the connectors.
    Figure 3. Activities tab
    Activities tab
  4. An orchestration with the Insert Row activity is shown in Figure 4.
    Figure 4. Insert Row activity
    Insert Row activity
  5. Click Pick Endpoint in the Checklist panel, as shown in Figure 5.
    Figure 5. Pick Endpoint
  6. Click the New button to open the Create Endpoint window.
    Figure 6. Create Endpoint
  7. Configure the Endpoint properties for the DB2 database as shown in Figure 7. The configuration properties include:
    • Database Name
    • Server IP address
    • Port
    • User Name
    • Password
    • Security Parameters
    Figure 7. Endpoint configuration properties
  8. Under Security Parameters, check Enable Encryption and Validate Server Certificate as shown in Figure 8.
    Figure 8. SSL security parameters
  9. To test the connection, click Test Connection as shown in Figure 9.
    Figure 9. Test connection
  10. The test results popup displays the connectivity to the database. Click OK.
    Figure 10. Test connection result
  11. Choose the database table for insertion. Click Pick Table as shown in Figure 11.
    Figure 11. Pick table
  12. Click Browse to select the database table from the server as shown in Figure 12.
    Figure 12. Browse tables
  13. Click Search to search for the available tables in the database as shown in Figure 13.
    Figure 13. Table selection
  14. Select the table to be inserted. In the current example, Customer table is selected as shown in Figure 14.
    Figure 14. Customer table
  15. The table schema of the selected table is shown in Figure 15.
    Figure 15. Table schema

    Click to see larger image

    Figure 15. Table schema

  16. Complete the Configure task by configuring the Delivery Rules and Retry parameters to suitable values.
  17. Click Map Inputs from the Checklist and set the data for the columns of the row to be inserted.
    Figure 16. Map inputs parameters
  18. The Insert Rows activity is configured. Drag and drop the Schedule Job activity from the Utilities section on the Activities tab to the orchestration as shown in Figure 17. The Schedule Job activity starts an orchestration job at the specified time and date based on the GMT timezone.
    Figure 17. Final orchestration
  19. Set the time interval for the orchestration to start in the Configure task of the Schedule Job activity as shown below.
    Figure 18. Schedule job configure
  20. Execute the orchestration by clicking the green Start button under the Verify tab as shown in Figure 19.
    Figure 19. Orchestration execution
  21. The orchestration record inserted into the database is shown in Figure 20.
    Figure 20. Inserted row

Conclusion

In this article, you learned how to configure a WebSphere Cast Iron Database connector for secure connection through SSL, how to import secure certificates from the database, and how to execute the orchestration securely.

Resources

Learn

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into WebSphere on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=WebSphere
ArticleID=974416
ArticleTitle=Securing connectivity between WebSphere Cast Iron Database connector and a database
publish-date=06182014