Category: Uncategorized

The response is not in a JSON format

Have you been using the HTTP with Azure AD connector lately? It´s really a game-changer for me. No more custom connector is needed, unless you want to. I wrote a whole how-to post about it.

The problem

I was using the connector to access an on-prem web service, "in the blind". I had some information about the message that should be sent but was not sure. I was trying out different messages when I got this strange error back:

{
    "code": 400,
    "source": <your logic app's home>,
    "clientRequest": <GUID>,
    "message": "The response is not in a JSON format",
    "innerError": "Bad request"
}

Honestly, I misinterpreted this message and therein lies the problem.
I was furious! Why did the connector interpret the response as JSON? I knew it was XML, I even sent the Accept:text/xml header. Why did the connector suppress the error-information I needed?

The search

After trying some variants on the request body all of a sudden I got this error message:

{
    "code": 500,
    "message": "{\r\n  \"error\": {\r\n    \"code\": 500,\r\n    \"source\": \<your logic app's home>\",\r\n    \"clientRequestId\": \"<GUID>\",\r\n    \"message\": \"The response is not in a JSON format.\",\r\n    \"innerError\": \"<?xml version=\\\"1.0\\\" encoding=\\\"utf-8\\\"?><soap:Envelope xmlns:soap=\\\"http://schemas.xmlsoap.org/soap/envelope/\\\" xmlns:xsi=\\\"http://www.w3.org/2001/XMLSchema-instance\\\" xmlns:xsd=\\\"http://www.w3.org/2001/XMLSchema\\\"><soap:Body><soap:Fault><faultcode>soap:Server</faultcode><faultstring>Server was unable to process request. ---> Data at the root level is invalid. Line 1, position 1.</faultstring><detail /></soap:Fault></soap:Body></soap:Envelope>\"\r\n  }\r\n}"
}

And now I was even more furious! The connector was inconsistent! The worst kind of error!!!

The support

I my world, I had found a bug and needed to know what was happening. There was really only one solution: Microsoft support.
Together we found the solution but I still would like to point out that error message that got me off track.

The solution

First off! The connector did not have a bug, nor is it inconsistent; it is just trying to parse and empty response as a JSON body.
Take a look back at the error messages. They are not only different in message text, but in error code. The first one was a 400 and the other a 500. The connector always tries to parse the response message as JSON.

Error 500: In the second case (500) it found the response body, and supplied the XML as an inner exception. Not the greatest solution, but it works.
Error 400 In the first error message the service responded back with a Bad request and an empty body. This pattern was normal back when we built Web Services. Nowadays, you expect a message back, saying what is wrong. In this case, the connector just assumed that the body was JSON, failed to parse it and presented it as such.

If we take a look at the message again perhaps it should read:

{
    "code": 400,
    "source": <your logic app's home>,
    "clientRequest": <GUID>,
    "message": "The response-message was emtpy",
    "innerError": "Bad request"
}

Or The body length was 0 bytes Or The body contained no data

Wrapping up

Do not get caught staring at error messages. You can easily follow down the trap of assumptions. Verify your theory, try to gather more data, update your On-Premise Data Gateway to the latest version, and if you can: try the call from within the network, just like old times.

What is this?

I have found a new connector which is much better suited for calling OnPrem services from Logic Apps.

The use of a custom connector

A custom connector is meant to be a bridge between your Logic App and an on premise service, handling JSON or XMLSOAP. It can also be used as a way of minimizing the clutter in a confusing API and exposing only the necessary settings to your Logic App developer. You do not even have to access on premise services using the connector.

I have have some experience in setting up OnPrem integrations and have posted about Planning installation of the On Premise Gateway and finally solving how to deploy the Custom Connector using ARM.

A better connector

My use of the Custom Connector has always been to call OnPrem services that are either SOAP or JSON based. For that I have used a custom connector but there is a great alternative, if you know how to create XML Envelopes or JSON bodies. The only thing you will loose using this connector is the nice interface in the Logic App.

Let me point you to the HTTP with Azure AD connector, and yes you can use it with Azure AD but this post is about replacing your Custom Connectors.

The scenario

I have an OnPrem service that uses old school SOAP and XML to provide information about Customers. The service uses Windows Authentication and will respond with the given customer as an XML response.

If you don´t know SOAP: it is basically a HTTP POST with a header named SOAPAction and an XML body. I will show you a JSON call at the end as well.

Using the connector

Prerequisite: In order to access OnPrem services, you must install the OnPrem data gateway.

Add the connector to a Logic App

Add a new Action the same way as always, Search for HTTP with Azure AD. I know, it says Azure AD. It is strange but trust me.

Now look at the available Actions.

The first one will only use the GET HTTP verb. Choose the other one.
You will now get this scary image.

I know it says Azure AD but here comes the payoff. Click the Connect via On-premises data gateway checkbox.

TADAAAAA! You can now start filling in the settings for your service call, directly to the OnPrem Service without using a Custom Connector.

Configure the connector

Authentication Type

Start by choosing the Authentication Type. Note that the Username and Password fields are still visible, even if you choose Anonymous.
I will use Windows Authentication, which, by the way, is not supported in the Custom Connector.

Base Resource URL

This is the start path to your service URL as if it was called from within the OnPrem network.
A full service URL might look like this: http://webapiprod/EmployeeService/GetEmployee.asmx, then the base would be http://webapiprod/EmployeeService or http://webapiprod depending on how you want to slice it. The full service URL will be defined later. Also notice that you do not end the base url with a /.

I will call a service with the full path http://erpsystem/WebServices/GetCustomers.asmx so I opt for http://erpsystem/WebServices

Windows Authentication

This is very simple: enter the username (including domain) and password to the OnPrem user you need to use for authentication. I have entered the username and domain like this companydomain\erpwebusr

Gateway

Choose the appropriate subscription and gateway. I have to censor mine for obvious reasons.

Done

The final product looks like this:

Just click Create to start using it.

Start using the connector

I test a lot of APIs in its raw format, using Postman or HTTP RestClient with VS Code or even SOAPUI back in the day. I know how to format a message. I think you do too. I will now configure the connector to execute a call to the SOAP service to get Customer data.

Choose a Method

You have to choose the HTTP verb you need. For SOAP you always use a POST, but your service might need to use a GET.

Url of the request

Here you can either enter the full url or the last part depending on how you feel about either.
My full path was http://erpsystem/WebServices/GetCustomers.asmx and I opted for http://erpsystem/WebServices as the base path. Therefore I can either enter the full path, or be fancy and just enter /GetCustomers.asmx. I am fancy.

Headers

You need to add headers to your call. In the case of SOAP, you need SOAPAction, Content-type and lastly an Accept header.
The last one seems to tell the connector what data to expect back. If you do not set this to text/xml, the connector will expect a JSON message and give you a 400 Bad Request back.

If you call a Rest-service you will only need a content type if you supply a BODY.

You might also need to send additional headers, like API-keys and such. Simply add the ones you need.

Add headers by clicking the Add new parameter dropdown.

Select Headers and fill in the headers you need, depending on your needs. For SOAP these are located in the WSDL-file.

I need to set SOAPAction: GetCustomer, Content-type: text/xml and Accept: text/xml

Body of the request

If you are calling a SOAP service or sending data to a rest service, you need to set the body. This Connector even supports XML in the designer. So simply supply the XML you need to execute your call. I must send this:

<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:get="http://erpsystem/webservices/GetCustomers" xmlns:cus="http://myns.se/customer" xmlns:man="http://www.erpsystem.com/Manager">
   <soap:Header/>
   <soap:Body>
      <get:Execute>
         <get:request>
            <get:_requests>
               <get:GetCustomers>
                  <cus:customer_Id>XXXX</cus:customer_Id>
               </get:GetCustomers>
            </get:_requests>
         </get:request>
      </get:Execute>
   </soap:Body>
</soap:Envelope

I know. SOAP was not aimed at being lightweight.
The XXXX part will be replaced by the customer ID sent to the Logic App.

The end product looks like this:

The Accept header is missing in the picture. It is from an earlier version of this post.

Calling the backend service

A call to the service might look like this (I blocked out some sensitive things):

Looking at the call

Please note that the Body of the request is always sent as a Base64 encoded. This means that you need to have access to a decoder to read what you actually sent. It also means that the service you are calling must accept Base64 encoded payloads. Binary payloads are not supported.

Looking at a JSON Call

Here is an Action configured for JSON and the resulting execution. The use of POST and sending the customer ID in the message body is due to the design of the service. I would also add a content-type to the call, just to be sure.

Conclusion

As long as you do not have the need to send data in binary to a backend service, you should really start using this connector and not bother with the custom connector.

What is this?

This is a re-release of an old article about BizTalk. I wrote the original in 2014 and removed it in a blog migration. However, a colleague asked about it so I am re-writing it.

What are Orphans?

When tracking is performed in BizTalk, it takes several different steps. The first thing is to create a row in the dta_ServiceInstances table in the DTA database. There is a bug in BizTalk that prevents the line from being removed by the DTA Purge and archive cleaning job. This creates an Orphan and if there are many, the table is filled with unnecessary junk.

Why?

When tracking starts the field dtStartTIme is set to the current UTC time. In the same row the field dtEndTime will be set once the flow is completed. Sometimes it does not. This seems to be related to a high number of exceptions or the use of request response with a SendPort group (do not do that!).

The row should be removed by the clean up job, but that job only removes completed rows, i.e. rows with a dtEndTime that is not null.

How many Orphans?

I think about 1000 is too many. To find out the number of orphans in your BizTalk installation run the script below. It should be run in BizTalkDTADb.

select
    count(*) as 'NoOfOrphans'
from 
    [BizTalkDTAdb].[dbo].[dta_ServiceInstances]
where 
    dtEndTime is NULL and [uidServiceInstanceId] NOT IN 
    (
    SELECT 
        [uidInstanceID]
    FROM 
        [BizTalkMsgBoxDb].[dbo].[Instances] WITH (NOLOCK)
    UNION
    SELECT 
        [StreamID]
    FROM 
        [BizTalkMsgBoxDb].[dbo].[TrackingData] with (NOLOCK)
    )

The query clearly shows that you need to not only look in the DTA-database, but also count the number of instances still in the MessageBox, meaning active messages. If you want more information on the instances just do a SELECT * instead of SELECT count(*). You can clearly see that the data has a dtStartTime but not a dtEndTime.

How to remove them

The BizTalk Terminator tool

I do not know if this exists or is supported anymore. Be advised! The below text is form 2014.

This is the supported way of removing orphans. It is, however, important to point out that the entire BizTalk environment needs to be stopped for it to work, and if that is not a useful alternative, you can run the actual script that the Terminator tool runs.

The T-SQL Script from the terminator tool

This simple script updates the orphaned rows and sets dtEndTime to the current UTC time, making it possible for the cleanup job to remove the row.

BEGIN TRAN
USE [biztalkDTADb]

UPDATE
    [dbo].[dta_ServiceInstances]
SET 
    [dtEndTime] = GetUTCDate()
WHERE
    dtEndTime is NULL 
    AND 
    [uidServiceInstanceId] NOT IN
    (
    SELECT 
        [uidInstanceID]
    FROM
        BizTalkMsgBoxDb.[dbo].[Instances] WITH (NOLOCK)
    UNION 
    SELECT 
        [StreamID]
    FROM
        BizTalkMsgBoxDb.[dbo].[TrackingData] WITH (NOLOCK)
    )
-- If it works, uncomment the following row and run it. It commits the query to the database
-- Commit tran
-- If it DOES NOT work: uncomment the following row and run it. It undoes the query.
-- Rollback tran

Note that the script has the same query for finding orphans as the above that only counts the number of orphans.
In order to perform this update by the book, flow these steps:

1. Run the first query to find the number of orphans.
2. Run the second script and note the number of rows that was updated.
3. If the number of rows matches you can uncomment and run the Commit Tran row. Done.
4. If the number of rows does not match, something is wrong and you must run the Rollback Tran row. Not done.

NOTE! You must run both the query and the Rollback or Commit in the same query window.

If you go to number 4, check the scripts and make sure you are running the scripts on the same database. If you have a constantly increasing number of orphans in your DB, that might also be the reason.

How are the orphans removed

The rows can now be deleted by the DTA Purge and archive job, in accordance with the job settings. This might take a while as your settings might let older tracking data be in the database for several days. Also, the job runs once every minute so you just might need to wait for 60 seconds.

What is this?

The documentation on how to deploy Logic Apps Connectors in a proper CI/CD scenario is … lacking.
Being able to use Connectors and to deploy them automatically, is a must in today´s enterprise landscape. I have worked with Connectors for two years and have just recently been able to have everything fully automated.

Also: A huge shout-out to Maxim Zhizhin for solving the last part of the problem and sharing it with me.

Who is this for?

This is not a full walkthru on how to use Connectors, and it mostly focuses on how to use a Connector to connect to an on-premise service, i.e. using the On Premise Data Gateway.
If you are looking at how to incorporate a Custom Connector into your CI/CD setup using ARM, this is for you.

The TLDR

If you know what you are doing and is just looking for the ARM-template file, you can get it here.

The Steps

These are the steps:

1. Finnish configuring you connector.
2. Export the Starting ARM Template
3. Examining the ARM
4. Adding the Swagger/OpenAPI Definition to the ARM template
5. Adding additional settings
6. The Parameter File
7. Trying to deploy

Finnish configuring your connector

You have created a Logic App Custom Connector and know that it is working properly. In my case I built a SOAP Passthru (the best way to connect to SOAP services). The SOAP service is located on-premise. It looks like this:

The service sends a POST with a header for the SOAPAction (which a SOAP service needs) and another header for Content-type, signaling to the SOAP service that we are using XML and also in what encoding,

Export the Starting ARM Template

I put emphasis on starting because this ARM Template that will be exported is in NO WAY complete.

• Find the Export Template in the starting page of the Connector (it is under automation).
  
• Click it and after the template has been generated, find Download at the top of the screen.
  
• Download and open the file using your preferred tool. I am using VS Code.

Examining the ARM

Looking from the top to the bottom, you first have a parameter for the connector name. Then you have basic information about the region. Further down you have information about authentication (basic or none), and funny enough some information about how to fill in the UI for the auth part.
Then we come to the use of the gateway which is a repeat of the auth settings. Is should look like this:

"gateway": {
  "type": "gatewaySetting",
  "gatewaySettings": {
    "dataSourceType": "CustomConnector",
    "connectionDetails": []
  },
  "uiDefinition": {
    "constraints": {
      "tabIndex": 4,
      "required": "true",
      "capability": [
        "gateway"
      ]
    }
  }
}

This only defines the UI for use of the gateway, it does not actually implement it.

Scroll further and you have some basic settings, like the name and description. If you have used an Icon it will be exported as a base64 encoded.

Adding the Swagger/Open API definition

The ARM Template you have saved is not complete. You have to manually add the Open API definition to it.
To find the information you go back to the portal, and the start page (Overview) of the Connector. Find the Download link at the top.

Click to download the file. Note! The file has no file extension and therefore you have to choose a text tool to open it.

Copy the text from the file into a new property called swagger, and add the copied text. The end result should look like this:

"swagger": {
        "swagger": "2.0",
        "info": {
            "title": "SOAP pass-through",
            "description": "Get Info SOAP passthru",
            "version": "1.0"
        },
        ...
    }
}

Adding Additional Settings

There are four additional settings you need to manually enter in order for this to work properly.
All these settings should be on the same level as "iconUri", so just add these settings before that property.

apiType

Set this property to

"apiType": "Soap"

backendService

This property is the path to the on-premise service as if you published your connector on the same network. In my case this is

"backendService":{ "serviceUrl": "http://onpremservice/webservices"}

wsdlDefinition

This is mostly used if you are not using the SOAP passthru method. If you do you should set it to:

"wsdlDefinition": {
   "importMethod": "SoapPassThrough"
}

capabilities

Setting this, tells the deployment to use the on premise data gateway, not only have the UI show it (see above). To make this happen add this property

"capabilities": ["gateway"]

The parameter file

This file was downloaded with you initial export of the ARM-template and is located in the same Zip-file. You do not need a parameter file to make this work, it is just good practice. When adding a parameter file you can also have different parameter files for different environments, such as TEST or PROD.
The generated file only contains a single parameter but you should parameterize additional settings in the file to fully integrate the ARM-files in your CI/CD pipeline. Here are the settings I usually parameterize:

• Location make sure that the Location is the same as the resource group and set that from the CI/CD pipeline.
• BackendService ServiceUrl This might be different between TEST and prod for instance.
• Swagger Within the Swagger there might be multiple things that differ between environments. I usually make sure that Host is set by a parameter.

Trying to Deploy

If you do not want (or can´t) go thru the whole DevOps deploy pipeline to test this new ARM template, you can use a built in functionality in the Azure Portal.

In the search box at the top of the portal, search for deploy and select "Deploy a custom template".

This lands you on a page where you can enter your ARM-template and parameter files and deploy them.

1. Select "Build your own template in the editor".
2. Paste the ARM template JSON in the window.
   
3. Click Save at the bottom left part of the page.
4. If you are using a parameter file, click the "Edit parameters" link at the top right of the page.
5. Paste the content of your parameter file and click Save at the bottom left.
6. Done. Click Review+Create at the bottom left.

If my template can help you, I have uploaded it here.

In conclusion

The Logic App Custom Connector is an underestimated, underdone and under-documented feature in Azure, but if you know how to configure it and where to get the info, it is very useful.

Any questions or feedback regarding this post can be sent to my Twitter.
Hope this helps.

What is this?

Some time ago, I got this message when executing a Custom Connector from a Logic App. The response was very strange, and was returned in that object way that an HTTP action sometimes does:

{
'content-type': 'application/octet-stream',
'content': 'eyJjb2RlIjogIkFwaURpc2FibGVkIiwgIm1lc3NhZ2UiOiAiQVBJIGhhcyBiZWVuIGRpc2FibGVkIGR1ZSB0byBpbmFjdGl2aXR5LiBQbGVhc2UgdXBkYXRlIHRoZSBDdXN0b20gQ29ubmVjdG9yIHRvIGVuYWJsZSBpdCBhZ2Fpbi4ifQ=='
}

Running the $content thing thru a Base64 decoder I got back:

{'code': 'ApiDisabled', 'message': 'API has been disabled due to inactivity. Please update the Custom Connector to enable it again.'}

The connector had been … disabled?!!!
Does this seem familiar?

What to do (short term)

The very short version is to update the connector. To basically force Azure to deploy it again. To solve this I simply added a space in the description-field and clicked Update Connector. Done.

Why did this happen?

I raised a ticket with MS Support and got a very clear answer: They used to disable connectors that had not been used for 30 days. Why? They did not say, only that they no longer have that policy. I think it was due to the datacenter being close to overloaded (thanks Covid-19) and they wanted to remove unused resources.

What to do (long term)

You need to look at your connectors and find all those that have not been used for a while. The Enfo support team checked the run history of all Logic Apps that uses Custom Connectors. If a Logic App had not been run for a long time, they updated the connector behind it. I think you should do this as well.
In our case it was easy, two Logic Apps had not been run for two months and all others are running on a daily basis.

Get to updating your connectors.