Sunday, September 22, 2019

Getting Started with Logic Apps - AS2

What is AS2?

Every enterprise requires some kind of product, service or counselling from another enterprises thus there happens a B2B(business to business) communications, some of the communication is unique whereas most of the communications are common for most of the enterprises e.g., Purchase Order. But every enterprise has there own way(format) of sending the messages, leading to difficult management when number of partners increased. 

Considering this the Enterprise leaders decided to have some standards/rules defined across the communication leading to B2B protocol standards which provide guidelines for trading partners to follow when conducting business between enterprises. EDI X12, EDIFACT, TRADACOM etc.

AS2 stands for Applicability Statement 2 and is a B2B messaging protocol used to transmit Electronic Data Interchange (EDI)/Business documents from one organization to another. So EDI standards define how to format data and AS2 specifies how to securely transport data via the Internet using HTTP/S (hypertext transport protocol secure). AS2 uses the HTTP POST operation to send business data. In response to an AS2 message, a message disposition notification (MDN) is returned as an acknowledgment, provided the sender of the AS2 messages expects an MDN.

Note: Although AS2 is majorly associated with EDI Standards, however AS2 is an open standard for secure, payload-agnostic exchange of B2B documents.

How exactly it provides Security

AS2 uses the S/MIME(Secure/Multipurpose Internet Mail Extensions) protocol to wrap EDI/business data in a secure "envelope" . Files are encoded as "attachments" in a standardized S/MIME message, so the basic structure of an AS2 message consists of MIME format inside a HTTP message with additional AS2-specific header and send it over the internet, also it allows to makes use of digital certificates for following (not mandatory but recommended as it adds security). 
Digital Signing: For signing the data the sender uses the private key to ensure the sender's identity as the creator of the document, on receiver side the signature on the document is verified using the sender's public key to verify the sender's identity.

Encryption: To encrypt data sender uses the receiver's public encryption key, so only the proper recipient will be able to extract the document, the prepared document is decrypted using the receiver's private key.
Thus above two ensure the integrity of data transmitted.
AS2 also supports compression of the data using a compression algorithm to reduce the size of the transported data.

Another thing is MDN(Message Disposition Notification), which is sent to the sender and it denotes that message from sender is received, validated and decrypted successfully. MDN can be send synchronously(over the same HTTP connection message was received) or Asynchronously (later on given url or via email). 

AS2 Architecture

AS2 Architecture

Whenever two organization decide to go with AS2, they need to agree upon following things and thus an Agreement is saved at each partner and further communications happens based on it.
i. What will be the data format
ii. Which signing algorithm is to be used
iii. If encryption is required
iv. If compression is required
v. If MDN is required 

IF signing and encryption is chosen then each of the partner have to share the public key of digital certificate which will be used for signing and encryption.

The AS2 protocol relies on X.509 certificates for signing and encryption.A .pfx includes both the public and private key for the associated certificate which should never be shared outside your organization, it can be used for TLS/SSL on web site, for digitally signing messages or authorization tokens, or for authenticating to a partner system. A .cer file only has the public key - this is what you share with integration partners, also it can be used to verify tokens or client authentication requests.

AS2 and Logic App together

When we decide to use AS2 for B2B communication, then we need systems/services which understand AS2 and support. Earlier Microsoft had only BizTalk Server(on-premises) which was capable to support AS2 and now we have cloud service too which supports AS2 i.e. Logic Apps.

With Enterprise Integration Pack features required for B2B are made available to Logic Apps,the enterprise B2B capabilities like AS2 and EDI standards support and set of XML capabilities like XML Validation, XSLT Transformation and Flat file to XML encode/decode etc (this all are present in BizTalk server already) .

Both the partners need to procure certificate and share the public certificate with each other.

Steps to enable Logic App for AS2

1. Add  Private certificate in Key Vault

We need to store the Private Certificate in Key vault, and provide the access to the  services which would need the certificates via Access Policies of Key Vault, here it would be Logic Apps.

2. Integration Account

With Integration account you get a container wherein you can store schemas, maps, partners, agreements, certificates etc required for Integration project. Also you get access to following connectors 
Integration Account connectors

Note: Both your integration account and logic app must exist in the same location or region.

3. Add certificates in Integration Account

Click on certificates and add Partner1(sender) public certificate, give it a Name , from Certificate Type drop-down select Public and select a certificate by browsing to the certificate location.

Add public certificate in Integration account

and Partner2(receiver)’s private certificate, give it a Name , from Certificate Type drop-down select Private and select a Resource Group from drop-down which has the Key Vault and Key name in which private certificate is added in step1.

Add private certificate in Integration account
Note: Private certificate has to be stored in KeyVault and has to be referenced whereas Public certificate can be stored locally and uploaded.

4. Add Partner

Click on Partners and add Partner1 the sender and Partner2 the receiver(host), against Qualifier select AS2Identity and against Value Partner1 and Partner2 respectively.
Add partner1 in Integration accountAdd partner2 in Integration account
5. Add Agreement
After adding partners next is to add Agreement, give it a Name , from Agreement Type drop-down select AS2 , Host Partner is Partner2 , Guest Partner is Partner1 and select identity respectively.
                                 Add agreement between two partners in Integration Account

Agreement Receive setting - here we decide how the host partner would receive the AS2 message

i.Override Message Properties - If selected then the settings for validation and MDN which is defined below in Agreement is used else info from Message header is used.

ii.Message Should be signed - If selected then the sender partner has to sign the message with his private certificate and to validate it's public certificate has to be used at receiver s - Select the public certificate of the partner from the drop-down.

ii.Message Should be encrypted - If selected then the sender partner has to encrypt the message with receiver's public certificate and to decrypt receivers private certificate has to be used at receiver partner - Select the private certificate of the partner from the drop-down.

iii.Message Should be compressed - If selected all incoming messages must be compressed. Non-compressed messages are rejected.

iv.Disallow Message ID duplicates - It is to specify whether to allow messages with duplicate IDs. If you disallow duplicate IDs, select the number of days between checks. You can also choose whether to suspend duplicates.

v.MDN Text - Specifies the default message disposition notification (MDN) that you want sent to the message sender.

vi.Send MDN - Whether to send synchronous MDNs for received messages.

vii.Send signed MDN - Whether to send signed MDNs for received messages. If you require signing, from the MIC Algorithm list, select the algorithm to use for signing messages.

viii. Send asynchronous MDN - Whether to send MDNs asynchronously. If you select asynchronous MDNs, in the URL box, specify the URL for where to send the MDNs

Agreement Receive setting

Agreement  Send Setting - here we decide how the host partner would send the AS2 message

i. Enable message signing - Should all outgoing messages must be digitally signed. If you require signing, select these values:
- From the Signing Algorithm list, select the algorithm to use for signing messages.
- From the Certificate list, select an existing host partner private certificate for signing messages. 

ii.Enable message encryptionShould all outgoing messages must be encrypted. If you require encryption, select these values:
- From the Encryption Algorithm list, select the guest partner public certificate algorithm to use for encrypting messages.
- From the Certificate list, select an existing guest partner private certificate for encrypting outgoing messages.

iii. Enable message compressionShould all outgoing messages must be compressed.

iv.Unfold HTTP headers - Puts the HTTP content-type header onto a single line.

v.Transmit file name in MIME header - Whether to include the file name in the MIME header.

vi.Request MDN Should message disposition notifications (MDNs) to be received for all outgoing messages.

vii.Request signed MDNShould receive signed MDNs for all outgoing messages. If you require signing, from the MIC Algorithm list, select the algorithm to use for signing messages.

viii.Request asynchronous MDNShould receive MDNs asynchronously. If you select asynchronous MDNs, in the URL box, specify the URL for where to send the MDNs.

ix.Enable NRRShould require non-repudiation receipt (NRR). This communication attribute provides evidence that the data was received as addressed.

x.SHA2 Algorithm format - Specifies the MIC algorithm format to use for signing in the headers for the outgoing AS2 messages or MDN
Agreement  Send Setting

Create Logic App to receive AS2 Message and send MDN back

The first step is to Link Integration Account with Logic App, go to Settings --> Workflow Settings and select the Integration Account to 

linking integration Account

On designer Add Http request action to receive the AS2 message over the HTTP endpoint

AS2 receive logic app

Next is to Add Decode AS2 action here we first need to create a AS2 connection, an api connection to Integration Account (this is how the Encode/Decode AS2 connector gets access to the Partners/Agreement/Certificate which is required for them to perform expected operations i.e. signing/validation/decryption/encryption/compresssion)

Decode AS2 Message action

Then after provide input, i.e. the trigger body and header (header has http headers + AS2 headers which contains AS2 specific info )  to Decode AS2 Message

"body": "@triggerBody()",
"headers": "@triggerOutputs()['headers']",

Last step is to add Response Action to Send MDN (the same http connection will be used thus synchronous)  and input to it will be 
                     "body": "@base64ToString(body('Decode_AS2_message')?['OutgoingMdn']?['Content'])",
"headers": "@body('Decode_AS2_message')?['OutgoingMdn']?['OutboundHeaders']",
"statusCode": 200

AS2 messages are base64 encoded, thus to send MDN in string form @base64ToString function is used.

Note: OutgoingMdn is created by the AS2 Decoder, a task apart from validation and decryption. And AS2 decoder can use settings as per the agreement or it can use AS2 header of the incoming message .

Tuesday, August 27, 2019

The template validation failed: 'The action(s) 'xxx' referenced by 'inputs' in action 'xxx' are not defined in the template.'.

Currently working on a Logic App to accept AS2 message,decode it and do further processing and if any error encountered then notify the stakeholders by sending an email.

And for that I used email action and tried to pass on the error message as a body to the email, but I was not allowed to save the logic app for following reason

Action not defined in template


The template validation failed: 'The action(s) 'Decode AS2 message' referenced by 'inputs' in action 'Failure_Alert' are not defined in the template.'.

Why it happened

I am using following expression to set the body of alert email which will execute only if Decode AS2 message action fails

actions('Decode AS2 message').outputs.body.errormessage

It looks absolutely fine on designer but still wasn't able to save and the reason is how Logic App renders in background i.e. at code level. Upon switching to code view found that Decode AS2 message gets converted to Decode_AS2_ message

What to do

I just did the way logic app needs to provide the action name in the expression as below and all worked fine.


Sunday, June 16, 2019

Getting Started with Azure Data Factory - Insert Pipeline details in Custom Monitoring Table

Monitoring of any system/application's health and performance plays very important role in an Enterprise , as you get insight about what is happening with the applications and can prevent from major losses by enabling better/corrective decision.

Microsoft has provided inbuilt Monitoring feature within Azure Data Factory where you gets details about the each Pipeline which is run and also details about the activity inside the pipeline.

Pipeline run

As can be seen lot of details are made available at both the levels,RunID, Starttime, status, name, duration etc. 
activity run

 and at activity level you can even see input and output of that particular activity.

So the question might arise why need of Custom monitoring table?

1. Currently the pipeline run data retention is only for 45 days, then after data would be no more available.
2. Support for User defined properties -- all the details which we see is system properties but what about other than those, i.e. User defined properties which we want to monitor (non system). Yes, there is support to include User Properties in Monitor - you can promote any pipeline activity property as a user property( But only 5).
3. And not all properties are shown in Monitor tab.However columns can be added or removed but are limited and only available for pipeline run and not for activity run.


For demo I am using simple scenario - moving data from Azure SQL to Azure Data Lake Store. Continued from last post(Getting Started with Azure Data Factory - CopyData from CosmosDB to SQL) 
Azure SQL to Azure Data Lake Store

But here, upon failure details are logged into custom monitoring table

Steps in creating solution

1. Create Azure Data Lake Store  -- Destination

ADLS gen2

Small mistake here which led to failure in Pipeline run.

2. Create Azure SQL DB -- Source
SQL DB Table

Small mistake here which led to failure in Pipeline run.

3. Create Error Logging Table and Stored Procedure to Insert values in it

Based on what all details you need to capture, number of columns can be defined. For demo twelve entities are identified thus those are added as columns  in ErrorLogTable
ErrorLog Table and Stored Procedure

And a simple stored procedure to insert values in it

CREATE PROCEDURE [dbo].[usp_UpdateErrorLogTable]
@PipelineName VARCHAR(250),
@PipelineID VARCHAR(250),
@Source VARCHAR(300),
@ActivityName VARCHAR(250),
@ActivityID VARCHAR(250),
@ErrorCode VARCHAR(10),
@ErrorDescription VARCHAR(5000),
@FailureType VARCHAR(50),
@ActivityStartTime DATETIME,
@PipelineStartTime DATETIME,
@BatchID VARCHAR(100),
@ErrorLoggedTime DATETIME,
DECLARE @CheckError INT = 0;
INSERT INTO [ErrorLogTable]
[ActivityName ],
[ActivityID ],
[ErrorCode ],
[ErrorDescription ],
[FailureType ],
[ActivityStartTime ],
[PipelineStartTime ],
[BatchID ],
[ErrorLoggedTime ]
@PipelineName ,
@PipelineID ,
@Source ,
@ActivityName ,
@ActivityID ,
@ErrorCode ,
@ErrorDescription ,
@FailureType ,
@ActivityStartTime ,
@PipelineStartTime ,
@BatchID ,
SET @CheckError = ERROR_NUMBER()
IF @CheckError = 0 


4. Create Azure Data Factory and Pipeline

i. To create new instance of Data Factory, login to Azure portal Click on +Create a resource --> Data Factories --> click on +Add button 

Provide unique name to Data Factory instance to be created as per purpose , select the subscription, resource group, version and location(Note that currently only limited locations are available to choose from).

After creating an instance of Data Factory, you need to click on Author and Monitor - which will lead to ADF designer portal (dev env) which opens in separate tab. 

get started page

The new tab opens with options to get started with. Either click on Create Pipeline Wizard or the Pencil icon, both will lead to the canvas.

ii. If you already have Data Factory instance, you can skip above step. 
Instance of Data factory

Search and open existing ADF instance and add new pipeline in it.

iii. Create Linked Service and Dataset which will be used in pipeline.

SQL Linked service and dataset
SQL Linked Service

Go to Connection tab and click on New button. Give it a name, database details, username, password etc.
SQl Dataset

Here no Table is selected, as am going to use query(Note: Even if you choose table here, you can use query in the activity).

ADLS Linked service and dataset

ADLS Linked Service

Go to Connection tab and click on New button. Give it a name, ADLS url, account key.

ADLS Dataset

iv. Now drag and drop Copy activity from Activities-->Move & Transform section on canvas

copy activity
Give it a name, select Dataset (SQL Dataset created in above step) and the query to fetch data

SQL Sink

In sink tab select Dataset (ADLS dataset created in above step)

v. Now next step is to add StoredProcedure activity on canvas and connect Copy DataFromSQL activity to it but on failure condition.

Select Copy DataFromSQL activity and Click on plus sign and select Failure

Under general tab give name and under SQL Account select the Linked Service, here we are using same Linked service which was created to SQL DB.
Stored Procedure Parameter

Under Stored Procedure tab select the SP and click on Import Parameter button -- It will fetch list of all the parameters defined in SP. You can add parameters manually too by pressing +New  button.
SP Parameter

Here am capturing details of Activity and Pipeline from properties which are promoted by system at runtime (ActivityID, PipelineID, ErrorCode etc.) and few are static values (User defined properties e.g., Source and ActivityName).

How it works -- Whenever pipeline is triggered a RunID gets assigned to it and also to each activities within it , also other info like PipelineName, JobId, ActivityRunId, Status, StatusCode, Output, Error, ExecutionStartTime, ExecutionEndTime, ExecutionDetails, Duration and many more are captured. But all are not accessible, only few of them are, the ones which are promoted by system(available to access). And to access them we need to use accessors(properties).
example -
a. to get Pipeline ID use RunID property of Pipeline class -- pipeline().RunId
b. to get when particular activity started use ExecutionStartTime property of activity class -- activity('name of activity').ExecutionStartTime
c. to get the error details of particular activity  use error.message property of activity class -- activity('name of activity').error.message (it is available only if that particular activity has failed).

That's it, pipeline is ready, validate it , publish it and test.


The copy activity has to fail so that entry is made in ErrorLogTable, for that provided Incorrect table name in Query

Select * from EmployeeArchive1 where Jobtitle='Developer'

Invalid Object Name error

 After the pipeline run completed, entries were found in the ErrorLogTable
entry in error log table

Related Post