Tallan's Technology Blog

Tallan's Top Technologists Share Their Thoughts on Today's Technology Challenges

MABS EAI Bridge LoB Lookup (Part 1 of 2)

Dan Field

Microsoft Azure BizTalk Services (MABS) has a lot to offer for companies looking for a PaaS Middleware solution.  EAI bridges provide B2B communication as well as LoB access functionality for EDI, XML, and flat file interchanges.  The new mapping system offers some exciting and powerful new functionality as well, vastly simplifying certain tasks that previously required several functiods, and opening up new possibilities and enhanced performance with lists.

However, it is a new technology, and certain tasks that have been very straightforward in BizTalk Server 2013 require a different way of thinking for MABS.  For example, it is a fairly trivial task to create an orchestration that accesses a LoB adapter (using, for example, WCF slqBinding) to do data validation or enhancement, and publishing this orchestration as a web service for client consumption. If your SQL database is SQL Azure, there is some built in functionality to do a “Lookup” in the Azure database, but this may not be an option for an infrastructure that is makes use of features not currently available in SQL Azure, such as the ability to encrypt at rest.  It may also just be possible that an existing LoB SQL database cannot easily be moved for various other reasons.  In this series of posts, I will outline the process for implementing this functionality using the powerful custom code functionality available in MABS.

The tasks include the following (this post will cover steps 1-6):

  1. Creating the BizTalk services
  2. Setting up BizTalk Adapter Services in a local (or IaaS) environment to run a stored procedure in SQL Server
  3. Creating a sample table and stored procedure
  4. Creating a ServiceBus namespace with ACS
  5. Create the Relay to the LOB system
  6. Creating an EAI bridge to access the LoB system
  7. Testing and debugging the bridge with a Visual Studio add on
  8. Writing a custom component to call the LoB adapter in a EAI Bridge Stage and parse the response
  9. Having the component send an email notification using an Office 365 server

Step 1: Create BizTalk Service This is fairly straightforward.  Log into your Azure portal, click BizTalk Service, click New, and then Custom Create.  Choose a name, edition, and region, and your service will begin provisioning:


You’ll need the ACS Information for your newly created service for several of the following steps.  You can get this information by selecting the service you created and clicking “Connection Information” at the bottom.  That will display the following information.  You’ll need similar information for the ServiceBus relay (created in step 4) as well; I found it convenient to copy this information into an Excel sheet for quick reference during development (however, be sure this is stored in a secure manner and is consistent with your IT security policies – this is sensitive information that should not be disclosed to non-administrators of the service):


Step 2: Setting up BizTalk Adapter Services For this, you’ll need to get the BizTalk Services SDK: http://www.microsoft.com/en-us/download/details.aspx?id=39087.  The instructions for this can be found at: http://msdn.microsoft.com/en-us/library/azure/hh689760.aspx#BKMK_Installation.  Take note of the requirement regarding machine.config if you have previously installed Azure BizTalk SDK components, and install the optional runtime (this had me going in circles for a while!).  Also take note of the requirements for downloading and installing the certificate from the Azure:


To add it to your security store (see http://msdn.microsoft.com/en-us/library/azure/hh949825.aspx):

  1. On your test/development machine, double-click the .cer file. Select Install Certificate.
  2. Select Place all certificates in the following store, select Browse, and select Trusted Root Certification Authorities.
  3. Complete the installation. You’ll get a Security Warning; which is normal. Click Yes.

When prompted for the service identity, be sure to use an account that has internet access and permissions SQL (or whatever LoB item you’re working with).   A successful install should result in the following page when you navigate to https://localhost:8080/BAService/ManagementService.svc/


Step 3: Creating a sample stored procedure For this sample, I’m working with a SQL Server database running in a VM behind Tallan’s firewall.  I did not have to do any special firewall configuration for the VM, my host machine, or IT to get this up and running, nor did I have to make use of any kind of tricky proxying methods.  In my database (named BTSTrainDb, a database I have for testing and samples), I created a table: dbo.sn.  This table has an ID column, a varchar SerialNumber column, and a bit IsValid column.  I also created a simple stored procedure that takes a varchar as a parameter and uses it to do a look up on the dbo.sn table:

USE [BTSTrainDb]

CREATE TABLE [dbo].[sn](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [SerialNumber] [varchar](50) NOT NULL,
 [IsValid] [bit] NOT NULL



INSERT INTO [dbo].[sn] (SerialNumber, IsValid) VALUES ('asdf', 1), ('fdsa', 0), ('test', 1);'


CREATE PROCEDURE [dbo].[usp_ValidateSN]
 @sn varchar(50)
SELECT IsValid from dbo.sn WHERE SerialNumber = @sn;

Obviously, this procedure could contain any amount of business logic and/or perform other CRUD operations if desired.  I also have a SQL user that has permissions on the database, table, and stored procedure. Step 4: Create a ServiceBus relay with an ACS namespace Microsoft has recently removed the ability to do this from the management portal, and so it has to be done through a PowerShell cmdlet.  On top of that, the current documentation for this command is out of date!  Cesar Cordero (a colleague here at Tallan) just recently wrote a blog about this with some more details here: http://blog.tallan.com/2014/11/06/service-bus-authentication-and-authorization/ Here’s an overview of what you need to do:

  1. Get the Azure PowerShell module(s) if you don’t already have them: http://azure.microsoft.com/en-us/documentation/articles/install-configure-powershell/
  2. Add-AzureAccount; follow the steps in the dialog that pops up
  3. Select-AzureSubscription (if your account is associated with more than one subscription; this information can be found by clicking on the “Subscriptons” button near your username at the top of the Azure Portal)
  4. New-AzureSBNamespace -Name <name> -Location “Eastern US”; at the prompt for NamespaceType, type Messaging.  Note: The name of this namespace must be different from the name of the BizTalk service.  If it is not, there will be a clash between the ACS namespace used for the BizTalk Services ACS and the ServiceBus ACS. 
  5. You can now retrieve the connection information for this ServiceBus namespace from the management portal (or copy it from the output of the PowerShell cmdlet).  I made a note of it as it’s required for configuration in step 5.

Step 5: Create the LoB relay Open Visual Studio.  In the Server Explorer, right click on BizTalk Adapter Services, and select Add a BizTalk Adapter Service.  Enter the URL set up in step 2 (https://localhost:8080/BAService/ManagementService.svc/). Expand the newly created adapter service.  You will be prompted to enter ACS information; enter the information for the BizTalk Adapater Service ACS from Step 1. Right click on SQL, and click “Add a Target”.  For connection parameters, fill in the server information

step 2 of sql

Operations should look familiar to anyone who’s used the WCF SQL binding.  Go to Strongly-Typed Procedures and add the dbo.usp_ValidateSN:

operations sql

Choose your runtime security.  Here, I entered the username that has access to the procedures and its password as a Fixed username:

step 4 sql

Specify the LOB Relay URL using your newly created service bus namespace; for LOB Relay path, choose something that will be memorable this particular SQL relay path, and for the sub-path choose a unique identifier (like the InboundID parameter for WCF SQL binding in on prem BizTalk): step 5 sql

Review your information on the next page and click create.   Step 6: Create an EAI Bridge for this relay In Visual Studio, create a new BizTalk Service project (Under Visual C#).  Right click on your new LOB SQL type and click “Add schemas to <ProjectName>”; enter the project folder and filename prefix you want, and enter the credentials you set up in the previous step under runtime security.  This will generate two schemas that should look pretty familiar if you’ve used WCF SQL before. Drag the LOB SQL type over onto your design surface (named MessageFlowItnerary.bcs by default).  Drag an XML Request-Reply bridge onto the surface from the toolbox.  Drag a connector between the two. On the connector, set the following properties: Filter Condition: Match All (will set to 1=1): this sets the connector to always send messages through; we’ll cover content routing in the next article in this series


Route Action: configure as follows (again, this should look familiar!): this sets the SOAP action header


Your surface should look like this now (after renaming the XML bridge to SQLRequest):


Double click the SQL LOB icon (circled). In the config file that pops up, edit the sharedSecret node with  the information from your ServiceBus relay ACS (not BizTalk Services ACS):


Build and deploy the solution (you’ll be prompted for your BizTalk Services ACS information on deployment).  This operation should not require a refresh of the service, but go ahead and select it anyway.  Grab the MessageSender app: https://code.msdn.microsoft.com/windowsazure/Windows-Azure-BizTalk-EAI-af9bc99f.  Build it and run the following command with a generated instance of the request schema:

messagesender.exe <BizTalk Service Namespace Name> owner <BizTalk Service ACS Key> https://<name>.biztalk.windows.net/default/SQLRequest <instance of the request schema.xml> application/xml

A sample request message looks like this:

<ns0:usp_ValidateSN xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo">

And here’s the successful output:


Stay tuned for part 2!

9 Comments. Leave new

Excellent article … looking forward to part 2

Hi Dan, I am trying to insert a flat file data in to sql server table using Azure biztalk service
I have a concern here,
I am using this url http://localhost:8080/BAService/ManagementService.svc/ to add the biztalk adapter service. After adding ,setting the new sql lob target and deploying the application into the cloud , does this http://localhost:8080/BAService/ManagementService.svc/ url have any role on the cloud? Does the azure connect to the Biztalk adapter service url(which is used to create the Lob target) in any stage?
Or Is it just for creating the LOB target in the development environment?


The BAS connects to the relay end point on the Azure Service Bus. The URL itself is not accessible from outside the machine, but the service will connect to the Service Bus relay,and the relay can then be used to connect to the LoB system behind the adapter service.

Dan, meaning if I deploy the Biztalk service application with the adapter service url http://localhost:8080/BAService/ManagementService.svc/ on azure, the flat file data won’t be inserted into the sql database since the localhost url cannot be accessed from the cloud, is that correct?

No, the service will actually be able to connect through the relay you set up later in the tutorial.

I created the service bus namespace via azure PowerShell. In the Sql LOB target set up wizard, at the deployment step I used this namespace, service bus issuer name and service bus issuer key to set up the relay(I had also given the LOB relay path, LOB target sub path there). But I am getting ‘(400) Bad request ‘ error when I click on the Create button(at summary) . Is there any way to see the log file for the SQL LOB Target wizard?
Error Details:
Code: ’21’
Message: ‘The type Initializer for Microsoft.ApplicationServer.Integration.AFConnect.Management.HostingHelper’ threw an exception
What can cause this error?

here is the link for the error screen shot
error details

Not sure Hari, Sorry. I’d double check to make sure you have the latest version of the SDK installed, and double check machine.config as explained at http://msdn.microsoft.com/en-us/library/azure/hh689760.aspx#BKMK_Installation

A wonderful job. Super helpful inafrmotion.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>