What is BizTalk Adapter Service?

BizTalk Adapter Service allows an application in the cloud to communicate with an on-premise Line-of-Business (LOB) system behind a firewall. Using the LOB adapters in BizTalk Adapter Pack (BAP), a BizTalk Adapter Service application can execute LOB operations to the following on-premise LOB systems:

  • Microsoft SQL Server
  • Oracle Database
  • Oracle E-Business Suite
  • SAP
  • Siebel eBusiness Applications

BAS Scenario

There is an on-premise SQL Server that stores Customer data, including Name, Shipping Address, Order History, and Payment Methods. You want to develop a purchasing application. When the order is being placed, this application retrieves the Name, Shipping Address, Order History, and Payment Methods from this SQL Server database. The goal is to host the application in the cloud and have it retrieve the customer data from this on-premise SQL Server.

BizTalk Adapter Service is your answer. You continue to maintain your on-premise SQL Server and allow the cloud to host your application. You can execute any LOB operation, like DELETE, INSERT, SELECT, UPDATE, from your cloud application.

For further information please refer here.

BizTalk Adapter Service Architecture

BAS Architecture provides information on BizTalk Adapter Service Architecture.

BizTalk Adapter Service Setup installation

Pre-Requisites

Missing Pre-Requisites can cause issues in BAS installation. Please make sure you install all the required Pre-Reqs.

Here’s the list of pre-requisites for BAS. There’s a smaller list if the user is installing only runtime/tools but excluding that from this section. 

  1. Windows Server AppFabric v1.0 or higher  
  2. IIS 
  3. IIS Windows Authentication feature 
  4. BizTalk Adapter Pack 64 bit 
  5. BizTalk Adapter Pack 32 bit 
  6. ASP.NET 

Note: For Win 8/ Win Server 2012 machine, ASP.NET4.5 has to be enabled at 2 separate places. 

    1. Windows 8:  
      1. Control Panel -> Programs -> Turn Windows Features On or Off –> Internet Information Services –> World Wide Web Services –> enable under Application Development Feature.  
      2. Control Panel -> Programs -> Turn Windows Features On or off –> enable under .NET Framework 4.5 Advanced Services. 
    2. Windows Server 2012: 
      1. Server Manager -> Add Roles and Features -> Server Roles Web Server (IIS) -> Web Server -> Enable under Application Development Feature. 
      2. Server Manager -> Add Roles and Features -> Features -> enable under .NET Framework 4.5 Features 
    3. For other Lower Versions: 
      1. For windows Server machine: Server Manager -> Roles -> Web Server (IIS) -> Add Role Services -> enable under Application Development Features  
      2. For Client machine: Control Panel -> Programs -> Turn Windows Features On or Off -> Internet Information Services –> World Wide Web Services –> Application Development Features  

       7. ISAPI Extensions 

    1. For windows Server machine: Server Manager -> Roles -> Web Server (IIS) -> Add Role Services -> enable under Application Development Features 
    2. For Windows 8/7: Control Panel -> Programs -> Turn Windows Features On or Off –> Internet Information Services –> World Wide Web Services –> Application Development Features 

       8. ISAPI Filters 

    1. For windows Server machine: Server Manager -> Roles -> Web Server (IIS) -> Add Role Services -> enable under Application Development Features 
    2. For Windows 8/7: Control Panel -> Programs -> Turn Windows Features On or Off –> Internet Information Services –> World Wide Web Services –> Application Development Features 

While anything missing from above list would prevent installation from succeeding, there are some additional pre-requisites for BAS to work correctly. The list includes 

  1. CGI 
  2. HTTPACTIVATION  

If the user is running into issues installing BAS, we should ask the user to run the setup with extended logging option “msiexec /i BizTalkAdapterService .msi /l*vx logFile.txt“. The log file will have more details on the exact cause of the failure. 

Troubleshooting

Common errors observed and reason for the same 

  • The setup fails unexpectedly, the log file shows the following error: “Error 26201. Error -2147467259: failed to create SQL database: BAService, error detail: unknown error”  

In the setup, it prompts for the SQL server/instance name, Make sure it correctly maps to the SQL server on your local machine/ the server you are targeting. 

  • The setup fails unexpectedly, the log file shows the following error: “CustomAction AddIsapiCgiFilter returned actual error code 1603 (note this may not be 100% accurate if translation happened inside sandbox)” 

This error generally prompts on Windows 8, 32-BIT machine. Please ensure ASP.NET 4.5 is installed on the machine. Please Install ASP.NET 4.5 and try re-installing the BAS setup. 

  • The setup fails unexpectedly, the log file shows the following error: ”Calling CustomAction SBConnectCA!Microsoft.ApplicationServer.Integration.AFConnect.Program.AddIspiCgiFilter 

Enable ISAPI/CGI filters: Enter 

ERROR: System.UnauthorizedAccessException: Filename: redirection.config 

Error: Cannot read configuration file due to insufficient permissions” 

Please try running the setup with Administrator Privileges. Right Click on the Setup file and “Run as Administrator” 

Recommended Installations

  • I have installed all the pre-requisites and the setup installed successfully. But still I cannot host any .svc files in my browser. Why? 

Please make sure that these three windows features are turned on your machine. These are required to serve the WCF .svc files. 

  • HTTPACTIVATION 
  • CGI 
  • WINDOWS PROCESS ACTIVATION 

Step by step procedure to connect to a SQL server from BizTalk Services Project

1)      After you have the BAS (BizTalk Adapter Service) setup installed in your machine. In Visual Studio, you can see a Server Explorer Component “BizTalk Adapter Services”

 

 

2)      Right click on BizTalk Adapter Service and click on “Add BizTalk Adapter Service…”

   

                Specify the server name on which your SQL data exists.

                If you are targeting the local system specify “localhost” in the highlighted.

 

3)      After establishing the connection you can see the following 5 LOB types.

           

Right click on the LOB type you want to connect to, SQL for instance.

 

4)       Right click -> Add SQL target..

Connection Parameters:

 

 

It throws this wizard and helps you in creating services for your on-premise LOB systems that can be accessed from the cloud.

 

Here specify the respective Sql Server database details:

Server: The server you want to target

Instance: The sql server instance you have in your machine, ex: sqlexpress

Catalog: The Name of the Database you want to access.

 

After entering these details Specify the respective credentials and click next..

 

5)      Operations:

Here I can select all the operations I can expose.

Consider the following EmpData table, here I want to expose all the 4 operations “Delete”, “Insert”, “Select”, “Update”.

Click on the desired operation and add it.

 

 

 

6)      Runtime Security:

This is the critical part. This is the runtime security.
The authentication that we specified in the Connection parameters is just the design time security. But this authentication specified here will be validated when we try connect to on-prem sql database. This Is the credentials that will be verified when a message come from the cloud.

 

We have 4 different options:

  1. FIXED USER: This is the SQL authentication that we provided in the machine.
  2. FIXED WINDOWS CREDENTIALS: You can provide a Domain, Username and Password to connect to the SQL Database.
  3. CUSTOM SOAP HEADER: The client as a part of message is going to sent a UserName and Password to connect to Sql Database. This will come as part of the SOAP header.
  4. MESSAGE CREDENTIAL: Client will send username and Password in WS-Security header of the message.

Select the respective secutiry type and click next.

 

 

7)      Deployment:

A single LOB relay can host multiple LOB targets.

If you have an existing LOB Relay you can use that. You can select an existing relay.

Or

You can create an entirely new relay.

 

 

 

If you want to create a new realy specify the 4 required fields: Namespace, Issuer name, Issuer Secret, Relay Path.

 

You need to provide a sub path to identify your target.

it shows the runtime URL based on the details we enter:

Format: “https://LOBrelayNamespace.windows.net/lobrelayRelativePath/TargetSub-Path”

 

Click next.

 

8)      Summary

It shows a summary of details you entered cross check them and click create to create the relay on the service.. Congratulations J

 You can see the LOB Target in the server explorer.

 Now create a BizTalk Services project and In the MessageFlowItinerary.bcs file just drag the required LobTarget..

 

 

 

Any message sent to the runtime Address path, shown in the propeties of the LobTarget properties, will be targeted to the On-Premise SQL DataBase you are connected to.

 

BizTalk Adapter Service PowerShell Cmdlets

PowerShell is a Windows command-line shell typically used by system administrators. The BizTalk Adapter Service Runtime Server utilizes the cmdlet (command-let) ability of PowerShell to expose the LOB Relay and LOB Target entities. Using PowerShell, BizTalk Adapter Service Administrators can manage the BizTalk Adapter Service Runtime Service and these LOB entities. The BizTalk Adapter Service PowerShell cmdlets can be executed directly on the BizTalk Adapter Service Runtime server or on any other server.

For further information on BAS PowerShell Cmdlets please refer here.

Blog Post by: BizTalk Blog