Microsoft Integration Weekly Update: August 13, 2018

Microsoft Integration Weekly Update: August 13, 2018

Do you feel difficult to keep up to date on all the frequent updates and announcements in the Microsoft Integration platform?

Integration weekly update can be your solution. It’s a weekly update on the topics related to Integration – enterprise integration, robust & scalable messaging capabilities and Citizen Integration capabilities empowered by Microsoft platform to deliver value to the business.

If you want to receive these updates weekly, then don’t forget to Subscribe!

Feedback

Hope this would be helpful. Please feel free to reach out to me with your feedback and questions.

Advertisements

Our Integration Product Roadmap

Microsoft customers and partners are asking what our cloud-hybrid integration roadmap is for BizTalk Server and beyond. BizTalk Server 2016 is our tenth release of a product that first shipped in December, 2000. Much has changed in the past 18 years, the rise of the Internet, the move to the cloud and the advent of Integration Platforms as a Service (iPaaS) being just a few. One thing has prevailed over these many years, existing enterprise customers running business critical workloads using BizTalk Server on-premises – and Microsoft’s continued commitment and support to these customers.

We want to confirm our commitment to BizTalk Server by providing clarity on our cloud-hybrid integration roadmap, of which BizTalk Server is a key part. Shortly after we released BizTalk Server 2016, we introduced the concept of Feature Packs – incremental product improvements and additions that facilitated a much faster ship cycle than was before possible. We believe, and have also heard from customers, that this approach is advantageous for a number of reasons: quicker time to get requested features or improvements; fewer costly upgrade cycles; continuous product investments. Yet, we understand that this model does not suit every customer, especially those that require more notice, planning and preparation for consuming both new platforms and new functionality.

Therefore, we are pleased to announce that we have begun work on BizTalk Server “vNext”, the next major release of BizTalk Server. BizTalk Server “vNext” will contain all previously released feature packs, platform support for the latest versions of Windows Server, SQL Server and Visual Studio, as well as a supported upgrade path from BizTalk Server 2013 R2 and 2016. The new release of BizTalk Server will be accompanied by a vNext of the Adapter Pack and Host Integration Server as well. We are in the early stages of planning this next release and more details will follow in due course, but we expect to make it generally available within roughly 9 months of the general availability of Windows Server 2019.

Alongside BizTalk Server, we will also continue to expand the capabilities of our iPaaS offering providing additional hosting options for Azure Integration Services, which includes Logic Apps, starting with the ability to run on-premises. We expect to deliver these capabilities during calendar year 2019.

With two offerings, BizTalk Server and Logic Apps, both capable of running on-premises and in Azure, many customers will be evaluating migration from BizTalk Server to Logic Apps. We understand this and are planning guidance on migration to assist customers’ decision-making and investments to enable this move. We know there are functional gaps between BizTalk Server and Logic Apps, and we are hard at work closing them. Our Azure Integration Services vision is to enable all customers to move to a modern, holistic and rich iPaaS platform that can meet the challenges of all their integration workloads, both today and in the future wherever they reside.

Jon Fancey, Azure Integration Services PM Lead

BizTalk Server and GDPR Considerations: How to properly restrict access to SQL Server stored procedures

BizTalk Server and GDPR Considerations: How to properly restrict access to SQL Server stored procedures

We all know that the WCF-SQL adapter enables BizTalk Server to perform composite operations on any SQL Server database. A composite operation can include any number of the following operations, and in any order:

  • The Insert, Update and Delete operations on the tables and views
  • Stored procedures that are surfaced as operations in the adapter

It can also execute Transact-SQL and CLR:

  • Stored procedures in an SQL Server database
  • Scalar and table-valued functions in an SQL Server database
  • And so on

In resume operations at the Tables, Views, Procedures, Scalar Functions, and Table-Valued Functions, levels will be supported.

Stored Procedure permissions

I personally like to use Stored Procedures instead of directly accessing the tables which are available in the database.

Regarding the required access permission in SQL Server for BizTalk Server, to connect to a particular database to extract or store data, or in this case, be able to call stored procedures, what teams normally do is creating:

  • A new SQL user with “db_owner” privileges
  • Or they give “db_owner” privileges to the service account that is running the BizTalk Server host instance, for example, “BTSHostSrvs” (BizTalk Host Instance Account)

Why? Because this is simple and quick, and they don’t need to worry about lack of permissions or the proper permissions.

GDPR considerations

But sometimes these tables contain sensitive data or personal data, and nowadays with General Data Protection Regulation (GDPR) in the European Union (EU), this sometimes can be a backdoor for other possible problems. Teams need to start thinking in concepts like “Privacy by Design” and “Privacy by Default” for their solutions:

  • Privacy by Designs” holds that organizations need to consider privacy at the initial design stages and throughout the complete development process of new products, processes or services that involve processing personal data
  • Privacy by default” means that when a system or service includes choices for the individual on how much personal data he/she shares with others, the default settings should be the most privacy-friendly ones

So, companies should be more careful and more strict in:

  • Who has access to what?
  • Limit the number of persons that can access that information to the strictly essential persons
  • Define a better access granularity and restrict access, once again, to the essential tasks
  •  A service account that consumes or store new data shouldn’t be a database owner or a sysadmin.

Secure Stored Procedure permissions

Of course, giving “sysadmin” or “db_owner” would solve all our problems but it goes against security best practices.

One way, or -personally- the best way, for you to properly define a better access granularity and restrict access to the essential tasks or in other words, the essential stored procedures, is to create a new server role, for that particular database, in SQL Server. Follow below steps to create such a server role:

  • Open SQL Server Management Studio and connect to your SQL server
  • In the Object Explorer, access to your database and expand it
  • Expand the Security folder
  • Right-click the “Database Roles” folder and select “New Database Role…”
  • In the “New Database Role” window
    • On the “Role name” property, on the General page, enter a name for the new database role, for example, “db_spexecution”
    • At the Securables page, under Securables, click the “Search” button
      • On “Add Objects” window, select “Specific objects…” and click “OK”

BizTalk Server and GDPR Considerations: creating a new role

    • On “Select Objects” windows, click “Object Types…” and then select “Stored Procedures”

BizTalk Server and GDPR Considerations: creating a new role

    • After selecting the object type, click “Browse…” and from the “Browser for Objects” window, select the stored procedures you want to invoke(only the one that you need)

BizTalk Server and GDPR Considerations: creating a new role

    • Click “Ok” and again “OK” to return to the main “New Database Role” window
  • The last step, on the Securables page, is to give Execute permissions “Grant” and “Grant with

BizTalk Server and GDPR Considerations: creating a new role

  • Finally, on the General tab, add the service account that is running the host instance to the Role Members for that role

BizTalk Server and GDPR Considerations: creating a new role

  • Click “OK” to finish

It gives you more work, that is for sure, but now you will have a properly access granularity defined, with the minimum rights defined for the actually necessary tasks. Nothing more, nothing less… as things should be.

Author: Sandro Pereira

Sandro Pereira is an Azure MVP and works as an Integration consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc.

Access Denied and COM Activation failure after installing Windows July 2018 Security Updates

Access Denied and COM Activation failure after installing Windows July 2018 Security Updates

Recently Microsoft rolled out the July 2018 .NET Framework Security Updates. These Security Updates caused series of errors in the BizTalk Server Administration Console, SharePoint, Internet Information Server (IIS) with classic ASP and .NET applications which use impersonation.

Related to BizTalk Server, the issue is that BizTalk server relies on a COM component, that runs with restricted permissions. This COM component may fail to start after installing the July 2018 Security Updates.

For BizTalk360 to monitor the BizTalk server, it needs that the BizTalk Server Administrator components are installed in the server. But when the BizTalk360 server is also updated with the security patches, the same errors will occur while accessing the BizTalk applications and their artifacts. Many of our customers raised support tickets to BizTalk360 support channel, as they were not aware of these issues due to the windows security patch.

How the errors occur in BizTalk360 and in BizTalk Server

The com exception will occur in BizTalk360 only after the July 2018 security patch update is installed on the servers. The issue will pop up on many occasions as mentioned below.

While accessing,

  •  BizTalk360 application 
  • BizTalk Applications and their artifacts
  • Hosts and Host Instances

Following screenshot refers, how the exception appears in BizTalk360.

access denied and com activation failure in BizTalk Server

When launching BizTalk Server, a series of errors appear from Hosts and Host Instances.

Here are the errors that occurred in the BizTalk Admin console, When trying to access the host instance

access denied and com activation failure in BizTalk Server

Root Cause of the problem

Let ’s have a look at the root cause of these issues in detail. The Microsoft .NET Framework runtime uses the process token to determine whether the process is running within an elevated security context. These system calls can fail if the required process inspection permissions are not present. This causes an “access denied” error. However, these errors might reveal themselves with other error messages, as we have seen from the screen prints earlier in this article.

A temporary workaround is discussed in this MSDN thread. The simplest workaround is to uninstall the security patch and everything will be back to normal.

Resolution

The latest patch was re-released by Microsoft on July 30, 2018. This update helps to resolve this issue. This patch has different versions applicable to your operating system and .NET Framework installed.

  • 4346877Update for Windows 10, version 1607 and Windows Server 2016: July 30, 2018
  • 4346406Update for .NET Framework 4.6, 4.6.1, 4.6.2, 4.7, 4.7.1 and 4.7.2 on Windows 8.1, RT 8.1 and Server 2012 R2 (KB 4346406): July 30, 2018
  • 4346405Update for .NET Framework 4.6, 4.6.1, 4.6.2, 4.7, 4.7.1 and 4.7.2 on Windows Server 2012 (KB 4346405): July 30, 2018
  • 4346407Update for .NET Framework 4.6, 4.6.1, 4.6.2, 4.7, 4.7.1 and 4.7.2 on Windows 7 SP1 and Server 2008 R2 SP1 and .NET Framework 4.6 on Server 2008 SP2 (KB 4346407): July 30, 2018
  • 4346408Update for .NET Framework 4.5.2 on Windows 8.1, RT 8.1 and Server 2012 R2 (KB 4346408): July 30, 2018

For detailed information about latest patch release, please click this link. Microsoft had also suggested a workaround for the issue but with the following warning message:

Warning: The workarounds may make a computer or a network more vulnerable to attack by malicious users or by malicious software such as viruses. We do not recommend these workarounds. However, we are providing this information so that you can implement the workarounds at your own discretion. Use these workarounds at your own risk.

Customer Feedback

We have received quite a few support tickets on this specific cases from our customers who have recently update the security patches. Because, the problem lies with BizTalk Server, It has affected the BizTalk360 as well while accessing the applications. We informed the customers about the reason for the error. The new patches are uninstalled, BizTalk360 and BizTalk Server will be back to normal. Here is a happy feedback from our most valued customers.

Microsoft Integration Weekly Update: August 06, 2018

Microsoft Integration Weekly Update: August 06, 2018

Do you feel difficult to keep up to date on all the frequent updates and announcements in the Microsoft Integration platform?

Integration weekly update can be your solution. It’s a weekly update on the topics related to Integration – enterprise integration, robust & scalable messaging capabilities and Citizen Integration capabilities empowered by Microsoft platform to deliver value to the business.

If you want to receive these updates weekly, then don’t forget to Subscribe!

Feedback

Hope this would be helpful. Please feel free to reach out to me with your feedback and questions.

Advertisements

Azure API Management Feature: SOAP to REST

Azure API Management Feature: SOAP to REST

By Bill Chesnut

This is the second post in a multi part series on the features of Azure API Management.

As with the previous post where I demonstrated publishing a SOAP Services with pass-through, this time I am going to demonstrate publishing the same SOAP Service as REST, using the SOAP to REST feature of API Management, I consider this feature very important to APIM, in the past many of my clients have built intermediate services using either BizTalk or .Net.

For this blog post I am going to demonstrate how you publish a BizTalk SOAP service as REST in APIM. With APIM you can publish SOAP services by importing the WSDL (this can be either via the URL or by uploading the WSDL file), In APIM Click the “API” menu item on the left, then Click “WSDL”

image

In this demonstration I am going to use an uploaded file, this is the WSDL from a BizTalk orchestration exposed as a WCF Request/Response Service, it only has a single operation ‘submit”. I am using an uploaded file because the BizTalk server is hosted in an Azure Virtual Machine and I have changed the URL to reflect the DNS name of the Virtual Machine. I could have also changed the name in APIM after the WSDL was imported.  Select “SOAP to REST” and configure as shown and click “Create”

image

Once the WSDL import is complete, we can now see the API and it’s operations, now let click settings to look at the API settings, this is where we can change the URL to the BizTalk Server hosting our SOAP Service. Click the “submit” operation, then click the “policy editor”

image

Now look at the policy that does the transformation from REST/JSON to SOAP/XML.

image

Inbound

image

Outbound

image

Now lets click the “Test” menu, this is where we can test our API Operation, before we release them to our consumers

image

APIM fills in all the details to test the API, notice that the payload for the API is JSON, this is because we chose SOAP to REST, SOAP services are XML based, but the above policy we looked at does the conversion from JSON to XML. Click “try it”

image

You can now see the results of our call to the BizTalk hosted SOAP Service, the results are in JSON again the policy is doing the conversion, now lets click the “trace” tab, this will give us everything that has taken place in APIM as part of our call to the BizTalk SOAP service.  You can notice that the policy has set the SOAPAction and converted the JSON body to XML

image

The Policy for the SOAP to REST feature is using Liquid Template Language to do the JSON to XML and XML to JSON transforms, these templates can be use to transform from JSON to XML, XML to JSON, XML to XML and JSON to JSON, this allow not only the scenario for SOAP to REST, but the ability to manipulate both inbound and outbound payload for maximum flexibility.

Hopefully this has given you a quick demonstration on how to expose your SOAP Services as REST with APIM.

Cross Posted on http://www.sixpivot.com.au

BizTalk360 with SQL Server Express Edition

BizTalk360 with SQL Server Express Edition

Introduction

Most of users prefer to install BizTalk360 in a standalone sever. Importantly, this approach does not overload the BizTalk environment or the BizTalk360 instance. Quite often, BizTalk360 database is configured in the same SQL Server instance as where BizTalk databases resides. In this case, configuring database in a separate SQL Server Instance for better performance and disk usage. Considering this fact, customers are acquiring a dedicated SQL Server for the BizTalk360 database. Meanwhile, few customers are using SQL Server Express edition, when they  don’t prefer to buy a separate license for SQL Server.  This article covers things that need to be considered when BizTalk360 with SQL Server Express Edition.

Customer Scenario

BizTalk360 is installed on a standalone server with all components, which are:

  • The database
  • The web portal
  • The monitoring
  • The analytics services

The customer has installed SQL Server 2017 Express edition

SQL Server Express Edition

Let us see features and limitations of SQL Server Express edition.

Features

  • There is no requirement of license for using it, as it is free for distribution
  • It is an easy to use version, designed for building simple data driven applications
  • Applications develop faster through the deep integration with Visual Web Developer, Visual Studio, and so on
  • Easy to create and share reports that answer complex questions through basic reporting services
  • Provides backup and restore functionality and compatibility with all its versions

Limitations

  • SQL Server Express can be installed on a server with many CPUs. But, it can use only one CPU at a time and 4GB database size limit was there for SQL2005/2008 but after 2008R2 it has been increased to up to 10GB for each database. For this limit, only the size of the Data file is considered. The size of the Log file is not relevant
  • Can use a maximum of 1GB memory for temporarily storing the data while it is being transferred from one place to the other
  • The performance analysis tool, Profiles, is not included with the SQL Server Express edition
  • The functionality to create and attach a schedule to a job (Job Scheduler) is not available with the express edition
  • You cannot import or export the data with the SQL server express as this feature is also not available

BizTalk360 as Lightweight

Under this circumstance, user must take care few considerations in BizTalk360 to keep the size of database within the value supported by SQL Express Edition.  For better performance of data processing, make sure you keep the database as light as possible.

The following configuration will help to maintain BizTalk360 database as lightweight:

  • Event Log
  • Analytics
  • Data Purging

Event Log

In a BizTalk environment it’s important to monitor the Event Log to avoid any activities blocked at the back-end. A BizTalk Administrator’s main job is to keep BizTalk System Resources, Disk Usage and Database processes 100% healthy!

BizTalk360 collects Event Log data based on configured Event Logs and Event Log Sources.  With this collected data, the BizTalk360 Monitoring service will send notifications based on configuration (Threshold and Data Monitoring).  From the Operations section, Event Log data access functionality is also available with all kind of filtering capabilities.  In BizTalk Reporting Analytics, the Event Log widget is populating the report with “Top 10” Event Log sources.

Event Log data collection will need more space based on the configured number of sources and the number of physical BizTalk Servers and SQL Servers in an environment.  We can see the possibility to reduce the amount of event log data collection by:

  • Configure just the important Event Log type and sources you want to monitor
  • Enable Event Log data collection only at the physical server level (BizTalk/SQL) for which Event Log monitoring is important

Analytics

In a BizTalk production environment most of the users are using the BizTalk360 Analytics capabilities. The ‘Analytics’ section has the Performance Analyser functionality, the Messaging Patterns and the Throttling Analyser. The BizTalk360 services are also collecting data to generate the graphical widgets which are shown in multiple dashboards. Both Throttling and Performance data are collected every 15 secs. In a complex BizTalk environment data collection is at considerable size.

Message Pattern

BizTalk Artifacts tracking is enabled at port level and at pipeline level. BizTalk360 uses its Analytics service to determine Message Patterns are by querying tracking data which is collected from the BizTalkDTADb. Similarly, tracking data is used to visualize messaging performance by port and by pipeline.

Throttling Analyser

The Analytics service is responsible for collecting the Throttling Performance Counter data from all the BizTalk servers in the environment. The BizTalk360 Throttling Analyser helps to simplify the complexity in understanding BizTalk throttling mechanism and provide a simple dashboard view.

Users can observe the ways to optimize the data collection;

  • Disable unnecessary tracking (Port and Pipeline). When possible, try to limit to Event tracking and prevent using Message body and context tracking, it will also boost performance of the BizTalk Tracking database
  • Configure  requirement performance counters in an environment to boost the data collection

To be able to get a good overview of all the Tracking settings within your BizTalk environment and to configure these settings in one consolidated screen, you can use the Advanced Tracking Manager.

Advanced Tracking Manager

Data Purging

BizTalk360 comes out of the box with the ability to set purging duration. The background Monitoring service has the capability to purge older data automatically after a specified period.  Data purging is configurable for different activities in which can be found under BizTalk360 Settings > Data Purging. Through this feature, you can control the size of data of the BizTalk360 database.

Compromising Performance and Storage? 

We have seen about the performance and the size of data with several features in BizTalk360. Using SQL Express edition with 4 CPU Cores, 1410 MB of buffer pool size and storage of 10 GB in a BizTalk Production environment is critical. A BizTalk/Database administrator must monitor the size of the database by setting purging policies to a minimum period for data persistence.

Conclusion

Consider using SQL Express edition with BizTalk360 based on case by case as we discussed.

  • Using SQL Express Edition in a critical production environment is not suggested as it compromises performance and data storage. For those BizTalk environments with more than one BizTalk servers, we advise to use a higher edition of SQL Server
  • In Non-Production environments install different BizTalk360 instances by considering
  1. Enable important Event Log source to data collection.
  2. Another key point is configure single BizTalk environment in a BizTalk360 Instance.
  3. Periodically check the size of database and optimize the collected data.
Author: Senthil Palanisamy

Senthil Palanisamy is the Technical Lead at BizTalk360 having 12 years of experience in Microsoft Technologies. Worked various products across domains like Health Care, Energy and Retail.