Azure API Management – How to set basic authentication in SendRequest

Scenario: I have both username and password stored in name value configuration of API management but I need to use it in SendRequest policy.

Source Code:

<set-variable name=“userName” value=“{{username}}” />

<set-variable name=“password” value=“{{password}}” />

<set-variable name=“basicAuthDetails” value=”@{

var username = context.Variables.GetValueOrDefault<string>(“userName”);

var password = context.Variables.GetValueOrDefault<string>(“password”);

return System.Convert.ToBase64String(System.Text.Encoding.GetEncoding(“ISO-8859-1”).GetBytes(username + “:” + password));


<set-variable name=“jsonPayload” value=”@{

JObject transBody = new JObject();

//Add all json Property

transBody.Add(“test”, JToken.FromObject(new[]


 “test data”



<send-request mode=“new” response-variable-name=“var” ignore-error=“false”>


        var url = context.Api.ServiceUrl+ “/{someURL}”;

       return url;



<set-header name=“Authorization” exists-action=“override”>



<set-header name=“Content-Type” exists-action=“override”>



<set-body template=“none”>@(context.Variables.GetValueOrDefault<string>(“jsonPayload”))</set-body>



That is how you can set basic authentication in SendRequest in Azure API Management.


Azure API Management Policy – Asynchronous API as Synchronous API

Below is the link to reference/examples of using Azure API management policy

There’s an example there on how to Mask Asynchronous calls as Synchronous API however for my requirement it’s not enough.

The target API behaves as follow:

  1. All operations are POST and asynchronous.
  2. To get the results of any operation a second API needs to be called and it’s expecting the transaction id.
  3. API expects a payload in command manner (args parameter) instead of proper JSON name/value pair.

Requirement: API should return the results in synchronous manner.


API Callout Policy in API Management.


  1. Create a GET operation and via policy rewrite the operation to POST
  2. Create a JSON transformation logic in the inbound policy and execute the backend API.
  3. Create a JSON transformation logic in outbound policy and add a retry policy to execute the second API  that returns the result passing the transaction id from #2
  4. Return the results from second API.

Policy Code:

<base />
<set-header name="Content-Type" exists-action="override">
<!-- Save authorization data to variable, to be used in outbound status request -->
<set-variable name="authorization" value="@(context.Request.Headers.GetValueOrDefault("Authorization"))" />
Construct the payload
var paramFromRequest = Uri.UnescapeDataString(context.Request.OriginalUrl.Query.GetValueOrDefault("paramFromRequest"));
JObject transBody = new JObject();
     new JObject
         {"someproperty", "fixvalue"},
         {"someproperty2", "fixvalue2"},

//Add all json properties as arg
transBody.Add("args", JToken.FromObject(new[] 
return transBody.ToString();
<trace source="debug">@(context.Request.Body.As<string>(true))</trace>
<!--Remove additional query string -->
<set-query-parameter name="paramFromRequest" exists-action="delete" />
<rewrite-uri template="{{FIRSTBACKENDAPI_URL}}" />
<base />
<base />
<!-- Get the Key from response -->
<set-variable name="jsonPayload" value="@{ 
JObject inBody = context.Response.Body.As<JObject>();
var transactionId= inBody["transactionId"].ToString(); 
string jsonString = "{\"transactionId\":\"_transactionId\" }";
var json = jsonString.Replace("_transactionId", transactionId);
return json;
}" />
<retry condition="@( Convert.ToBoolean(context.Variables.GetValueOrDefault<JObject>("status")["{{PROPERTYNAME IN JSON OBJECT RESPONSE}}"]))" count="10" interval="1">
<!-- Call Status API to get the results-->
<send-request mode="new" response-variable-name="var" ignore-error="false">
var url = context.Api.ServiceUrl+ "{{SECONDBACKENDAPI_URL}}";
return url;
<set-header name="Authorization" exists-action="override">
<set-header name="Content-Type" exists-action="override">
<set-body template="none">@(context.Variables.GetValueOrDefault<string>("jsonPayload"))</set-body>
<set-variable name="results" value="@(((IResponse)context.Variables["var"]).Body.As<JObject>())" />
<set-body template="none">@((context.Variables.GetValueOrDefault<JObject>("results")["{{PROPERTYNAME IN JSON OBJECT THAT CONTAINS THE RESULT}}"].ToString()))</set-body>
<base />


Querying Azure SQL Database using Azure Functions 2.0 to return JSON data

The guide below shows how you can easily query Azure SQL Database using Azure Functions.

I have to admit, I have to do multiple google search and combine it for a working solution.


  1. How to get SQL connectionString from Azure Function settings.
  2. How to convert the sql results to JSON.


    • 1. Create a new Function with HTTP trigger
    • 2. Add a new file called serialize.csx with following contents below. This will convert the SQL rows to a JSON like data.
using System.Text;
using System.Data;
using System.Linq;
using System.Configuration;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Collections;
public static IEnumerable<Dictionary<string, object>> Serialize(SqlDataReader reader)
var results = new List<Dictionary<string, object>>();
var cols = new List<string>();
for (var i = 0; i < reader.FieldCount; i++)
var colName = reader.GetName(i);
var camelCaseName = Char.ToLowerInvariant(colName[0]) + colName.Substring(1);

while (reader.Read())
results.Add(SerializeRow(cols, reader));

return results;
private static Dictionary<string, object> SerializeRow(IEnumerable<string> cols,
SqlDataReader reader) {
var result = new Dictionary<string, object>();
foreach (var col in cols)
result.Add(col, reader[col]);
return result;


3. In the run.csx, paste the following code. This will query the Azure SQL database and returns the data.

#r "Newtonsoft.Json"
#load "serialize.csx"

using System.Net;

using Microsoft.AspNetCore.Mvc;

using Microsoft.Extensions.Primitives;

using Newtonsoft.Json;

using System.Text;

using System.Data;

using System.Linq;

using System.Configuration;

using System.Data.SqlClient;

using System.Collections.Generic;

public static async Task<HttpResponseMessage> Run(HttpRequest req, ILogger log)


log.LogInformation("C# HTTP trigger function processed a request.");

string name = req.Query["name"];

string json =" ";



var str = Environment.GetEnvironmentVariable("<ConnectionStringName in appSettings>");

using(SqlConnection conn =new SqlConnection(str))


using(SqlCommand cmd =new SqlCommand())


SqlDataReader dataReader;

cmd.CommandText = "<SQL QUERY HERE>";

cmd.CommandType = CommandType.Text;

cmd.Connection = conn;


dataReader = cmd.ExecuteReader();

var r = Serialize(dataReader);

json = JsonConvert.SerializeObject(r, Formatting.Indented);




catch(SqlException sqlex)




returnnew HttpResponseMessage(HttpStatusCode.BadRequest)


Content = new StringContent(JsonConvert.SerializeObject($"The following SqlException happened: {sqlex.Message}"), Encoding.UTF8, "application/json")



catch(Exception ex)




returnnew HttpResponseMessage(HttpStatusCode.BadRequest)


Content = new StringContent(JsonConvert.SerializeObject($"The following SqlException happened: {ex.Message}"), Encoding.UTF8, "application/json")



returnnew HttpResponseMessage(HttpStatusCode.OK)


Content = new StringContent(json, Encoding.UTF8, "application/json")


This shows how you can easily query the Azure SQL Database and return the data JSON in few minutes.
Perhaps next steps is to deploy this Azure Function into API management.


BizTalk – BTDF Compilation Error – ICE01

Current setup:

  • BizTalk Server 2016
  • Azure DevOps On-premise Build Agent

Error Message:

Error executing ICE action ‘ICE01’. The most common cause of this kind of ICE failure is an incorrectly registered scripting engine. See for details and how to solve this problem. The following string format was not expected by the external UI message logger: “The Windows Installer Service could not be accessed. This can occur if the Windows Installer is not correctly installed. Contact your support personnel for assistance


Change the Log On As of VSTS Agent to Local System Account

  1. Run services.msc
  2. Look for the VSTS Agent Service
  3. On Tab change the Log On from Network Service to Local System account


SAP – How to generate an XSD/IDoc Schema

Below are the steps on how to generate an XSD Schema / IDoc Schema from SAP.

1. In the SAP System in which the IDoc is defined, call transaction WE60 (IDoc Documentation).

2. Enter the name of the IDoc in the Basic type field (or the Enhancement field if your IDoc is an Enhancement of a standard SAP IDoc).

3. Choose menu Documentation, XML Schema.

4. Respond Yes, if prompted to ‘Generate Documentation for Unicode File’.

5. On the resulting screen containing the XSD of the IDoc, choose menu XML, Download.

BizTalk – SFTP Adapter – Missing WinSCPNet Library


This guide is applicable to BizTalk Server 2016 with Feature Pack 3 Installed.

Version of WinSCPNet library is different once CU5/FP3 have been installed.

Original BizTalk Server 2016 installation requires 5.7.7.

More info here:

BizTalk Server 2016 Prerequisites:

CU5/FP3 Info:


The Messaging Engine failed to add a receive location “FileSync.SFTP” with URL “s*.*” to the adapter “SFTP”. Reason: “System.IO.FileLoadException: Could not load file or assembly ‘WinSCPnet, Version=, Culture=neutral, PublicKeyToken=2271ec4a3c56d0bf’ or one of its dependencies. General Exception (Exception from HRESULT: 0x80131500)

How to solve:

  1. Copy the both WinSCP exe and dll (5.13.1) to BizTalk Installation Path (Do not GAC the dll)
    1. WinSCP.exe
    2. WinSCPnet.dll

How to get the exe and dll

  1. Use Nuget in Visual Studio to get the specific version of WinSCP.

Nuget-WinSCP    2.  Once added, just browse to the lib for the dll and tools folder for the .exe





All receive location green and by the moment you’ve send your first IDoc this warning occurs:

The adapter “WCF-Custom” raised an error message. Details “Microsoft.Adapters.SAP.RFCException: Details: ErrorCode=RFC_INVALID_HANDLE. AdapterErrorMessage=An exception has occurred on the listener while executing RfcWaitForRequest..”.

To solve this error you’ll need to install the BizTalk SAP Adapter Schema.


  1. Create a BizTalk application or use the default BizTalk Application
  2. Right Click Add Resource.
  3. Browse to: <install path of Microsoft BizTalk Adapter Pack(x64)>\bin\Microsoft.Adapters.SAP.BiztalkPropertySchema.dll
  4. In the options check all.