Control and get better insight of your Event Log data using BizTalk360 Advanced Event Viewer

Control and get better insight of your Event Log data using BizTalk360 Advanced Event Viewer

Are you using the Event Log for diagnosing problems and challenges faced while performing the operation in BizTalk server administration console? Then you might face the difficulty, on having multiple BizTalk / SQL Server users who need to look Event Logs on each server when there is a problem. To access the Event Log, every user should have administration permission to access the physical server, which is not the ideal solution for the business needs.

What is the purpose of the Event Log on a BizTalk Server?

The Event Log holds information related to different aspects of, amongst others, BizTalk Server. The Windows Event Logs are used by the BizTalk admins, mostly for diagnosing problems. When they face any issue, the first step they try to diagnose the problem or faults in the BizTalk Admin console itself. If they could not achieve a solution, then they will view the Event Viewer for detailed information on such cases like errors, messed up environments and failure of message processing.

Filter for log noise reduction during Event Log collection

The BizTalk360 Advanced Event Viewer which collects the events that are related to BizTalk and SQL sources. These sources can be configured in Settings–>Advanced Event Viewer, as shown below.

Insights and control your BizTalk Environment using BizTalk360 Event Log viewer- Source configuration screen

The Event Logs are collected based on the Event Log Categories and the administrators can also add categories as per their need. The data collection can be performed based on the environment selection as below.

Insights and control your BizTalk Environment using BizTalk360 Event Log viewer - Server configuration screen

Once, after enabling the “Enable Advanced Event Viewer data collection”, that will display all the available BizTalk and SQL server of the specific environment. The BizTalk admins or the people using BizTalk server can select the servers based on their needs. Based on the configurations, the subservice “Eventlog Collection” of the BizTalk360 Monitoring NT service will start to collect Event Log data and store the information in the BizTalk360 database.

This approach will seamlessly solve the problems as we highlighted in this article. The BizTalk administrators no more need physical access to each BizTalk or SQL server to diagnose the issue. This approach will avoid data collision, improves the performance and makes the user interface clear.

Let’s have a look at few scenarios where using the Advanced Event Viewer is beneficial over the Windows Event Viewer.

Scenario 1: If there is any failure of file transaction at an application level, the user can view the relevant event log information in the single user interface. This will avoid context switching and reduces the time to diagnose the issue.

Diagnosing issue made easier than Windows Event Viewer

In real time business transactions using BizTalk can cause any kind of failure.

Scenario 2: When you try to run a BizTalk application after deploying an orchestration with custom components. In case, the BizTalk orchestration engine cannot locate the custom component, that means you will get below error.

Insights and control your BizTalk Environment using BizTalk360 Event Log viewer - A sample error in windows Event viewer

It is easy to query the Event Log details if BizTalk server is installed in a standalone environment. However, in case of multiple BizTalk servers, the user needs to login to each server to find the Event Log information and it is a tedious process to search the exact issue on ‘n’ number of results. So, it will eat up lots of time on finding the exact result.

In the BizTalk360 Advanced Event Viewer, diagnosing the problem or issue, in a single user interface related to the event “application” of BizTalk and SQL search, is made much easier than with the Windows Event Viewer.

Insights and control your BizTalk Environment using BizTalk360 Event Log viewer- Operation capability

With the Advanced Event Viewer, the BizTalk admins (and other people) can search the results using rich query builder tools, based on the different parameters such as the Event Log, Type, Source, Event, Category, User, Computer, Timestamp, and Message. One or more filters can be added, which will be useful to get an exact result and the user can view the related information the same as in the Event Viewer.

This approach will fasten the search of the Event Log information from the different servers within a stipulated time which obviously saves time.

Archiving distributed events on Centralized Location

BizTalk360 help to get the needed Event Log data from the Event Logs on different servers and shows the information in a simple, intuitive user interface. However, it is practically impossible to manually keep an eye on the Event Log data throughout the day, as they are constantly updated with the latest log data.

In BizTalk360, we solve this problem by downloading the data into Excel Format. The BizTalk admins can download the file by using the Export to Excel feature, that will download the grid data in Excel format and they can store the Event Log Data for further investigation, as shown below.

Insights and control your BizTalk Environment using BizTalk360 Event Log viewer - Download screen

It is possible to monitor Event Log Data!

The Windows Event Logs hold all the important log information from sources like SQL Server, IIS, BizTalk infrastructure and run time problems. As the Windows Event Log contains that much data, it is nearly impossible to monitor the Event Log manually. Luckily, through BizTalk360, the BizTalk admins can easily monitor the BizTalk and SQL server logs by configuring the Event Log monitoring.

Scenario 3: The BizTalk admins can customize the alert in such a way that they must be notified when there has been a sequence of start/stops of the host instances in the past 30 minutes. Without being alerted, you may not know the frequent start/stops of the host instances and this could impact the performance of the environment.

Insights and control your BizTalk Environment using BizTalk360 Event Log viewer - Advanced data monitoring screen

By mapping the Event Log to the Alarm, the administrator will get notified when the Event meets the condition. The alert can be notified through the required sources like Email and notification Channels.

The administrators can also log this Event Log alert in the Event Viewer itself, this is possible by enabling the Event Log option in alarm creation as below

Insights and control your BizTalk Environment using BizTalk360 Event Log viewer- General Monitoring event log flag

This will log the Event log alert in the Event Viewer when the alert exceeds the Host Start/Stop error/warning condition as below

Insights and control your BizTalk Environment using BizTalk360 Event Log viewer - Event log data in Operation screen

Create Log rules with an intuitive interface using the Event Log Data Monitoring capability

The primary purpose of auditing Event Log for BizTalk administrator is to discover the events of interest, whether they provide general information or call for further investigation. BizTalk360 will help to get the needed log data from multiple servers and shows the information in a simple, intuitive user interface. However, it is practically impossible to manually keep up the data throughout the day, as they are constantly updated with the latest log data. BizTalk360 also solved this by giving the real-time alert features on Event Log Data Monitoring.

Scenario 4: BizTalk Administrator wants to monitor different Events from multiple servers. For example, they might want to monitor the ESB from BizTalk Server at the same time they want to ensure the performance of the SQL Server & ENTSSO events from a separate SSO server and they also need to monitor the IIS from multiple BizTalk servers. In real-time, there is no option to monitor it with a single tool. It is hard to predict any issues and it is time-consuming for BizTalk admins to constantly monitor the Windows Event Log manually.

BizTalk solves this problem by bringing the heterogeneous data in the single intuitive user interface. On the top of that, it provides a powerful capability of getting real-time notifications through email or SMS when an event occurs on the specific frequency by setting additional threshold conditions. The admins can correlate two or more event calls for the further investigation by setting the conditions as shown below.

Insights and control your BizTalk Environment using BizTalk360 Event Log viewer- Advanced Data Monitoring configurations screen

It will be useful to query against different Event Sources of multiple servers of a single BizTalk Environment. The admins can include or exclude the events that pertain to your environment. Also, the admins can get the detailed notification on Event Logs by selecting the ‘Send Event Log in notification’.

Insights and control your BizTalk Environment using BizTalk360 Event Log viewer- Data Monitoring Filter screen

This will send the detailed information on every event source in Email which will look like below.

Insights and control your BizTalk Environment using BizTalk360 Event Log viewer - A sample Email alert

BizTalk Admins can choose the Notification Channel on the alarm creation and they can configure the Event Log as previously mentioned to get notified when any violation happened on a specific frequency. On the Data Monitoring Dashboard, the admins can visualize the triggered alert results based on the day view as shown below.

Insights and control your BizTalk Environment using BizTalk360 Event Log viewer- Dashboard View

It also easy to maintain, once after scheduling Event Log Data Monitoring; when you disable AEV for the environment, it will stop collecting Event Log data. So, the admins can enable collecting whenever they need it.

Is there any option to manage the Event Log data?

Managing the Windows Event Log is not simple. Administrators can manage or clean up the data through PowerShell script or by creating a custom log to log application errors. The BizTalk360 Monitoring Service collects the data based on the Event Log and Source configuration and stores in a separate database table in the BizTalk360 database.

In real-time business, BizTalk server will collect the log data for each event which happens during BizTalk operation. This can hugely increase the size of the database. Luckily, the administrator can set the purging for Event Log data in the BizTalk360 database as shown below.

Insights and control your BizTalk Environment using BizTalk360 Event Log viewer- Purge settings screen

Once after configuring the Purge Policy for Event Log data, it will clean up the data for every specific days/month/hour.

This approach will save the time and clean the old data easily, which will improve the overall performance of the BizTalk360 database. This also solves the problem of searching the current data with messed up old data.

Can we visualize the performance of Event Log?

 In BizTalk360 the BizTalk Administrators can indeed visualize the performance of Event Log data at a specific time.

Scenario 5: In any business environment, the performance metrics at a specific time will be critically important to take business decisions. To visualize the Event Log data the administrators can add the Event Log Data Count widget in the Reporting Dashboard on BizTalk Analytics.

Insights and control your BizTalk Environment using BizTalk360 Event Log viewer- Analytics Event Log widget screen

The Event Log reporting widget will show the exact count of Event Log data based on error, warning and critical errors for the available sources. The Event Log data widget will collect the data based on the scheduled time.

Leveraging the API Documentation Capability for Event Log

In BizTalk360, Event Log data in the API Documentation provides you with a very powerful way to integrate data and actions into your own tools or programs. Many of the BizTalk Administrators do not come from a development background and perceive an API as something for the exclusive use of BizTalk admins. This is not the case, and a mature and well-designed security product API can be of great value to other people, regardless of their development background.

Within BizTalk360, you can use the Event Log API Documentation to demonstrate broad functionality, primarily in the areas of:

  • user and organization account management
  • sample management
  • sample analysis data collection
  • threat intelligence harvesting

Insights and control your BizTalk Environment using BizTalk360 Event Log viewer - API Documentation screen

Of course, an easy way to use an API is to use a product that leverages that API.  Any RESTful API can be operated by anyone who can construct a web URI. the Event Log API documentation is also useful to create a custom widget using the JSON Response as shown below.

Insights and control your BizTalk Environment using BizTalk360 Event Log viewer - A Sample JSON Response screen

In an organization, you could easily tailor that to a list of IPs (or domains, or URLs, file hashes, etc) that had been seen in the analysis of only your own organization’s submitted samples with the help of this API’s.

Conclusion

To collect and process log data in real time, regardless of the volume of log data and the number of devices in the network, organizations need a robust log collection mechanism. BizTalk360 solves this problem by providing the Advanced Event Viewer functionality, which enables BizTalk Administrator to decipher and analyse any log data regard of its source configured. The collected log data is centrally aggregated and presented in a single console for log sources across locations. To summarise here are the Key features of BizTalk360 Event Log

Event Log Maintenance: Maintenance is also very simple when you disable AEV for the environment, it will stop collecting Event Log data. Manages data growth by Providing BizTalk360 purge policy.

Privileged Multiple Server Monitoring: The BizTalk Admins can monitor multiple BizTalk and SQL servers on a single user interface.

Server event trend analysis: It also protects security threats by collecting, analysing, reviewing and archiving event log. Through this, the BizTalk admins can get a comprehensive view of all the actions that is happening in the BizTalk server, SQL server, ENTSSO server etc..,

Real-time alerts: Receive instant alerts on unauthorized access to confidential data or even to the database server itself.

Intuitive dashboard: Drill down to the raw log level and view collected log content to facilitate simplified root cause analysis.

Out-of-the-box reports: Perform audits with pre-packaged reports that provide detailed information on Event Log Data through Event Log Count widgets.

BizTalk WCF-ORACLEDB error: PL/SQL: ORA-00917: missing comma

BizTalk WCF-ORACLEDB error: PL/SQL: ORA-00917: missing comma

In the last months I have been working with ORACLE adapter, mainly doing direct insert operations on ORACLE tables and as you might imagine, I found some errors that I find interesting to document. One of these errors was PL/SQL: ORA-00917: missing comma.

The first time I try to directly insert data inside a table – without using any stored procedure, that I normally use in SQL Server or other implementations that I worked with ORACLE – I got the following error:

A message sent to adapter “WCF-Custom” on send port “SEND-PORT-NAME” with URI “oracledb://IP-ADDRESS:PORT-NUMBER/PATH” is suspended.

Error details: Microsoft.ServiceModel.Channels.Common.TargetSystemException: ORA-06550: line 2, column 677:

PL/SQL: ORA-00917: missing comma

ORA-06550: line 2, column 1:

PL/SQL: SQL Statement ignored —> Oracle.DataAccess.Client.OracleException: ORA-06550: line 2, column 677:

PL/SQL: ORA-00917: missing comma

ORA-06550: line 2, column 1:

PL/SQL: SQL Statement ignored

at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck, Int32 isRecoverable)

at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck)

at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()

at Microsoft.Adapters.OracleCommon.OracleCommonUtils.ExecuteNonQuery(OracleCommand command, OracleCommonExecutionHelper executionHelper)

— End of inner exception stack trace —

Server stack trace:

at System.Runtime.AsyncResult.End[TAsyncResult](IAsyncResult result)

at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result)

at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result)

at System.ServiceModel.Channels.ServiceChannel.EndRequest(IAsyncResult result)

Exception rethrown at [0]:

at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)

at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)

at System.ServiceModel.Channels.IRequestChannel.EndRequest(IAsyncResult result)

at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result)

MessageId: {29C0CAD2-1D48-4318-8C86-E4A4E38FBD1C}

InstanceID: {F64C65F2-99F2-410E-A92E-418D146C16C9}

PL/SQL: ORA-00917: missing comma error

Cause

When you import the Insert (or other operation) schema from a specific table, unlike SQL, that only brings fields for you to fill, ORACLE schema will have:

  • Elements (fields) – that are the columns present in that specific table
  • and each Element will have an optional “InlineValue” attribute.

BizTalk-WCF-ORACLE-insert-operation-schema-structure

The element, as you can imagine is to send the value data that you want to insert in that specify column of the database but what is the InlineValue attribute? And what is this for?

InlineValue

For all simple data records in a multiple record Insert operation, you can choose to override the value of a record by specifying a value for an optional attribute called “InlineValue“. The InlineValue attribute can be used to insert computed values into tables or views such as populating the primary key column using a sequence or inserting system date (using SYSDATE) into a date column. Again, this is an optional attribute and is available for all simple data records in a multiple record Insert operation.

Basically, in other words, it allows you to call ORACLE PL/SQL functions like SYSDATE, TO_DATE or others for that specific column. And you don’t need to insert any data on the element, again, by specifying the InlineValue attribute this will override the value that you insert on that element.

Why you are getting the PL/SQL: ORA-00917: missing comma error?

This error typically occurs when you are mistakenly putting the data to be inserted in the “InlineValue” attribute and not in the elements:

<ns0:PAYMENTSRECORDINSERT xmlns:ns0="http://Microsoft.LobServices.OracleDB/2007/03/STGADMIN/Table/RETAIL_PAYMENTS">
  <ns0:RECORD_UNIQUE_ID InlineValue="12345" />
  <ns0:BOOKING_REF_NUMBER InlineValue="12345" />
  <ns0:SOURCE_SYSTEM InlineValue="TEST" />
  <ns0:PAYMENT_METHOD InlineValue="MONEY" />
  <ns0:CURRENCY_CODE InlineValue="EUR" />
  <ns0:REFERENCE_CODE InlineValue="1234" />
  <ns0:PAYMENT_TRANSACTION_ID InlineValue="1234" />
  <ns0:INTERFACE_STATUS InlineValue="N" />
</ns0:ADMM_RETAIL_PAYMENTSRECORDINSERT>

Solution

The solution, in this case, is very simple: you need to place the data on the existing elements of the schema instead of using the InlineValue attribute of the element:

<ns0:PAYMENTSRECORDINSERT xmlns:ns0="http://Microsoft.LobServices.OracleDB/2007/03/STGADMIN/Table/RETAIL_PAYMENTS">
  <ns0:RECORD_UNIQUE_ID>1234</ns0:RECORD_UNIQUE_ID>
  <ns0:BOOKING_REF_NUMBER>1234</ns0:BOOKING_REF_NUMBER>
  <ns0:SOURCE_SYSTEM>TEST</ns0:SOURCE_SYSTEM>
  <ns0:PAYMENT_METHOD>MONEY</ns0:PAYMENT_METHOD>
  <ns0:CURRENCY_CODE>EUR</ns0:CURRENCY_CODE>
  <ns0:REFERENCE_CODE>1234</ns0:REFERENCE_CODE>
  <ns0:PAYMENT_TRANSACTION_ID>1234</ns0:PAYMENT_TRANSACTION_ID>
  <ns0:INTERFACE_STATUS>N</ns0:INTERFACE_STATUS>
</ns0:ADMM_RETAIL_PAYMENTSRECORDINSERT>
Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a 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. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

Microsoft Integration Weekly Update: August 20, 2018

Microsoft Integration Weekly Update: August 20, 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

TLS Support in BizTalk360

TLS Support in BizTalk360

BizTalk360 is the One platform monitoring solution for BizTalk server. It only takes about 15 minutes to install the product and have it ready for use. The installation is very smooth and straightforward. During the installation, the web application, Monitoring and Analytics services and the database will be installed as per the selection of choice by the users.
One of the main advantage of the BizTalk360 installer is that you have the option to view the installer logs in case if there are any errors during the process. The installation log would contain the complete details of the installation steps, the service account details, the database connection string details etc. and of course the error details, if any.
Our BizTalk360 support team receive tickets from customers seeking assistance during the installation. In this blog, I would like to share an interesting case from a customer related to BizTalk360 installation.

The customer scenario

When the customer tried to install BizTalk360, they got below error in the creation of the BizTalk360 database step.

Here are the investigation steps

When the installer fails, our first step of investigation would be to check for the installation logs. In the logs, there was the following error message: Error 0x80004005: ‘failed to check if database exists’.
The next option would be to check for the permissions for the BizTalk360 service account in the SQL server hosting the BizTalk360 database. As a prerequisite for smooth installation of BizTalk360, the service account must be provided the db_owner permission for the BizTalk360 database. At the customer end, all the permissions were intact but still the error persisted. After a thorough analysis, we found the root cause to be with the TLS version installed in the servers.

Standards for securing communication

As database driven applications are increasing their hold on the systems market, the security of the retained information is also increasing. One way of mitigating a potential attack during a user’s session, would be to use a secure communication protocol to encrypt data in transit between the user and the server on which the sensitive information resides. Two of these communication protocols used are: Secure Sockets Layer (SSL) and Transport Layer Security (TLS).
SSL was first introduced by Netscape back in 1993-1994. The growth of the Internet was rising and so was the need for transport security. Today SSL/TLS is used in almost every conceivable online service. TLS is the protocol that allows digital devices (such as computers and phones) to communicate over the internet securely, without the transmission being vulnerable to an outside audience. The latest version of TLS is TLS1.2.

How did TLS hinder the installation of BizTalk360?

As per any organization’s security policy, they would be installing TLS protocol for more security. But do you think that this protocol would make the BizTalk360 installer to fail? How is that related to BizTalk360? Let’s move on to find the answer.
Let us consider the following setup for BizTalk360. Having a single BizTalk Server & standalone BizTalk360 environment, the setup will look like below.

The BizTalk360 database may be hosted on a separate SQL Server as well. In that scenario, all the servers will have the TLS installed and they communicate with each other through the protocol. In some of the customers’ cases, where they disabled TLS 1.0 and enabled TLS 1.2, they had the difficulty in installing BizTalk360 as the database was not getting created. However, once TLS 1.0 was enabled and TLS1.2 disabled, the installation was successful with the database creation.

The different scenario of the customer

In another customer scenario, they were using the SQL Express Edition for hosting the BizTalk360 database. This is first time we have come across such a case with the SQL Server edition. At first, we suggested them to enable TLS1.0 and disable TLS1.2. But when TLS1.2 was disabled, they faced the error in connecting to the SQL Server as shown below.

We tried different options for enabling the TLS versions, but this did not solve the error at all. Let’s move ahead to know the real cause of the error and how we resolved it.

The WiX toolset and BizTalk360 installer

BizTalk360 installer uses the WiX toolset for the installation package and database creation. It seemed that there was no support for TLS 1.2 in the WiX toolset for the SQL Express edition. Hence it was not able to connect to the SQL Server for the database creation.
The BizTalk360 installer is so robust that each and every error will be displayed, and it won’t allow the installation to proceed in case if it encounters any problem. The installation logs are an added advantage to get the clear picture of the error message. The below picture shows the TLS settings at the customer end.

As a quick step for testing this case, we immediately provisioned a VM with the same configuration as the customer and we were able to reproduce the exact issue. After a complete analysis and research, we found the issue with the WiX toolset. Now that we had identified the issue with the WiX toolset, the update was available for it. With this updated version of the installer,  the installation was successful, and the database was also created.
This was a different experience and of course a good learning for us. We, the support people, now had acquired the background knowledge of the installer as well as the TLS protocols.

Conclusion

New experiences lead to new learnings. As TLS 1.0 has been deprecated, many of our customers who have already moved to TLS 1.2 were not sure if BizTalk360 supports the latest version. Now the installer supports the latest TLS version, which is TLS1.2. This will be updated in our upcoming release version of BizTalk360 v8.9. When the customers upgrade to v8.9, they can have TLS 1.2 enabled which was not the case previously. When they had TLS1.0 disabled, they were not able to install BizTalk360. They had to enable TLS1.0, disable TLS1.2, install BizTalk360 and then once again disable TLS 1.0, which is a cumbersome job.
Now BizTalk360 supports TLS 1.2. Happy monitoring with BizTalk360!!! 😊.

Author: Praveena Jayanarayanan

I am working as Senior Support Engineer at BizTalk360. I always believe in team work leading to success because “We all cannot do everything or solve every issue. ‘It’s impossible’. However, if we each simply do our part, make our own contribution, regardless of how small we may think it is…. together it adds up and great things get accomplished.”

My session about “BizTalk Server: Lessons from the Road” is now available online

My session about “BizTalk Server: Lessons from the Road” is now available online

One more year and one more BizTalk Server session delivered in London: “BizTalk Server: Lessons from the Road“. INTEGRATE 2018 was an amazing conference and for that, we once again need to thank you BizTalk360 team and Saravana Kumar for being able to assemble not only another great event but keeping improve it each year which is not an easy task.

About my session: “BizTalk Server: Lessons from the Road”

I was asked, once again, by the event’s organizers to deliver a session about BizTalk Server, I topic that I love and like to talk but I must confess that this time I was a bit afraid. Because of the huge success of my previous session that I presented last year, it would not be easy to keep me on the same level. But life is full of challenges that we should not be afraid, but rather face them with frontality and confidence, so I decide to deliver a session about “BizTalk Server: Lessons from the Road“.

Abstract: The session will cover small pieces of stories with practical real examples from the field to address certain scenarios/requirements. See real techniques been used is some of the most important features of BizTalk Server, some of them are out-of-the-box capabilities others are custom extensions been made in the platform. Topics include BizTalk migration strategy, content-based routing techniques, Mapping, JSON support, BizTalk administration tips, extending BizTalk out-of-the-box capabilities and many more.

You can download the PowerPoint presentation here: BizTalk Server: Lessons from the Road (slides)

And see the video session online here: BizTalk Server: Lessons from the Road (video)

BizTalk Server: Lessons from the Road

Hope you enjoy! But that is not all, you can also see the rest of the amazing sessions delivered by Microsoft Product group and Microsoft Most Valuable Professionals (MVP’s) at the event also online here: https://www.biztalk360.com/integrate-2018-resources/

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a 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. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

Centralized Advanced Event Log viewer performance improvement for getting better insights about your BizTalk Environment

Centralized Advanced Event Log viewer performance improvement for getting better insights about your BizTalk Environment

In this blog post, I wanted to highlight the performance improvements of advanced event viewer collection logic in our upcoming version 8.9. This change will provide a fine-grained access to enable specific servers for the Event Log collection that are more important to you.

Windows Event Log Monitoring to detect the data breach

What would you do if you were faced with a critical issue in your BizTalk Environment in your enterprise? Think about this for a moment. Do you have an efficient process to respond to the problem? Are you equipped with the right set of tools to mitigate the problem in your environment? Sometimes, despite our best efforts, an issue may happen in unexpected ways and cause significant problems to the business.

In this situation, the Windows Event Log viewer is a handy tool for the BizTalk Administrators/support engineers to monitor their BizTalk environments and to detect any data breach or for any specific exception. At the same time, if the BizTalk servers are deployed in highly available setup and with clustered SQL server architecture, searching the Event Log from all of these servers together is like looking for a needle in a haystack.

Become the God of the Event Logs with BizTalk360 centralized Advanced Event Log viewer capability

Log data can be huge even in small integration business units. Many of the log entries might be routine messages about the BizTalk environment I.e. services and job status, that are mostly of no concern to you, but other messages like suspension of any service instances, system throttling state, state of host instances, etc., that is critical to your day to day business transactions.

However, to search for this logs in all the servers by logging into the physical server and correlate the Event Logs is a tedious process for the BizTalk support engineers /Administrators. This is where lots of their valuable time is wasted.

Who would not be happy if there is a capability to provide access to all the Event Logs across all the configured servers? Therefore, BizTalk360 provides you centralized access to all the Event Logs across all the servers in a rich web-based UI. To know more about the Advanced Event viewer, visit this blog “Why did we build Centralized Advanced Event Viewer for BizTalk Administration?”

No Fear! No Agent required. BizTalk360 provides agentless Event Log collection:

For Event Log collection, BizTalk360’s Advanced Event Viewer does not require a separate agent to be installed on each server. The BizTalk360 Monitoring service collects Event Log and is present as part of the BizTalk360 installation. In this way, BizTalk360 performs Event Log collection task without introducing any additional load to the servers.

Why this performance improvement required?

The Advanced Event Viewer is the most used feature in BizTalk360, where the administrators use this capability for their day to day business operations and monitoring with ease. To do so, there are some settings which need to be configured in BizTalk360. These settings are bound to the BizTalk environment level. In case of highly configured business architecture (Eg: 5 BizTalk Servers and 5 SQL Server with clustered setup), there may be millions of transactions which will happen on a regular basis.

From BizTalk360 V8.6 onwards, we have provided a new capability to monitor the Event Logs of the BizTalk & SQL servers. For this, we are calling the Windows Management Instrumentation service every minute. Because of the restriction with the environment level, all the servers will be polled every minute to collect the Event Log data.

We have received few support tickets where this specific logic is consuming a lot of memory and spikes the CPU performance of their BizTalk & SQL Servers. There are workarounds already suggested to our customers to make use of this capability in the best way, by configuring the sources which are required for them and remove the unnecessary sources. However, there are no options to enable the Event Log collection per individual server. Therefore, we have improved performance of the collection logic in our V8.9 version.

Comprehensive Event Logs collection using the newly introduced configuration method

 In our previous versions, up to V8.8, Event Log collection logic is not segregated based per server. To enable the Event Log collection, it is bound to the BizTalk environment and not on an individual server level. However, the user has control on configuring the sources based on the need.

Below screenshot represents our old screen in the Settings side to enable the Event Log viewer in order to view the logs in BizTalk360.

BizTalk360 Advanced Event viewer Event Log collection performance improvement- Old Screen

We have revisited the collection logic in our new version to improve the performance. Since the configuration is bound to the environment, the user doesn’t have control over the individual servers. In the new version of BizTalk360, users can control the Event Log collection according to individual servers. As an administrator, you know the value of each source which needs most considerations. So, there is an option as well in BizTalk360 to configure the BizTalk & SQL server sources separately.

Using these settings, you can customize and narrow down your Event Log search.

BizTalk360 Advanced Event viewer Event Log collection performance improvement - New_source_segregation_settings_screen

BizTalk360 Advanced Event viewer Event Log collection performance improvement - Server_configuration_screen

Improving the Archiving and Retrieving of Event Logs capability

Event Log retrieval is fairly a cumbersome task for the administrators from all the configured servers individually. BizTalk360 provides to download all the Event Log messages in a single place. Archived Event Logs give a lot of insight into the performance of your BizTalk environment unless all the Event Logs are archived in a central location. This way operators are able to access them at any time.

BizTalk360 Advanced Event viewer Event Log collection performance improvement - Export to Excel capability

Changes in the Event Log Data Monitoring section

Monitoring BizTalk Servers in a connected environment is the prime requirement for organizations – large or small.

The first thing we wanted, was to show the common issues/problems in the blink of an eye when an administrator needs it. The Advanced Event Viewer, in the Operation section of BizTalk360, would show users instantly, details of any Event Log about their BizTalk Environment. To know more about operations of Advanced Event Viewer refer to this article.

The second thing was the exact opposite – if there are some issues that should never happen, but they did in your Biztalk Environment, we would want you to know about it as well – once again, instantly. This is why we have built advanced Data Monitoring for Event Logs.

Previously, irrespective of the server selection, all the configured sources are displayed in the sources drop-down list. Now, based on the server selection, the sources will be collected and listed. This would minimize the turnaround time of creating the data monitor by selecting the required sources easily. Below screenshot refers to the new changes done in our new version of BizTalk360. Using this Data Monitoring capability, BizTalk360 will continuously monitor without any manual intervention and attention.

BizTalk360 Advanced Event viewer Event Log collection performance improvement - Data Monitoring New screen

Conclusion

We are sure, this implementation will highly optimize the BizTalk360 Advanced Event Viewer collection logic. Happy migrating and try BizTalk360!!!

Get started with the free 30 days trial. For any queries/feedback please write to us support@biztalk360.com.

Author: Mekala Ramesh

Test Lead at BizTalk360 – Software Testing Engineer having diverse exposure in various features and application testing with a comprehensive understanding of all aspects of SDLC. Strong knowledge to establish the testing process from the scratch. Love to test the software product to deliver it with good quality. Strongly believes on “Testing goes beyond just executing the test protocol”.

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.