Category Archives: Integration

Integrating with Force.com Platform using DbAmp: Part-03: Insert

Typically, when data is uploaded to Salesforce, it will be usually an upsert operation, but it is not uncommon to use insert operation. For e.g. if you are importing a fresh set of data or importing data for new object(s), there is no need to do an upsert and insert is a perfect candidate for these kind of scenarios.
The insert operation using the DbAmp can be done either directly against a Salesforce org or can be done locally in a staged database and then can be inserted into Salesforce org. The main advantage for using the staged database is that while the direct access method is very slow, especially for bulk uploads, the local access method (the staged database) is much faster. Also, the direct access method affects the governor limits at a faster rate when compared to local access method, since in the direct access method, each record inserted count against the API limit, while in the local access method, even without using BULKAPI switch, it takes 200 record inserts to count against the API limit. We will cover both the scenarios here and demonstrate them with couple of examples.
Direct Access Method:
It’s very simple to use the direct access method. The following statement inserts new Contact record in the Contact object:
INSERT INTO sforce contact ( LastName) VALUES (‘TestLastName’ )
As you may guess, the auto-populate system fields such as the Id, CreatedBy, CreatedDateTime, LastModifiedBy, LastModifiedDateTime fields should be left blank. These fields will be auto populated by the Salesforce itself.
Staged (Local) Database Method:
In the staged database method, a database is created locally in the SQL server and the data is inserted using a temporary table called load table. Here is the brief steps on accomplishing this:
  • Create a database in the SQL server (one-time task)
  • Replicate / Refresh the table that you want to insert the record(s)
  • Create the load table. The load table is a physical table, but temporary in nature. This load table should include the following fields
    • Id, Error and any fields that are necessary or that you want to populate.
      • The ‘Id’ field should be left empty and should not be populated. When insert succeeds, this field is populated with the ‘Id’ returned by the Salesforce.
      • The ‘Error’ field is populated by DbAmp with the status of the insert operation.
  • Populate the load table
  • Insert the data from the table into Salesforce using the SF_BULKOPS stored procedure.
Note that the SF_BULKOPS is one of the several stored procedure that ships with the DbAmp. We will cover the options, nitty-gritty’s of this stored procedure in a separate blog post.
Now, let’s look at an example of doing insert with the staged database method. The following script populates a list of contacts in the load table and then does the SF_BULKOPS to insert those contacts into the Salesforce.
EXEC SF_REFRESH ‘SALESFORCE’, [Contact]
CREATE TABLE [dbo]. [Contact_Load]
  (
    [Id] [nvarchar] ( 100 ) NULL,
    [Error] [nvarchar] (4000 ) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    LastName [nvarchar] ( 100 ) NULL
   )
INSERT INTO Contact_Load ( LastName) VALUES ( ‘Smith’ );
INSERT INTO Contact_Load ( LastName) VALUES ( ‘Adams’ );
INSERT INTO Contact_Load ( LastName) VALUES ( ‘Paul’ );
 
EXEC dbo . SF_BulkOps @operation = ‘Insert’, @table_server = ‘SFORCE’ , @table_name = ‘Contact_Load’
Let’s analyze what this script does.
  • The first line ‘EXEC SF_REFRESH ‘SFORCE’, [Contact]‘ refreshes the contact table from the Contact object in the Salesforce. The ‘SFORCE’ is the linked server name and the ‘[Contact]’ is the Salesforce Contact object. The reason that we have to refresh always before doing any CRUD operation is because, there could be many changes made between the last time the Contact object was refreshed and now. The SF_REFRESH brings the delta changes from the Salesforce Contact object to the local contact table in the SQL server.
  • The ‘CREATE TABLE [dbo]. [Contact_Load]…‘ creates the load table. As described previously, this table SHOULD have at least two fields which is ‘Id’ and the ‘Error’.
  • The ‘INSERT INTO Contact_Load…‘ statements inserts the records into these table. In the ideal world, the load table will be populated from a SELECT query that you will use to select the records from your tables.
  • The line ‘EXEC dbo . SF_BulkOps‘ is what actually inserts the data into Salesforce.
When you execute the script and the execution completes successfully, the contacts would have been inserted into the Salesforce Contact object. The ‘Id’ field will be populated with the Id for these new records and the ‘Error’ field will be populated with the status message. The older versions of the DbAmp used to leave the field empty, but somewhere in the documentation, I read the latest version of  DbAmp populates this field for even successful operations. Note that if you accidentally populate the Id field in the Contact_Load table with the existing record, the SF_BULKOPS will not detect that this is an existing record and will always insert this as a new record in the Contact object. In other words, if you have any records in the Contact_Load table, the SF_BULKOPS procedure will treat it as new records to be inserted and will do so.
Using Bulk API for INSERT
Salesforce.com provides two different types of APIs for sending (Insert, Upsert, Update, Delete) data to Salesforce.com. All the commercial tools such as Dell Boomi, Snaplogic, Cast Iron, Jitterbit, DbAmp, Pragmatic Works Task Factory, etc… and the open source tools such as Mulesoft, dataloader.io and even the Salesforce.com’s Data Loader use the same API to send the data to Salesforce.com. There is no other back door to deal with the Salesforce.com. The first of them which is the Web Services API is what highly used in most of the scenarios. The Web Services API is a synchronous API and can sends records in a batch of 200. That means if you 1000 records, then it makes 5 API calls to send all the 1000 records. The web services API should cater to the most integration needs because in a typical integration scenario the data moved is usually in few hundreds to thousands. But there are situations where bulk data movement is necessary which may run in few thousands to few hundred thousands of records. For such scenarios, Salesforce has a different set of API called the Bulk API. The Bulk API can support upto 10,000 records in one single API call. It is also asynchronous in nature which means that when a bulk API is used, the call is queued and executed whenever the system has the resources, which is usually, quite sooner. It is the responsibility of the caller to query and get the status of the queued call. So, if you need to send 100,000 records, with bulk API, it would take only 10 API calls, while the web services API would take 500 API calls.
DbAmp supports both the APIs and uses the web service API by default. If you need to use the bulk API, then it can be specified as an option. For e.g.
EXEC dbo . SF_BulkOps @operation = N’Insert:bulkapi’, @table_server = ‘SALESFORCE’ , @table_name = ‘Contact_Load’
 
One important thing to remember is that when you use the bulk API, the ‘Error’ column will be updated with a token and not with the status and you can use this token to track the status. To get the status, use the following command.
EXEC dbo . SF_BulkOps @operation = N’Status’, @table_server = ‘SALESFORCE’ , @table_name = ‘Contact_Load’
Conclusion
This article explored the basics of using DbAmp to insert data from local SQL database to Salesforce. There are much more things can be done with the insert operation and complete details can be found under the DbAmp documentation. The next article will focus on using the DbAmp to update the existing data in Salesforce from local SQL server.

Integrating with Force.com Platform using DbAmp: Part-02: Basics

In the first part of the article series titled ‘Integrating with Force.com platform using DbAmp’, we got introduced to the DbAmp tool and its benefits. We also went through the fundamentals and learned how it works and components of the DbAmp. In this article, we’ll look at some basics of the DbAmp as this is very essential in developing the DbAmp Jobs to integrate with Salesforce. The official DbAmp documentation is an excellent source of information to learn about the basics and this article series will touch upon few topics that you can find in the documentation, but will not go into those topics in detail, as they have been already covered in that documentation.

Execution Types

When writing SQL queries to perform any CRUD operations against Salesforce, there are two ways a developer can choose to write them.

Direct Access:

The first method is to write the queries directly against the Salesforce. In this method, the developers use specific type of syntax to access the Salesforce objects and the queries are executed against the Salesforce in real-time. The format is described as follows:

LINKED_SERVER_NAME…OBJECT NAME.

For e.g. to select all records from the Account object, you simply execute the following command (assuming your linked server name is SFORCE)

   1: SELECT * FROM SFORCE...Account

This has few drawbacks; Firstly, the performance takes a big hit as it has to execute directly against the Salesforce. Secondly, this will have a big impact on the API call limit. The reason is if you use INSERT or UPDATE in this manner, every record that gets inserted or updated count against the API call.

Local Access:

With local access method, you first replicate the required objects and store it in your local SQL server database and you write your SQL query against this table for SELECT queries. For CUD queries, you create a load table (as explained in part-1). This method has couple of advantages: Firstly, the performance is much improved, because your SELECT queries run against local database and not against the Salesforce. Secondly, as a developer, you have the option of using bulk API. With bulk API, upto 10,000 records can be sent in one single call. Also, when using the SF_BULKOPS (without the bulk api option), DbAmp batches upto 200 records in one single call.

Object-Table representation

DbAmp supports both child to parent relationship queries and parent to child queries. When replicating the Salesforce objects from Force.com platform to the local SQL server database, DbAmp uses a special mechanism to flatten the Salesforce objects in the local SQL server database. Here is a list of things that happens when an object is replicated:

  • The DbAmp creates every table with a column titled ‘Id’ which is mapped to the Object Id. Note that Salesforce never displays the Id field in the object definition. You can check my earlier article on Object Id’s to learn more about it. DbAmp also makes this field as primary key.
  • For lookup fields, the columns are created with the same name and suffixed by ‘Id’. For e.g. the ‘CreatedBy’ field becomes ‘CreatedById’ column in the local table and DbAmp stores the record id of the record it is pointing to. For e.g when replicating Case object, the field ‘CreatedBy’ is created as ‘CreatedById’ column in the local Case object and if the case is created by the user ‘John Adams’ and if the ‘Id’ of the User object for this user is ‘005014A7CA434AC’, then the ‘CreatedById’ column in the local Case object will have the value of ‘005014A7CA434AC’.
  • When a parent object in a lookup or master-detail relationship is replicated, there will be one record for each of the record in the related list of the parent object. For e.g. If an Account object has 3 contacts, then there will be totally three account records. If there are multiple related related lists, then there will be one record for each of the record in each of the related list. For e.g. if an Account object has 2 contacts with 5 cases, then there will be totally seven account records.

Handling DateTime

The value for the datetime types are always represented as UTC in Salesforce. When the user sees a value for the DateTime field, the value is represented in the user’s timezone (configured in the user’s profile). When the DateTime fields in Salesforce are manipulated using DbAmp, it is converted into local timezone. This is handled by DbAmp automatically and users need not convert back and forth from UTC to local and local to UTC. This is a very important point to remember when dealing with DateTime fields.

Using Column Subset Views

The Salesforce API has a limit on 10,000 characters for the SELECT queries; hence objects with huge number of columns cannot be replicated or refreshed who cross this limit. The DbAmp documentation mentions that if field count is above 325, the API may return error. In my experience, we got this error when the field count went above 400. Anyways, DbAmp suggest that the object can be split and then queries and this is called as Column Subset Views. In this method, the developer writes a SELECT query with a specific form of syntax to split the objects. The syntax is as follows:

   1: SELECT * FROM <LinkedServerName>...<ObjectName>_ColumnSubset<StartingAlphabetPrefix><EndingAlphabetPrefix>

For e.g. to split the Case object into two tables, the following query can be issued.

   1: SELECT * FROM SFORCE...[Case]_ColumnSubsetAM

   2:

   3: SELECT * FROM SFORCE...[Case]_ColumnSubsetNZ

   4:

Note that the Case object is enclosed by square brackets. This is because, ‘Case’ is reserved keyword and any reserved keyword in the table/column names should be enclosed by square brackets. The above query will get all the records from the Case table and will create two separate tables, named ‘Case_SubsetAM’ which will contain all the columns which start with A to M, both inclusive and another table named ‘Case_SubsetNZ’ which will contain all the columns that starts with N to Z, both inclusive. The column subset view can be used with SF_REPLICATE stored procedure as well, as follows:

   1: EXEC SF_REPLICATE 'SFORCE', 'Case_ColumnSubsetAM'

   2:

   3: EXEC SF_REPLICATE 'SFORCE', 'Case_ColumnSubsetNZ'

Governer Limits

In order to work with Salesforce data, every tool in the market has to use the Salesforce API. There is no other shortcut or preferred treatment for certain vendors. To ensure fair usage, Salesforce restricts the resource usage by putting a limit on the API calls. The following list provides high level limits on the API:

  • SELECT/SF_REPLICATE/SF_REFRESH – 1000 records => 1 API call
  • UPDATE & INSERT – 1 record => 1 API call
  • SF_BULKOPS without bulkapi switch 1 API call for each batch of 200 records
  • SF_BULKOPS with bulkapi switch => 1 API call for each batch of 10,000 records

Summary

Understanding the fundamentals of DbAmp helps developer to write better queries against Salesforce. In this article we covered such fundamentals and in the next article we will explore how records can be retrieved from the Salesforce. For list of the articles in this article series, please check the first part of this article series.

Integrating with Force.com platform using DbAmp: Part-01: Introduction

Introduction
The Force.com platform is becoming a ubiquitous platform of choice in the cloud for many organizations to move their Sales, Service and custom cloud applications. These applications or solutions cannot exist in vacuum and in almost all cases, they need to tightly integrate with the enterprise data that is housed local on-premise network or even with other cloud services such as Windows Azure or AWS. For e.g. if an organization implements a sales cloud, then they need their user information from their internal active directory to load in the Contact object, the customer lists from their SAP or other system for Account or Contact objects. Similarly custom Force.com applications might require data from the organization’s internal systems. Integration is a big concern for the organizations as they grow their presence in the cloud and it’s numerous tools and services are flooding the market to integrate with any systems.
This article series will cover one such tool called DbAmp from the ForceAmp.com. The goal here is to demonstrate the capabilities of this tool and show how the common tasks such as Insert, Delete, Upsert, Update operations can be done using this tool to accomplish the integration between a SQL Server deployed in the on-premise network with the Force.com platform. All of these topics are discussed elaborately in the following articles:
  • Integrating with Force.com Platform using DbAmp: Part-01: Introduction
  • Integrating with Force.com Platform using DbAmp: Part-02: Basics
  • Integrating with Force.com Platform using DbAmp: Part-03: Insert
  • Integrating with Force.com Platform using DbAmp: Part-04: Update
  • Integrating with Force.com Platform using DbAmp: Part-05: Upsert
  • Integrating with Force.com Platform using DbAmp: Part-06: Delete
  • Integrating with Force.com Platform using DbAmp: Part-07: Using SSIS
  • Integrating with Force.com Platform using DbAmp: Part-08: SF_REPLICATE
  • Integrating with Force.com Platform using DbAmp: Part-09: SF_REFRESH
  • Integrating with Force.com Platform using DbAmp: Part-10: SF_BULKOPS
  • Integrating with Force.com Platform using DbAmp: Part-11: Under the hood
  • Integrating with Force.com Platform using DbAmp: Part-12: Common integration techniques
DbAmp
DbAmp is a simple, yet very powerful tool, that exposes the Force.com as another database to your SQL Server. It allows the developers to use their familiar SQL (and SOQL as part of Open Query) to do all the CRUD operations on the Salesforce objects. The data on the Force.com platform can be backed up completely to an on-premise SQL Server using DbAmp with very little programming.
Benefits
Complete backup of Force.com data
DbAmp can be used to backup the data from the Force.com platform completely to an SQL Server database located on-premise. It is not limited to any specific Salesforce edition or application or objects; it can backup standard and custom objects and even custom settings for all types of Force.com cloud, such as Sales, Service, Marketing, Custom clouds, etc. However it is not a universal Force.com backup tool, meaning, it cannot backup other Force.com features such as profiles, permission sets, groups, roles, etc., because it is only a data backup tool. But it does support both full backup and incremental backup for the data. Full backups enable to completely backup all the objects with all the records, while incremental backups refreshes only the delta changes from Force.com to local on-premise database.
Build reports
DbAmp allows developers to build reports that can use data both from local repository and Salesforce in both real-time and offline fashion using simple SQL SELECT queries. For offline, DbAmp provides stored procedures (which will be covered shortly) to bring the data from Force.com platform to on-premise. Developers
Integrate local and cloud
DbAmp can be used to integrate local on-premise applications with Force.com data in both directions. That means that data can be inserted or upserted or deleted in Force.com from the on-premise SQL database.
How it works?
As outlined previously, DbAmp exposes Force.com as another database to your SQL Server. It does this by providing a OLE DB Provider to Force.com. As you might know, the SQL Server can connect to other data sources (that can support OLE DB, such as another SQL Server or Oracle, and expose them as another database using Linked Server. The following diagram (taken from the above link) illustrates this idea:
 
A3-P1-001-LinkedServer
This allows the developers to use the DbAmp OLE DB Provider to create a linked server that can connect to Force.com. Once this linked server is created, now developers can use regular SQL queries to manipulate the data on the Force.com platform. Developers can also use SOQL directly with the SQL using the Open Query, but keep in mind that there are some limitations in using the SOQL with the Open Query. Check the DbAmp documentation for more details.  One important thing to highlight here is that DbAmp takes care of all governer limits, so a developer need not to worry about governer limit exceptions when he/she tries to upsert with 5000 rows/records. DbAmp even allows developer to specify the limit per operation so that the developer can precisely control how the data should be batched.
Components
Now that we have understood how DbAmp works, let’s see the components of the DbAmp to better understand about how DbAmp works.
DbAmp OLE Db Provider
The DbAmp OLE Db Provider is used to connect to the Force.com by the linked server configuration. This is the engine of the DbAmp and takes care of all the background processes such as converting the SQL to appropriate Force.com web services API, sending the data from on-premise to Force.com and bringing the data back to on-premise from Force.com, etc.
DbAmp System Stored Procedures
The DbAmp system stored procedures does the bulk work to backup completely or incrementally. It also does the bulk inserts, deletes, updates and upserts. Here are those stored procedures.
  • SF_REPLICATE – The SF_Replicate stored procedure creates a new table which is an exact replica of a Force.com object and imports every single record from that object into the local, similarly named table. If this table already exists, it takes a temporary backup and drops and recreates this table.
  • SF_REFRESH – The SF_Refresh stored procedure synchronizes the local table with it’s corresponding Salesforce object. It deletes the records in the local table if these records got deleted in the Salesforce and gets any all the records that was added in Salesforce and doesn’t exist locally and the records whose fields got updated in the Salesforce.
  • SF_BULKOPS – The SF_Bulkops stored procedure is the key stored procedure that is used to send any new records or updated records in Force.com and even to delete any existing records.
There are few other stored procedures that ships with DbAmp, but these are the most widely used stored procedures. In a later article in this article series, we will explore these three stored procedures in depth to gain deep understanding about how it affects the way a developer designs/develops the integration job.
Anatomy of a DbAmp Job
Using DbAmp to integrate on-premise data with Force.com requires SQL skills, as the developers will write their integration entirely using SQL. The DbAmp integration can be either done using regular SQL jobs or SSIS packages. For the most part, this article series will be using only SQL jobs to implement the integration.
SQL Jobs
The anatomy of a SQL job that uses DbAmp slightly varies depending on the task involved and the time it runs. For uploads, we first refresh the local tables and populate a load table and then perform the upload operation (CUD). The load table is a temporary (physical, but temporary in nature) table that mimics the Salesforce object. The load table name should start with the name that is similar to the object that it deals; for e.g. to perform upload operation on ‘Case’ object, the load table name can be ‘Case_Load’ or ‘Case_BillingIssues_Load’, but it can’t be ‘ABC_Load’, etc. The load table need not have every column as in the Salesforce object and can have fields/columns that the job is going to affect. The only other column that is required is named ‘Error’ which will be used by the DbAmp to update the status information after the SF_BULKOPS call. The load table can be dropped or left as it is after the SF_BULKOPS call. In general, it’s a best practice to first check for its existence and create it if it doesn’t exist and to drop it just before the end of the logic in the SQL job.
At a high level the anatomy of a DbAmp SQL job look like as follows:
To upload data from local on-premise SQL database to Force.com platform for frequent jobs.
  • Refresh the local table(s) from the Salesforce objects using SF_REFRESH (or it’s other variants)
  • Create the load table
  • Populate the load table with the data that needs to be sent to Salesforce
  • Use SF_BULKOPS to upload the data. The upload operation can be ‘Insert’, ‘Update’, ‘Upsert’, ‘Delete’
  • Check ‘Error’ column in the load table to manage the error handling scenarios.
To upload data from local on-premise SQL database to Force.com platform for nightly jobs.
  • Replicate the local table(s) from the Salesforce objects using SF_REPLICATE (or it’s other variants)
  • Create the load table
  • Populate the load table with the data that needs to be sent to Salesforce
  • Use SF_BULKOPS to upload the data. The upload operation can be ‘Insert’, ‘Update’, ‘Upsert’, ‘Delete’.
  • Check ‘Error’ column in the load table to manage the error handling scenarios.
To upload data from Force.com platform to local on-premise SQL database for frequent jobs.
  • Refresh the local table from the Salesforce objects using SF_REFRESH (or it’s other variants)
  • Perform CUD (Create, Update, Delete) operation(s) on your other databases retrieving data from the refreshed table(s)
To upload data from local on-premise SQL database to Force.com platform for frequent jobs.
  • Replicate the local table(s) from the Salesforce objects using SF_REPLICATE (or it’s other variants)
  • Perform CUD operation(s) operation(s) on your other database retrieving data from the refreshed table(s).
Conclusion
This article introduced the DbAmp, a tool to integrate local on-premise databases with Force.com data and discussed about the benefits and the underlying mechanism of how it works. It also discussed the basic constructs of the DbAmp tool and the anatomy of a DbAmp SQL job. In the next part, we will explore how the data can be inserted from local SQL database to Force.com using a DbAmp SQL job.
Stay tuned.

Demystifying Object IDs in Force.com platform

The demystifying series will take a single concept, idea, technology, standard, feature and will dissect it and provide a thorough understanding about that particular thing.

In Salesforce.com platform, the fundamental mechanism of storing data is through objects. Objects define the schema for storing related information for a particular ‘thing’. This ‘thing’ can be either standard objects such as Accounts, Contacts, Cases, Leads, Opportunities or custom objects that the users define to meet the business requirements. Objects can contain standard fields such as CreatedBy, LastModifiedBy and custom fields that the users define. A record is an instance of an object and every object can have zero or more records. Every record has a unique identifier represented by a standard field named ‘ID’. The subject of this blog post is all about this ‘ID’ and how it plays an important role in handling the objects and the relationships.

Note: I’m going to use ‘ID’ (upper case) to denote the ID type, while ‘Id’ (proper case) to denote that it is a value.

ID

The ID field is defined automatically by the platform whenever an object is created. The ID field is indexed and it is never shown in the field definition, hence this field cannot be edited or deleted. The ID field acts like a primary key for each record, hence every record can be uniquely identified using an ID. The value of this field for a record cannot be updated either through UI or through the web services. The IDs are unique within an organization and they are represented as either 15 digits or 18 digits. The 15 digit ID is base-62 encoded and is case sensitive and contains alpha-numeric characters (0-9,a-z,A-Z). The 18 digit ID is base-62 encoded and is case-insensitive and contains alpha-numeric characters (0-9,a-z,A-Z). The 18 digit IDs are introduced since some applications like Microsoft Access/Visual Basic are case-insensitive and do not distinguish between 00B30000007Mlka and 00B30000007MLKA.

The first 3 characters of an ID indicates what type of entity it references. For e.g. the following table list the first 3 characters for various entities:

# Entity Type Starts With
1 Groups/Queues 00G
2 Profiles/Roles 00E
3 Permission Sets 0PS
4 Object Records

Starts with a alphanumeric such as 5003, 0010.

The Salesforce UI always uses 15 digit IDs when viewing/editing records of the objects, users, roles, groups, profiles and permission sets, while web services always use 18 digit IDs. To get the 15 digit IDs from 18 digit, simply get the first 15 digits and ignore the last 3 digits. Salesforce recommends to use 18 digit IDs as the web service API’s always use 18 digits.

IDs in Relationship

The IDs are used as references field types in relationship fields. This means that when a lookup or master-detail relationship field is defined, it uses the ID to reference to the lookup or the master object that it refers to. There are subtle differences how the Ids are used in the ID fields and the reference fields.

# ID Type Fields Reference Fields
1 The Ids are generated by the platform and the user/developer cannot update the ID field The reference fields can be updated with the Ids from other objects
2 The ID fields are not displayed in the Field definitions The reference fields are always displayed in the field definitions
3 The Id of a record is always unique within an object. For e.g. every case record will have a unique id. The Ids in the reference fields are not unique. For e.g. the Last Modified By field can have the same user id
4 An object cannot have a null Id The reference fields in an object can be null for lookup objects, but in master-detail objects, the Id cannot be null

Relationship fields such as lookup or master-detail relationship fields are always defined in the child. When the user creates a new record, the platform automatically provides a lookup icon next to the field to choose the value from the related object. When the user select a value from the lookup dialog window and saves it, the platform saves a ‘reference‘ in the child object to the related object. This ‘reference‘ is the ‘Id‘ from the related object. But when you view this record, you would see the lookup value which is the record name (defined by the ‘Record Name’ during the object creation) and not the related object’s id.

Let’s understand how this works with an example. I’m going to use DbAmp for this example. DbAmp is an integration tool that exposes Salesforce as another database in your SQL Server. The advantage with this approach is that you can use regular SQL (not SOQL) to to all your CRUD operations. I’m not going to show how to setup the DbAmp as this is not in scope and the assumption here is it is already setup and ready for use.

For this demonstration, I created a new object named ‘Position’. You can see that the Position object definition screen doesn’t have the ID field. Also, note the standard lookup fields that are created for this object. These fields such as ‘Created By’, ‘Last Modified By’ are lookup fields. We will cover these fields shortly.

A3-P2-PositionDefinition(1)

Image 1: Position object definition

The following screenshot shows how the position record looks like when it is created. You can see that the ID field is never displayed in the UI (because it is not part of the field definition), but the URL has the Id which uniquely indicates this particular record. Actually, Salesforce always uses Id in the URLs to access the records for both viewing and editing. The highlighted fields indicate the lookup fields and in this case these fields are standard fields which gets created automatically when an object is created and populated by the platform every time a record is created.

A3-P1-NewPositionRecord

Image 2: A position record seen through UI

Let’s switch back to SSMS to see how this record looks like.

A3-P1-PositionRecord

Image 3: A position record seen through DbAmp

As previously said, DbAmp allows to manipulate records using regular SQL and the above command gets all records from the Position__c table (mapped to the Salesforce object Position__c). If you compare the image 1 and 3, you can find couple of differences. First, there is a difference in the field names; for e.g. Image 1 shows the field name as ‘Created By’ and ‘Last Modified By’, while the Image 2 shows it as ‘CreatedById’ and ‘LastModifiedById’. The reason is that every field has 2 fields to represent the field name;

  • Field Label – used in the page layouts
  • Field Name/API Name – used by the web services.

The standard fields use the ‘Field Name’ convention, while the custom fields use the ‘API Name’ convention. Nevertheless, the reason the Image 3 shows these fields as ‘CreatedById’ and ‘LastMofidiedById’ is because, DbAmp knows these fields are lookup fields, hence it appends the ‘Id’ to the column name.  Second, in the UI, the Id is 15 digits (which is displayed in the URL), but in DbAmp, the Id is 18 digits. The reason is that the web services always use 18 digit and DbAmp uses the web services behind the scenes to convert the SQL to web service API call to perform the CRUD operations. The point to note here is that the relationship fields use Ids as reference to link other objects and these Ids can be updated in the relationship fields, while the ID field cannot be updated.

IDs in CRUD

IDs play a crucial role in the CRUD operations. In fact, most CRUD operations use IDs in one way or another. The following sections details how the CRUD operations use IDs.

Create

As explained previously, whenever a record is created, the ‘ID’ field is auto-populated by the platform and the user has no way to update or delete. A record in Salesforce object cannot exist without an ID and it is always unique within the organization.

  • Create through Web Services: When the Create() method in the web service is called, it creates a new record and returns the 18 digit ID that the platform creates as part of the record creation.
  • Create through UI: When a new record is created through the Salesforce UI, it always displays the 15 digit ID.

Upsert

Upsert either creates new record if it doesn’t exist or updates if it exists. The upsert() call can be used in custom objects only if the object has an external field. When upsert() method is called, it uses the ID field to see if a record already exists. If it exists, then it updates; otherwise, it creates a new record and returns the 18 digit Id from the newly created record.

Update

Update can accept either the ID field or any other field in the condition list. But it cannot accept the ID field in the updateable list. If one or more Id is listed in the condition, the same number of records are updated, while if a non-Id is used in the condition list, it may update 0 or more number of records depending on how many records matches the condition.

  • Update through web services: Certain fields of an object (both in standard & custom objects) can never be updated such as ‘Created By’, ‘Last Modified By’, ‘Last Modified Date’, etc. The ID field is one such field. However, the reference (lookup & master-detail relationship) fields can be updated such as ‘OwnerId’.
  • Update through UI: Similar to web services, certain fields cannot be updated including the ID field, while the reference (lookup & master-detail relationship) fields can be updated.

Delete

Delete can accept either the ID filed or any other field in the condition list. If one or more Id is listed in the condition, the same number of records are deleted, while if a non-Id is used in the condition list, it may delete 0 or more number of records depending on how many records matches the condition.

Conclusion

The concept of ID is very simple, but not effectively understood many times. Understanding how the IDs are used in the Force.com platform helps developers to design and write applications in Force.com platform effectively.

Securing IBM WebSphere Cast Iron Appliance while integrating with Salesforce-Part V

Introduction

This article is the fifth and final part of the article series titled ‘Securing IBM Websphere Cast Iron while integrating with Salesforce. In this article, we will uncover the final piece of the puzzle: authenticating the incoming requests using web services.

Authenticating the user requests

Since IBM Websphere Cast Iron server is in DMZ, it is exposed to the public internet which enables to be accessed from any device. We saw couple of techniques to safeguard the access to the server at various levels from firewall to transport layer in the previous articles. At the application level, the IBM Websphere Cast Iron doesn’t provide out of the box security such as ‘Authentication’ or ‘Authorization’. To overcome this limitation, this article proposes a technique – using external web service to authenticate the client requests. ‘Authentication’ provides the ability for the web service host to challenge the client to provide access credentials, typically user name/password, but not limited to, to enable the access to provide integration capabilities. Authentication process generally involves in using the standard authentication mechanisms such as Windows Authentication, Kerberos or Forms based authentication. Covering the details of these authentication mechanisms is out of scope of this article. Hence this article will focus on providing a simple way to authenticate the client requests using standard web services technology.

As previously said, the IBM Websphere Cast Iron server doesn’t provide any type of authentication or authorization capabilities out of the box. So, a simple technique to overcome this limitation would be to implement the authentication and authorization services in an external stack such as J2EE or Microsoft .NET as standard web services and utilize these web services to provide the authentication/authorization capabilities. These web services in turn can implement any type of authentication mechanism from Windows to Kerberos to Forms based; using either user name/password credentials or a security token based credentials or simply oAuth based authentication.

This article will implement a simple web service based on .NET WCF web services technology to provide the authentication service. The Cast Iron orchestration can utilize this web service to authenticate the incoming requests and route the call or perform further integration logic or simple reject the call based on the result from this web service. The following diagram captures this flow:

AuthenticationProcess

Implementation

Cast Iron Orchestration – This Cast Iron orchestration is an example for calling an authentication web service hosted in external system. The code for the Cast Iron orchestration can be downloaded from here.  The following image is extracted from the Websphere Cast Iron Studio that demonstrates the flow of the orchestration.

TestRequestWithExtAuth

Authentication Web Service – The authentication web service is a simple .NET WCF web service that takes the security credentials as an XML dataset and returns the authentication status.

Since the article’s purpose is to demonstrate the technique, this web service doesn’t implement any particular authentication mechanism. But as the reader can observe, it is fairly easy to implement any type of authentication mechanism within this web service. The code for the .NET WCF web service can be downloaded from here.

Summary

This article series exposed the security challenges in integrating Salesforce with on-premise applications in the context of IBM Websphere Cast Iron as the integration platform. It also explored various techniques from firewall rules to transport level authentication to custom validation service to external authentication service to address these challenges. Neither the security challenges nor the solutions are complete, but it gives an idea about the security concerning the cloud to on-premise integration and various solutions to address them. The techniques explained here can be implemented with any integration platform and not limited to IBM Websphere Cast Iron, though the implementation details will differ.

Securing IBM WebSphere Cast Iron Appliance while integrating with Salesforce-Part IV

Introduction

This article series discusses about securing IBM Websphere Cast Iron Appliance when integrating with Salesforce. In the previous articles, we discussed about the security challenges and some of the methods to address those challenges including protecting the enterprise network using Firewall, SSL/Certificate based authentication. In this article, we will see how requests from cross-organization to cross-environment (from Salesforce to on-premise) can be prevented. This method also protects your organization’s web services from being accessed from other enterprises who are co-located along with your organization’s orgs. Implementing this method along with the solutions described in the previous articles is a powerful method that will secure your enterprises web services from almost any type of unauthorized access.

Background

In a typical enterprise, multiple environments are created to promote development to production. The usual model is to have one single environment for DEV, one for QA, one for Staging and another for Production. The number of environments may vary depending on the organization’s needs, but the bottom line is that there will be multiple organizations (Sandboxes) on the Salesforce side and matching environments at on-premise with mapping from Salesforce orgs to these local environments. At on-premise, each of these environments are either physically or logically separated using various technologies such as NAT and firewall rules are implemented to prevent cross environment access such that DEV servers can talk only to other DEV servers, QA servers can talk to other QA servers, etc. The bottom line is that applications from one environment cannot call into the applications/databases/services in another environment. But this type of implementation is not possible with Salesforce, because Salesforce doesn’t have the concept that we just explained above. Further we explained in part-2 about how the requests originates from Salesforce. To recap, Salesforce has a set of IP addresses for each region (such as North America, Asia Pacific, etc…) and the web service callouts made by the Salesforce client applications carry one of this IP address as their source address. Salesforce doesn’t have the concept of different network segments for the web service callouts, hence by default, there is no way for the enterprises to distinguish the requests to identify if it come from sandbox or production org or even from their own orgs. But not all is lost. The solution proposed here will address this problem, by not only, preventing the cross-organization to cross-environment access, but also prevents access from other enterprises orgs.

Solution

The solution to this problem involves some custom coding along with a Salesforce provided feature called ‘Organization Id’. Every Salesforce organization has a unique identifier called ‘Organization Id’. The ‘Organization Id’ can be found under Your Name | Setup | Administration Setup | Company Profile | Company Information.

A2-P4-01-OrganizationId[4]

Figure 1: Getting the Organization Id

The ‘Organization Id’ is unique for each Salesforce org. It is not only unique with your enterprise’s Salesforce orgs, but also unique universally across all orgs of all it’s clients. That means that if your web service client in Salesforce embed this ‘Organization Id’ in the request, then a web service can catch that and can prevent the call from executing if the ‘Organization Id’ is not in their access control list. This is what exactly we are going to do that. Here is a simple flowchart that describes this technique.

OrgValidation-FlowChart[4]

Figure 2: Flow chart that depicts the solution

As shown in the flowchart above, the client (Salesforce) will send the ‘Organization Id’ along with the input payload when it makes a web service callout to the Cast Iron. Apex provides the User API through which the ‘Organization Id’ can be retrieved which suits well for apex code. We get the ‘Organization Id’ from this object and embed it in the HTTP headers. For outbound SOAP messages, the outbound workflow action itself provides the ‘Organization Id’. Cast Iron will retrieve the ‘Organization Id’ from the request and validates against it’s configuration. If the ‘Organization Id’ matches, it continue to process, otherwise, the web service exits immediately, returning an error code. The following sections will provide step by step details along with the necessary code that implements this pattern.

Developing the OrgValidationService Cast Iron Orchestration

Since we want to validate all the incoming requests, it is best to develop the validation as a separate web service, so that all other web services can call this one web service to validate the ‘Organization Id’. We will call this as ‘OrgValidationService’. This web service will be consumed by all other Cast Iron web services that want to validate the organization ids. The following image is extracted from the Cast Iron studio that depicts the flow.

A2-P4-CI-01-OrgValidationService[4]

Figure 3: OrgValidationService

Previously, we saw that the client has to send the ‘Organization Id’ along with the input payload. This can be done with couple of ways. I preferred to embed the ‘Organization Id’ as part of the HTTP headers, because, this is less intrusive. Had we chose to embed this as part of the input request parameters, instead of putting into HTTP headers, then the XML schema for all of these web services need to be updated to include the ‘Organization Id’ parameter. Here is the logic:

  • The web service receives the input parameter and assigns it to input variable named ‘objOrgInfoRequest’. While assigning we filter it for the value ‘Organization Id’. Please note that apart from the HTTP headers, you will also notice another parameter named ‘OrganizationId’, which will be used if web service callout is made by outbound SOAP message. The following screenshot shows how the ‘Organization Id’ is passed through the HTTP headers.

A2-P4-CI-02-OrgInfo-HTTPHeaders-Mapping[4]

Figure 4: Filtering the input headers to get the ‘Organization Id’

  • The valid ‘Organization Id’ for that particular environment is configured in a variable named ‘OrgIdLookup’. This is retrieved and assigned to a variable.
  • The web service checks the ‘Organization Id’ that came as part of the input parameter and checks against this configured value.
  • If it matches, then it assigns the status to ‘true’, otherwise, it assigns the status ‘false’.

The code for this orchestration can be downloaded from here.

Developing the Test Web Service Cast Iron Orchestration

The Test Web Service Cast Iron orchestration will serve as an example of how the regular Cast Iron orchestrations (that will be developed to support your organization’s business requirements) should utilize the OrgValidationService to validate the ‘Organization Id’. The following image is extracted from the Cast Iron studio.

A2-P4-CI-03-TestRequestService[3]

Figure 5: TestRequestService Web Service to be consumed by Apex code.

This orchestration is exposed as a web service which will be consumed by your apex code. It copies the HTTP headers to a local variable and calls the OrgValidationService passing this variable. If the return value from the OrgValidationService is true, then the orchestration proceeds to execute, otherwise, it terminates immediately. The code for this orchestration can be downloaded from here.

Consuming the OrgValidationService in Salesforce

There are couple of ways the Salesforce can be programmed/configured to consume a web service that is hosted elsewhere. The right solution depends on the needs, but they are as follows:

  • Apex code
  • Outbound SOAP message
  • AppExchange App

Consuming the Test Web Service from Salesforce (through Apex code)

With apex code option, the consuming the Test Web Service from Salesforce is done completely by coding in Apex. Developers choose this option when they want to

  • pull data from multiple objects and pass them as input parameters
  • process the data and derive the input parameters for the web service

Here are the steps to consume the test web service from Salesforce using the apex code.

  • Generate the WSDL from the Cast Iron web service and save it to a folder
  • Login into your dev/sandbox org and go to the section ‘Apex Classes’ under Your Name | Setup | App Setup | Develop.
  • Click ‘Generate from WSDL’ and choose the file that you saved from the Cast Iron studio.
  • If you want to rename the default names provided by the import wizard, go ahead and change the file names. I renamed the files as follows:

A2-P4-CI-05-GenerateFromWsdl[4]

Figure 6: Naming the system generated classes in Salesforce

  • Click ‘Generate Apex Code’ button and click ‘Done’.

Now we need to create a wrapper class that utilizes the stub generated by the import wizard. To do this, click ‘New’ under Your Name | Setup | Develop | Apex Classes. Copy the code given below. This code consumes the test web service by utilizing the stub that we just generated.

public class TestRequestServiceClient {
    @future (callout=true)
    public static void Test() {
        Boolean isSuccess = true;
        try {
            TestRequestServiceWsdl.Provide_ServicePort binding = buildService();
            List<TestRequestServiceRequest.TestRequest_element > elementList = new List<TestRequestServiceRequest.TestRequest_element >();
            TestRequestServiceRequest.TestRequest_element element = new TestRequestServiceRequest.TestRequest_element ();
            binding.Provide_Service('1', 'Test');
            isSuccess = true;
        } catch (Exception e) {
            isSuccess = false;
        }
        //return;
    }
    private static TestRequestServiceWsdl.Provide_ServicePort buildService() {
        TestRequestServiceWsdl.Provide_ServicePort updateStatusInstance = new TestRequestServiceWsdl.Provide_ServicePort ();
        updateStatusInstance.endpoint_x         = 'https://yourcastironwebserver:443/TestRequestService';
        updateStatusInstance.inputHttpHeaders_x = new Map<String, String>();
        updateStatusInstance.inputHttpHeaders_x.put('OrganizationId', UserInfo.getOrganizationId());
        updateStatusInstance.inputHttpHeaders_x.put('OrganizationName', UserInfo.getOrganizationName());
        System.debug('\nOrganization Id :' + UserInfo.getOrganizationId());
        updateStatusInstance.timeout_x          = 60000;
        return updateStatusInstance;
    }
}

Click Save.

Consuming the OrgValidationService in Salesforce (outbound SOAP message)

With outbound SOAP message option, the outbound SOAP action sends the ‘Organization Id’ as part of the request payload. This is the reason that we designed our OrgValidationService to accept both HTTP headers and a separate parameter named ‘Organization Id’ to support both scenarios (Apex code and outbound SOAP messages).

Here is the test web service built to be consumed by an outbound SOAP message. The outbound SOAP message definition provides you the WSDL and the following screenshot shows the test web service implmented using this WSDL. The code for this web service can be found here.

A2-P4-CI-04-TestRequestServiceO[3]

Figure 7: ‘TestRequestServiceO’ web service to be consumed by Outbound SOAP message

Now we have completed all the development tasks. Before start testing, you need to perform the following tasks:

  • Deploy all the three Cast Iron orchestrations in your Cast Iron server (preferably in your development environment)
  • In OrgValidationService, configure the ‘OrganizationId’ configuration property to the organization id of the Salesforce org that you are going to test. You can get the organization id from the ‘Company Information’ link under Your Name | Setup | Company Profile‘ as shown in the Figure 1 above. Note that the organization id that is displayed here is 15 digit and you need to use the 18 digit version, as otherwise, your OrgValidationService will fail. [You can get the 18 digit through many ways. One way is to let it run against your cast iron web service and look at the cast iron log and you will find the 18 digit id (set the log level to ‘All’ in your cast iron server).
  • In Salesforce, update the ‘endpoint_x’ variable in the TestRequestServiceWsdl to point to your cast iron server and save it. (I renamed the system generated file to ‘TestRequestServiceWsdl’. If you choose a different name or if you didn’t rename, then the file name will be different in your case.

With this, we are all set to test it. Invoke the developer console from Your Name | Developer Console and execute the following: TestRequestServiceClient.Test(); Now if you open the debug logs, you will see that the call succeeded. If you check your Cast Iron logs, you would see log message titled either ‘Request came from authorized organization’ or ‘Request came from unauthorized organization’ depending on what value you have configured for the ‘Organization Id’ configuration property in the OrgValidationService orchestration.

Summary

This article is the fourth in the five part article series. The first part described the security challenges. The second part explained how the enterprise can use firewall to filter unwanted traffic other than the trusted networks. The third part continued to explain about transport level encryption. The fifth part will now explain about authorizing the trusted users. This scenario is particularly important if you are going to use Cast Iron server to integrate with other systems including other cloud services such as Docusign, Amazon elastic cloud, Windows Azure, etc.

Securing IBM WebSphere Cast Iron Appliance while integrating with Salesforce-Part III

Introduction

IBM Websphere Cast Iron can solve many integration problems without the traditional coding approach. This article series discusses about the security challenges when integrating on-premise applications with Salesforce. The first part of this article series introduced the security challenges and the second part continued with first of a set of solutions to secure the integration assets located at on-premise. In this article, we will discuss about adding security at the transport level.

TLS/SSL Authentication

Any information that goes in the public internet can be intercepted. Therefore, adequate security should be implemented at the transport level to ensure that the data is completely encrypted and unreadable from the prying eyes. The standard method to secure the transmission of data is by using the TLS/SSL authentication. Basically, in TLS/SSL authentication, the data is secured at the transport level by encrypting all the data that goes through the public internet.  The TLS/SSL authentication not only encrypts the data, but also provides the source and the target the ability to prove their identities. This is a great feature, specifically when integrating with the Salesforce. Recall the  security challenges that we discussed in part-I, where it was mentioned that the requests from Salesforce carry the IP addresses and not the Salesforce.com or other related domain names. Also, these IP addresses are shared by every other enterprises whose orgs are co-located in the same set of IP ranges. So, how does exactly the TLS/SSL solves the problem? The answer lies in the following sections.

One-way SSL/Certificate Authentication

In one-way SSL/Certificate authentication method, the provider secure the data transmission by implementing SSL/certificate based authentication. When a client makes a request to the server, the server proves its identity by providing the following information:

  • A digital certificate from the service provider with the provider’s information, such as Company name, address, phone, email, etc.
  • A digital certificate from the public Certification Authority (CA) such Verisgn, DigiCert, etc.
  • A chain of trust certificates if there are any intermediary CA’s.
  • An encrypted public key which the client can use to decrypt the data that the server uses to encrypt the data being transmitted

When the client receives this information, it verifies the provider’s digital certificate and upon successful verification, it establishes the secure communication with the server. With this method, the data is encrypted end-to-end throughout  the session. I have written a complete walkthrough on implementing one-way SSL/Certificate authentication here as part of five part article series on Making authenticated web service callouts from Salesforce to IBM Cast Iron Using SSL/Certificates. This solves the problem of the following questions:

  • Will the data be encrypted? If so, do we need to have the clients prove its identity?
  • What type of authentication do we need to use?

Two-way SSL Authentication

In two-way SSL authentication method, both the client and server proves their identities to have a secure communication. Once the mutual handshake is successful, a secure communication channel is established and the data transmission takes place on this channel where the data exchange is completely encrypted and can be decrypted only by the source and the target systems. A thorough walkthrough on the concept of two-way SSL/Certificate authentication with working example can be read in my previous article as part of the five part article series on Making authenticated web service callouts from Salesforce to IBM Cast Iron Using SSL/Certificates

This solves the following problems that we raised in the first article.

  • Will the data be encrypted? If so, do we need to have the clients prove its identity?
  • What type of authentication do we need to use?
  • What systems can access our Cast Iron server?
  • Who can be allowed to access our Cast Iron server?

The two-way SSL/Certification based authentication also solves another problem that we discussed in part-I, which is how to make sure if the request originated from our own orgs instead of some other enterprise’s org which is co-located with our org. Since, in two-way SSL/Certificate based authentication, both the parties need to prove their identities, if a request comes from somebody whose identity couldn’t be verified (using SSL/Certificates), then the server rejects the call.

Summary

This article discussed about using SSL/Certificate based authentication on securing the data communication between a server and client. In the next article, we will discuss about how to protect cross organization calls, so that even inadvertent mistakes of pointing to incorrect environments will be detected by the server and prevented from execution.

Securing IBM WebSphere Cast Iron Appliance while integrating with Salesforce-Part I

Introduction

The IBM WebSphere Cast Iron is a cloud integration platform that enables enterprises to integrate applications without any traditional coding. While the IBM Websphere Cast Iron can be used for almost any type of integration, it is most widely used to integrate the Cloud applications (SaaS, PaaS) with the on-premise applications. Integrating cloud applications with the traditional on-premise applications introduces some new challenges, particularly, in terms of security. An organization needs to carefully design their network security in order to avoid any pitfalls as otherwise it has to face several issues from compliance to customer dissatisfaction to data theft to lawsuits …you name it and you have it. We will see some of the key security questions that needs to be answered when we design our network/application security.

This article series will cover several solutions to secure your Cast Iron server. This is by no means a complete list, but covers most widely used scenarios.

Background

The enterprises typically have a separate network zone called DMZ through which all the communication from internal systems & applications that interact with the public network goes through. It is a common and best practice to deploy the Cast Iron server in this DMZ which acts like a middle-tier that integrates the cloud applications with on-premise applications. The following snapshot provides a rough idea on how this looks in a typical enterprise.

CI-01

This architecture offers several advantages such as

  • a central place to manage all your incoming / outgoing requests
  • need to open only the required ports, specifically, HTTP/S ports

Since this is going to be a gateway to your enterprise applications, securing the Cast Iron server is very important as explained in the ‘Introduction’ section. Several key questions arise while designing the security for integrating Salesforce cloud with our on-premise solutions using IBM Websphere Cast Iron server.

  • What systems can access our Cast Iron server?
  • Who can be allowed to access our Cast Iron server?
  • Will the data be encrypted? If so, do we need to have the clients prove its identity?
  • How do we make sure cross org/environment calls are blocked?
  • What type of authentication do we need to use?

As you can guess, there is no single solution that can satisfy all the security requirements and we need to implement multiple solutions in order to secure the organization’s integration assets. This article is going to address these questions and will propose multiple solutions answering all these questions. Now, some of the questions may not be an issue at all for some organizations and in those cases, it is okay to not implement them. For e.g. two-way SSL authentication may not be necessary, provided, firewall access restrictions, one-way SSL authentication along with Organization based authentication are implemented.

This article series will provide in-depth insight along with practical solutions to address the security concerns. The article series will be divided as follows:

Let’s dive into the details. The Part-2 of this article series will discuss about the Firewall Access Restriction.

Managing the invocation of scheduled jobs in IBM WebSphere Cast Iron

Introduction

IBM WebSphere Cast Iron facilitates to run the orchestrations in many ways; web services, HTTP Receive, Scheduled job, etc. Scheduled jobs are good candidate when you need to run an orchestration periodically such as once in a minute, once in 5 minutes,  once in a day, etc. However, the challenge here is that performing QA on the scheduled jobs may become an issue as the QA team will have very little time to no time to prepare the test data as the scheduled job will kick off at the predetermined time. This is very true for those scheduled jobs whose frequency is very short such as once in one minute, 5 minute. On the other side, the scheduled jobs whose frequency is longer is hard to test as the QA team has to wait till the scheduled job kicks off. This article will present a technique to address this problem that can make the QA process very easy.

HTTP Receive and Conditional Check

The technique here is to design  the orchestration to have both the Scheduled Job activity and HTTP Receive activity and have a Pick activity in before them so that the orchestration can be either invoked as a scheduled job to run at a predetermined time or can be invoked manually by using a HTTP Post. Actually this is a very popular technique and nothing new here. But this technique only allows the user to manually trigger the orchestration on top of the ability to run it at a predetermined time. Though the QA team doesn’t need to wait to get the orchestration invoked automatically at the predetermined time and invoke it whenever they want by using the HTTP request, the problem here is that it still has the Scheduled job activity which will get fired when the time elapses which once again may impact with the test data. To overcome this, add a configuration property named ‘IsAutoRunEnabled’ and set it to ‘N’ (without the quote). Now create a string variable named ‘isAutoRunEnabled’ and drag and drop a ‘Map Variables’ activity into the designer pane. Select this variable by clicking the ‘Select output’ and right click the variable and click ‘Define default value’. Choose  the configuration property named ‘IsAutoRunEnabled’ that we just now defined. Publish this orchestration to the runtime and try configuring it with ‘Y’ and ‘N’ to enable and disable the job. The following screen shot illustrates how the orchestration looks like.

A2-P1-Orchestration-01

Fig 1a. Orchestration with the if condition to check whether the Orchestration can continue to run.

A2-P1-Orchestration-02

Fig 1b. Configuring the map variable to read the configuration property

A2-P1-Orchestration-03

Fig 1c. The IF Condition.

Making authenticated web service callouts from Salesforce to IBM Cast Iron using SSL/certificates–Part V

Introduction

This article is the fifth and final in the five part article series on making authenticated web service callouts from Salesforce to IBM WebSphere Cast Iron. In this article, we will cover some of the issues that we come across in implementing SSL/Certificate based security and the solution to fix them. This is not an exhaustive list, but these are the most common problems one may face while implementing this type of security.

Certificate issues

PKIX path building failed

Exception message

sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

Solution

This exception can happen due to various reasons. The following list of actions might solve this issue.

  • If you are using two-way certificate authentication, then check whether you have included the certificate on your client side when you make the web service callout.
  • Make sure the certificate is not expired and valid.
  • Make sure to include the certificate if you enable two-way certificate authentication.

IO Exception: DER input, Integer tag error

Exception message

IO Exception: DER input, Integer tag error

Solution:

This exception can happen due to various reasons.

  • As explained previously, Salesforce will accept PKCS#12 certificates; if your certificate is DER/PEM, then you will receive this error. Once you use the PKCS#12 certificate this error should go away.
  • When you embed the third party certificate in the code and if the certificate content is tampered or if you incorrectly paste the certificate content, then you will receive this error

SSLPeerUnverifiedException: peer not authenticated

Exception message:

 javax.net.ssl.SSLPeerUnverifiedException: peer not authenticated.

Solution:

There are many situations where this error can happen. This can be summarized as:

  • If your certificate has a chain of trust (which means that the certificate has intermediate certificates), then the order of the certificates in the chain has to be correct. The order is defined as follows:
    • The server certificate
    • The intermediate certificate that have signed your server certificate (only if the server certificate is not signed by root certificate)
    • The intermediate certificate that signed the above intermediate certificate
    • Include all the intermediate certificates as defined in the previous certificate except the root CA (this is usually already available in your server’s trust store)
  • One or more of the certificate in the chain has expired or not valid.
  • One or more of the intermediate certificate in the chain is missing.
  • One or more of the certificate in the chain is either a self-signed certificate or not trusted by Salesforce. The list of the CA that Salesforce supports can be found here.

IO Exception: Unable to tunnel through proxy.

Exception message:

IO Exception: Unable to tunnel through proxy. Proxy returns \"HTTP/1.0 503 Service unavailable

Solution:

This error happens if your firewall doesn’t allow access to your server where the web service is hosted. Making the firewall changes should fix this issue.

In general, when you see an issue, follow this check list to troubleshoot the issue:

  • Make sure you have the firewall settings in your enterprise is configured to allow the inbound web service call.
  • Check whether the certificate is expired or not.
  • Check if your chain of trust has valid certificates.
  • Make sure you embed the PKCS#12 certificate on the Salesforce side when you make a web service callout.

Most vendors provide tools to check your server/certificate. DigiCert’s tool can ping your server and retrieve the certificate and can provide you the report. Similarly, Verisign has its own tool to validate the certificates.

Conclusion:

This article series had in-depth analysis of how to make authenticated web service callouts from Salesforce to IBM WebSphere Cast Iron both one-way SSL/Certificate and two-way SSL/Certificates. Though this article uses Salesforce and IBM WebSphere Cast Iron as examples, the concept applies elsewhere whether you are making the authenticated web service callouts from Java client to .NET WCF Web Services or from Windows .NET Azure Web Services to SAP, albeit the implementation details will differ.

%d bloggers like this: