Posts Tagged ‘BizTalk’

Monitoring and Alert Notification capabilities

BizTalk360 allows us to monitor all kinds of resources associated with the different BizTalk Server infrastructure layers in which we can highlight:

Services and Applications

Allows us to monitoring HTTP address or external Web services codes for known errors (e.g. 200, 202, etc.).

BizTalk360-Services-and-Applications

We can monitor the expected state (Started, Stopped, Enlisted, Un-enlisted, Enabled or Disabled) of orchestrations, send ports or receive locations.

BizTalk360-Services-and-Applications-2

BizTalk360 also allow us to monitor processes. Process monitoring is designed to help customers tackle common scenarios, without any custom development, for example:

  • We expect to receive or send a certain amount of messages to or from a given environment. If this rule does not carry out, then there should be an implication in the business and someone needs to be notified.
  • Your trading partner sends you some files via FTP every day. If you receive the file, everything works smoothly, but what happens if there is a problem on your partner end (ex: some firewall patch) and you didn’t receive any messages from them for couple of days.
  • You may want to keep an eye on the sales threshold, for ex: you expect to receive certain volume of messages in your system every business day from your trading partner(s).

BizTalk360-Services-and-Applications-3

Automatic monitoring of failures or violations, which allows us to control how these are reported to us, for example:

  • If it is an intermittent problem? If so wait 10 minutes before sending alerts;
  • Send only 5 alerts instead of 100 in the next 4 hours;
  • If someone fix the problem, we can also configure the system to notify us.

We can define this notifications to be sent by via email or SMS.

BizTalk360-Services-and-Applications-4

BizTalk Server Platform

BizTalk360 allow us to monitoring the state of the Host Instances, SQL Jobs or NT Services. For each of them we can also define the state that is expected, this point for example is extremely useful to monitoring SQL job that comes with BizTalk, since it is not supposed to be all active (MessageBox_Message_Cleanup_BizTalkMsgBoxDb must be deactivated and shall be activated by the job MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb).

BizTalk360-BizTalk-Server-Platform

SQL Server and in particular "MessageBox" database are the heart of the BizTalk Server platform and it’s for this reason that Microsoft SQL Server usually comes as a “black box” when it comes to BizTalk Server, is something that we know exists, but we shouldn’t play around! BizTalk Server brings included a series of SQL jobs to perform its entire management: backups, cleaning and so on, that ensure the prefect functioning of the platform. In reality, and contrary to what many DBA use and do, the only way to ensure a full backup and enabling its trusted restoration is using these SQL Jobs and not other processes usually created for this functionality.

To ensure that all these activities, especially the backups are working properly, the BizTalk administration team or DBA needs to periodically monitor these Jobs. BizTalk360 help us solve all these challenges by providing mechanisms for monitoring and notifications:

BizTalk360-BizTalk-Server-Platform-2

As well a “BizTalk Backup and Disaster Recovery" view panel that displays all the details in a simpler/graphical way.

BizTalk360-BizTalk-Server-Platform-3

BizTalk360 also give us the ability to monitor the Service Instances status, allowing for example to define different levels of warning and/or error for the number of services instances with specific state (Suspended, Active, Scheduled etc) in applications. If the services instances count crosses the threshold a notification will be sent.

BizTalk360-BizTalk-Server-Platform-4

Operation System

The ability to monitor the usage of CPU or memory consumption, allowing us to configure alert levels of error and/or warning on the amount of free memory or CPU consumption. For example send a notification if the free memory falls below the values set for a period of 30 minutes or if the consumption of the CPU is maintained above of the values defined during a period of time.

BizTalk360-Operation-System

And also the ability to monitoring event logs, allowing us to be able to configure various rules based on your requirements to different notifications, for example:

  • Trigger notification if the number of errors associated with BizTalk occurs more than 10 times in the last 30 minutes;
  • You can configure if there are 10 MSI installation events in the last 30 minutes then trigger notification.
  • Or whenever a host instance is started/stopped so many times in last 60 mins

BizTalk360-Operation-System-2

Server Hardware

Ability to monitor hard drives, allowing once again sending notifications if the free space on the disks reaches the defined values.BizTalk360-Server-Hardware

Advanced capabilities for managing permissions

The authorization model included out-of-the-box with BizTalk Server is very limited, you can give someone BizTalk administrators or BizTalk operator’s rights, but that is about it, however this is insufficient and is it’s imperative for organizations to be able to manage access permissions to various resources on Microsoft BizTalk Server platform.

With BizTalk360 this assumption becomes possible, bringing, included in the product, advanced management permissions capabilities, fully customizable, that will allow organizations to define their own authorization requirements.

BizTalk Server is too expensive to be used by only one department of the organization so it’s normal to be shared between departments or project teams. These features (Advanced User Authorization) will help the BizTalk administrations teams to properly define how the project teams or company departments will access to productions environment without the fear that they interfered with other resources that are not theirs. Administrations teams know will now be able, for example, to:

  • Define your own NT roles and dictate how user can access the environment;

BizTalk360-Advanced-capabilities-managing-permissions

BizTalk360-Advanced-capabilities-managing-permissions-2

  • Restrict users/groups to limited applications, give users read-only access or setting up restrictions for support staff to resume or terminate instances;
  • Restricted view for certain users/groups;
  • Restricted access to confidential messages;

Fine grained authorization brings lot of advantages for the enterprise. The foremost reason is, security, by restricting access to the various components, functionalities, applications or BizTalk artifacts. Also on the other hand it makes life easy for application support people, they are hidden away from unnecessary things, which are not related to their task.

In which BizTalk360 differs from other tools?

The BizTalk Server Administration Console is a Microsoft Management Console (MMC) and is the only tool included in the product that have the ability to manage and monitor, of course this is a very useful and powerful tool with many, many features allowing it to be used for just about everything we need to do on the platform:

  • Create, deploy, configure and manage BizTalk applications (Orchestrations, Role Links, Send port groups, Send ports, Receive ports, Receive locations, Policies, Schemas, Maps, Pipelines);
  • Create and configure Parties
  • Configure and manage BizTalk groups (you use the BizTalk Server group to represent a unit of organization – such as an enterprise, department, or hub – that requires a contained Microsoft BizTalk Server implementation.)
  • Add and configure adapters;
  • Create, configure and tracking Host and Host Instances;
  • Add, move or remove Servers that belong to the BizTalk Server group;
  • Managing MessageBox Databases;
  • Configure tracking mechanisms for virtually all artifacts or components in the platform (Orchestrations, Send Ports, Receive Ports, Policies, Schemas, Pipelines and so on);

We can and should use it to diagnose and resolve problems associated with BizTalk applications as well as monitoring partially the layers:

  • Services and Applications
  • And BizTalk Server Platform

But as the name indicates this is a tool with focus on administration and not monitoring, therefore is not ideal to use this tool for monitoring the platform as a whole and also there are several gaps in this tool for the latter to become the tool of choice for monitoring this platform in your organization, such as:

  1. The lack of access restrictions definition to certain resources or operations, e.g.:
    1. Read-only access to a particular Group or User Accounts or restrict them to view and/or manage certain applications; restrict that particular group to view private messages; or even not allow access or view the settings of the infrastructure (Host, Host instances, adapters , …) to the support group.
    2. The lack of auditing processes, example: Who did what: Who stopped a specific port? Who stopped or started a particular Host Instances? Who has suspended this service instance?
    3. The ability to view the topology of their environments in real time.
  2. Another important limitation is that this tool has no web interface, this mean that you need to give remote desktop access to your users in your production environment machines in order to them have access and used this tool, which not all organizations allow! In my personal experience and opinion, almost no organization allows this type of access to their production environments.

We can also monitor BizTalk Server platform as a whole using robust monitoring tools like System Center Operations Manager (SCOM). SCOM is used to monitor the health and performance of everything from servers to individual applications in Microsoft Windows environments, it is a comprehensive IT infrastructure, virtualization, and cloud management platform that allows you to easily and efficiently manage your applications and services across multiple hypervisors as well as across public and private cloud infrastructures to deliver flexible and cost-effective IT services for your business. It provides a unified management toolset for your applications and services.

However use only SCOM to monitor BizTalk platform can become extremely expensive for medium enterprises (or midsize companies) and in my opinion even for some large companies. This because the learning curve to use this tool is high, why? This tool is too complete, comprehensive and complex and for that it requires highly skilled people. Beyond the need for specialized resources it also requires a large investment in hardware and software since SCOM can monitor any Microsoft product, Azure or even third-party products. It would also be a waste of resources to just use SCOM to monitor a BizTalk Server platform.

Another tool that we can use to monitor is BAM Portal. We can monitor messages or business process by using Business Activity Monitoring (BAM). It provides visibility on business processes by tracking process milestones and business data (KPIs) allowing business analysts, developers, information workers and decision makers to monitor and gain insight on the current health and analyze data about in-flight processes they are responsible for. By using BAM, users can get information about business state, trends, and critical conditions… however we cannot use this tool to monitor our environment.

Saravana Kumar used the experience gained over many years as a BizTalk consultant in several clients to create the tool BizTalk360 (http://www.biztalk360.com) that addresses the above issues and many more, filling almost all the gaps left by Microsoft in the area of ​​support and monitoring BizTalk Server in an incredibly simple and intuitive way, thus fulfilling the most common needs of clients and at the same time radically reducing the learning curve required to use this tool compared to tools like SCOM.

BizTalk360-dashboard

There are several features that this tool gives us, however we can highlight the following as its key features:

  • It is a web application (RIA – Rich Internet Application), which unlike the BizTalk Server administration console in which we need access to BizTalk environments, allows us to access from anywhere;
  • Advanced capabilities for managing access permissions to various resources (artefacts) of BizTalk Server;
  • Possibility of conducting audit at all levels, messages and events;
  • Availability of dashboards for monitoring very comprehensive and detailed that cover the most common needs;
  • Monitoring capabilities of the most varied resources and proactive notification;
  • Integration with BAM Portal, Message Box Viewer and other tools like HP Operation Manager Integration;
  • Multi-environment support and provides the capability to configure all BizTalk environments through a single location;
  • Knowledge base Repository;
  • Advanced event viewer and many more.

We can and should use BizTalk360 to monitoring the following layers:

  • Services and Applications
  • BizTalk Server Platform

And partially layers, this tool enables to monitor the most common and basic resources, of:

  • Server Hardware
  • Infra-structure
  • Operation System
Introduction

BizTalk Server in most cases is an important part of the infrastructure of organizations because it allows us to connect different systems within organizations but most important because in most in most scenarios BizTalk Server processes critical data of the organizations themselves. This means that any data that is lost or downtime of the server can have a significant impact on the business that it supports. Like any component or service that are critical inside organizations, it should be administered and monitored by technicians who have the knowledge and experience required, however most system administrators are unfamiliar with this platform, which can hamper the proper functioning of the platform and as a result, the own organization.

The BizTalk Server infrastructure can and will vary from organization to organization, from a single server (standalone server) to a robust platform with BizTalk Server and SQL Server in a cluster environment providing high availability through server/failover clusters and load balancing, and availability through disaster recovery.

The figure below shows a graphical representation of the different layers of the BizTalk Server infrastructure that need to be actively monitored in order that BizTalk Server Administration Team can ensure maximum availability of the platform:

BizTalk-plataform-layers

Failure or error events can occur on multiple servers or even in multiple locations, for example: event viewer, applications logs, and so on.

Due to the complexity in infrastructure behind BizTalk Server platform, is not very efficient and effective when an administrator must manually check each server or application by events that may have occurred. Ideally, the administration team should make use of all monitoring tools at their disposal, whether they are include with the product such as BizTalk Server Administrative console, Event Viewer, HAT or BAM. Whether they are other monitoring tools from Microsoft such as Microsoft System Center Operation Manager (SCOM) or whether they are third party monitoring solutions such as BizTalk360, in order to easily monitor all these events and thereby prevent the occurrence of failures taking preventive measures, diagnose or recover the fault.

These tools should be able to read events from all layers of the infrastructure and help the administration team to take preventive measures, notifying them when a particular incident is about to happen, for example, when the free space of a hard drive is below 10%. Furthermore, they should allow the automation of operations when a specific event occurs, for example, restart a service when the amount of memory used by it exceeds 200MB, thereby preventing incidents or failures, without requiring human intervention.

In this series of posts I intend to demonstrate how the tool BizTalk360 can help administration teams to monitoring BizTalk Server, provide support and automate operations, thereby preventing the occurrence of failures in the platform.

What is BizTalk360?

BizTalk360 (http://www.biztalk360.com/) is a web based (RIA) designed primarily to perform monitoring and support for BizTalk Server environments (Production, Test, Development). It addresses some of the common problems customers face today like governance/auditing, fine grained authentication, remote access and so on. It also makes life easy for people who support production BizTalk systems by providing various dashboard and greater visibility.

Currently the fifth version of this tool is available for download, which shows a steady growth over these three years. BizTalk360 it’s a product developed by Kovai Limited, based in London UK, but everyone knows this tool through its CTO Saravana Kumar, Microsoft BizTalk Server MVP since 2007 and a well-recognized member of the BizTalk community.

Well, I decided to take a few minutes of my vacation to play a little with … BizTalk, renew some knowledge, answer a few emails and maybe try to answer some questions on the forums.

This exercise (or pattern) is actually from a question that I found on the forums: Reg BizTalk Mapping, which I found interesting.

So what’s the best way to map some values from a repeating node into a single node base in some conditions?

Note: you can find all mapping logic of this exercise in the forum, however briefly we have a repeating node “TimeSeries” and based on Path attribute value of “TimeSeries” node we will map on some elements of the output message:

  • If “Path” attribute == “1″ then assign the value of “TimedValue” to “Quantity”
  • If “Path” attribute == “2″ then assign the value of “TimedValue” to “NRJQuantity”
  • If “Path” attribute == “3″ then assign the value of “TimedValue” to “AvgCal”
  • If “Path” attribute == “4″ then assign the value of “TimedValue” to “AvgDens”
First Solution: Using only functoids (without custom XSLT)

To solve this mapping problem using this approach, for each element in the destination schema we need to drag:

  • One Equal functoid and drag a link from the attribute “Path” in the source schema to this functoid, this will be the first input parameter in the functoid
    • And in the second parameter we need to put the number that we want to find, in this case: “1”.
  • Drag a Value Mapping functoid to the grid
    • Drag a link from the Equal functoid to this Value Mapping functoid
    • Drag a link from the “TimedValue” element in the source element to the Value Mapping functoid
  • Drag a link from the Equal functoid for the element in question in the destination schema, in this case “Quantity” element
  • And finally we need to drag a link from the Value Mapping functoid to the respective element in the destination schema, in this case again “Quantity” element as you can see in the picture bellow.

map-values-from-repeating-node-into-single-node-using-conditions-with-functoids

  • We need to repeat the above steps for all the element until we get the following map:

map-values-from-repeating-node-into-single-node-using-conditions-with-functoids-all

This solution is correct and in fact is what’s normally we found in this type of mapping problems however this is not the best option in terms of performance. If we analyze the XSLT regenerated by the BizTalk mapping engine by:

  • Right-click in the map and select the option “Validate Map”
  • In the Output windows, press CRTL key and click on the link of “The file in the output XSLT is stored in the following file”, it will open this file in a new windows
  • Right-click and select “View Source” option

We will see that for each element in the destination schema it will be one for-each element:

<ns0:Req>
  <xsl:for-each select="TimeSeries">
    <xsl:variable name="var:v1" select="userCSharp:LogicalEq(string(@Path) , &quot;1&quot;)" />
    <xsl:if test="$var:v1">
      <xsl:variable name="var:v2" select="string(@Path)" />
      <xsl:variable name="var:v3" select="userCSharp:LogicalEq($var:v2 , &quot;1&quot;)" />
      <xsl:if test="string($var:v3)='true'">
        <xsl:variable name="var:v4" select="TimedValues/TimedValue/text()" />
        <ns0:Quantity>
          <xsl:value-of select="$var:v4" />
        </ns0:Quantity>
      </xsl:if>
    </xsl:if>
  </xsl:for-each>

  <xsl:for-each select="TimeSeries">
    <xsl:variable name="var:v5" select="string(@Path)" />
    <xsl:variable name="var:v6" select="userCSharp:LogicalEq($var:v5 , &quot;2&quot;)" />
    <xsl:if test="$var:v6">
      <xsl:if test="string($var:v6)='true'">
        <xsl:variable name="var:v7" select="TimedValues/TimedValue/text()" />
        <ns0:NRJQuantity>
          <xsl:value-of select="$var:v7" />
        </ns0:NRJQuantity>
      </xsl:if>
    </xsl:if>
  </xsl:for-each>
....

This means that if we have 50 occurrences of “TimeSeries” node, we will have 50 iterations for each element that we want to map in the destination schema… so this approach may be easy to implement and somewhat acceptable in small messages is extremely disadvantageous for large message.

Limitations of this approach:

  • Lack of performance
Second Solution: Using Inline XSLT

In this second approach what we will do is take the XSLT code generated by the compiler and optimize it by removing all unnecessary cycles and put this code in a Scripting functoid.

To accomplish this, we need to:

  • Drag Scripting functoid to the map grid
    • In the scripting type select “Inline XSLT” option
    • In the Inline script put the following code:
<xsl:for-each select="TimeSeries">
  <xsl:if test="string(@Path) = '1' ">
    <Quantity>
      <xsl:value-of select="TimedValues/TimedValue/text()" />
    </Quantity>
  </xsl:if>
  <xsl:if test="string(@Path) = '2' ">
    <NRJQuantity>
      <xsl:value-of select="TimedValues/TimedValue/text()" />
    </NRJQuantity>
  </xsl:if>
  <xsl:if test="string(@Path) = '3' ">
    <AvgCal>
      <xsl:value-of select="TimedValues/TimedValue/text()" />
    </AvgCal>
  </xsl:if>
  <xsl:if test="string(@Path) = '4' ">
    <AvgDens>
      <xsl:value-of select="TimedValues/TimedValue/text()" />
    </AvgDens>
  </xsl:if>
</xsl:for-each>
  • Finally drag a link from the Scripting functoid to one element in the destination schema, for example “NRJQuantity”

map-values-from-repeating-node-into-single-node-using-conditions-with-scripting

Limitations of this approach:

  • May have some lack of performance if we work with large message because some unnecessary iterations in the cycle, however it is far more efficient than the first solution.
  • Some warnings saying that some required field has no incoming link.
  • Because we use scripting functoids we cannot read the entire map visually. We need to open the functoids and read, mainly, the XSLT code.
Third Solution: Using Inline XSLT along with XPath queries

After analyzing all the advantages and disadvantages, I decided that I could optimize even more the XSLT script in order to have a better performance but to do this I would have to use a different approach than the one that was used by the BizTalk mapper engine, and for me this is the best approach to accomplish this type of mapping problem, because basically solves all limitations of previous solutions: it’s easy to create (only need basic knowledge of XSLT and XPath) and don’t have performance problems.

To accomplish this, we need to:

  • Replace the code of the Scripting functoid, existing in the previous solution, by:
<xsl:choose>
  <xsl:when test="count(//TimeSeries[@Path='1']) > 0">
    <Quantity>
      <xsl:value-of select="//TimeSeries[@Path='1']/TimedValues/TimedValue/text()" />
    </Quantity>
  </xsl:when>
</xsl:choose>
<xsl:choose>
  <xsl:when test="count(//TimeSeries[@Path='2']) > 0">
    <NRJQuantity>
      <xsl:value-of select="//TimeSeries[@Path='2']/TimedValues/TimedValue/text()" />
    </NRJQuantity>
  </xsl:when>
</xsl:choose>
<xsl:choose>
  <xsl:when test="count(//TimeSeries[@Path='3']) > 0">
    <AvgCal>
      <xsl:value-of select="//TimeSeries[@Path='3']/TimedValues/TimedValue/text()" />
    </AvgCal>
  </xsl:when>
</xsl:choose>
<xsl:choose>
  <xsl:when test="count(//TimeSeries[@Path='4']) > 0">
    <AvgDens>
      <xsl:value-of select="//TimeSeries[@Path='4']/TimedValues/TimedValue/text()" />
    </AvgDens>
  </xsl:when>
</xsl:choose>

map-values-from-repeating-node-into-single-node-using-conditions-with-scripting-2

Limitations of this approach:

  • Because we use scripting functoids we cannot read the entire map visually. We need to open the functoids and read, mainly, the XSLT code.
  • Need basic knowledge of XSLT and XPath
  • Some warnings saying that some required field has no incoming link.
Fourth Solution: Using Inline XSLT along with XPath queries (avoiding warnings)

So to avoid warnings saying that some required field has no incoming link we must split the XSLT code that we use in the last solution (Third Solution) in different blocks for each element in the destination schema

To accomplish this, we need to:

  • Drag four Scripting functoid to the map grid and drag a link from each Scripting functoid to each element in the destination schema
  • For each Scripting functoid:
    • In the scripting type select “Inline XSLT” option
    • In the Inline script put the code that corresponding to the element in the destination element, for example in the first:
<xsl:choose>
  <xsl:when test="count(//TimeSeries[@Path='1']) > 0">
    <Quantity>
      <xsl:value-of select="//TimeSeries[@Path='1']/TimedValues/TimedValue/text()" />
    </Quantity>
  </xsl:when>
</xsl:choose>

map-values-from-repeating-node-into-single-node-using-conditions-with-scripting-3

Limitations of this approach:

  • Because we use scripting functoids we cannot read the entire map visually. We need to open the functoids and read, mainly, the XSLT code.
  • Need basic knowledge of XSLT and XPath

The sample code is available for download in Code Gallery:.

How to map values from a repeating node into a single node using conditions (142.6 KB)
Microsoft Code Gallery

Some people say that according to Mayan calendar, the Mayan fifth world finished in 1987 and the sixth world starts in 2012, so we are currently “between worlds” and this time is called the “Apocalypse” or revealing. Mayans also say that by in 2012 (again according to some peoples):

  • We will have gone beyond technology as we know it.
  • Planet Earth and the Solar System will come into galactic synchronization with the rest of the Universe.
  • Our DNA will be “upgraded” (reprogrammed) from the center of our galaxy. (Hunab Ku)

Well I don’t know anything about solar system, Mayan calendar or if our DNA will be upgraded…

What I know is that BizTalk Server DNA is being upgraded and that today is a special day… today is the twelfth anniversary of the product… Happy 12th birthday BizTalk Server!

Biztalk-Server-2000-logo.gif

BizTalk Server 2000 was released in 12/12/2000 (see Gijs in ‘t Veld collection item here) and like I said its DNA is being upgrade to BizTalk Server 2013 (available in beta version), also take a look to BizTalk Server, all logos over time …

Congratulations BizTalk Server Team!

Sometimes people want to start learning BizTalk and don’t know where to start and it is very normal to see a lot of people asking this question, which leads me to always be looking for this information.

To be honest, it is more usual people asking “How can I learn to be a BizTalk Developer” than “How can I learn to be a BizTalk Administrator”, however BizTalk Administrator plays a very important role in BizTalk environments.

Therefore to have a quick response to this type of request and also to act as reference for administrators and developers, I decided to create two articles on the Microsoft TechNet Wiki that will act as Quick reference to all available resources:

Training-Resources-BizTalk-Developer

Training-Resources-BizTalk-Administrators

I hope you find this information useful and I challenge (invite) all BizTalk Administrator and Developers to help improve this article with new content: whitepaper’s, Microsoft documentation, tutorials, scripts, Webcast and so on…

I decided to put the name of the error in the post title … but this post also could be called “Why you shouldn’t delete Active Directory accounts (if you don’t know where it is being used!)”… but before I tell you why, let me try to explain the problem and put some context on it.

Last week I had a funny surprised when I tried to access my BAM Portal in my BizTalk Server 2006 environment

An unspecified error has occured.
Use the navigation bar on the left to access Business Activity Monitoring views.
If the problem persist, contact you System Administrator.

BAM-portal-error

I just love this type of errors! Because I’m also one of the System Administrator, meaning that I was f*$#%& Sorriso.

My first reaction is that it could have been some connectivity problem, and as the error indicates, I tried to navigate the remaining views to see if the problem remained… and surprisingly this problem occurred only in certain views!

BAM-portal

PROBLEM

So at this point I knew it was happening some problem. Of course the first thing we should do is to check the Event Viewer to see if we can find more details about the error and I found three errors related between themselves and associated with BAM:

BAM-Portal-Errors-Event-Viewer

Starting from below:

  • The first error was:

Current User: DOMAIN\sandro
EXCEPTION:
Microsoft.BizTalk.Bam.Management.BamManagerException: Failed to list permissions for BAM view. —> System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.
at System.Data.SqlClient.SqlBuffer.get_String()
at System.Data.SqlClient.SqlDataReader.GetString(Int32 i)
at Microsoft.BizTalk.Bam.Management.SecurityModule.ListViewPermissions(String viewName, String& dboUsername)
— End of inner exception stack trace —
at Microsoft.BizTalk.Bam.Management.SecurityModule.ListViewPermissions(String viewName, String& dboUsername)
at Microsoft.BizTalk.Bam.WebServices.SecurityHelper.VerifyViewPermissions(String viewName, IPrincipal user, BamManager bamManager, Boolean throwIfNoPermissions)
at Microsoft.BizTalk.Bam.WebServices.SecurityHelper.VerifyViewPermissions(String viewName, IPrincipal user, BamManager bamManager)
at Microsoft.BizTalk.Bam.WebServices.Management.BamManagementService.GetViewDetailsAsXml(String viewName)

Observation: Impossible, I’m BizTalk Administrator but most important my user is the owner of this views!!!

  • The second error was:

Current User: DOMAIN\sandro
EXCEPTION: System.Web.Services.Protocols.SoapException: Internal Server Error.

Observation: says absolutely nothing … trash!

  • And finally the third error was:

(BAMPortal.PortalApplication) Void LogAllErrors(System.Exception[]): System.Web.HttpException: Error executing child request for /BAM/Pages/Search.aspx. —> System.Web.HttpUnhandledException: Exception of type ‘System.Web.HttpUnhandledException’ was thrown. —> System.Web.Services.Protocols.SoapException: System.Web.Services.Protocols.SoapException: Internal Server Error.
at Microsoft.BizTalk.Bam.WebServices.Management.BamManagementService.GetViewDetailsAsXml(String viewName)
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at Microsoft.BizTalk.Bam.WebServices.ManagementService.BamManagementService.GetViewDetailsAsXml(String viewName)
at Microsoft.BizTalk.Bam.Portal.DataAccess.BamDefinitionCache.FetchViewDefinition(String viewName)
at Microsoft.BizTalk.Bam.Portal.DataAccess.BamDefinitionCache.GetBamDefinition(String viewName)
at Microsoft.BizTalk.Bam.Portal.DataAccess.Activity.BuildColumnsCollection()
at Microsoft.BizTalk.Bam.Portal.DataAccess.Activity.EnsureColumnsCollection()
at Microsoft.BizTalk.Bam.Portal.DataAccess.Activity.ColumnsOfType(ColumnTypes type)
at Microsoft.BizTalk.Bam.Portal.DataAccess.Activity.EnsureInstanceColumns()
at Microsoft.BizTalk.Bam.Portal.DataAccess.Activity.get_InstanceColumns()
at BAMPortal.ColumnsChooser_ascx.GetColumns()
at BAMPortal.ColumnsChooser_ascx.GetAvailableColumns()
at BAMPortal.ColumnsChooser_ascx.ReconcileColumns()
at BAMPortal.ColumnsChooser_ascx.OnLoad(EventArgs e)
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
— End of inner exception stack trace —
at System.Web.UI.Page.HandleError(Exception e)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest()
at System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context)
at System.Web.UI.Page.ProcessRequest(HttpContext context)
at ASP.pages_search_aspx.ProcessRequest(HttpContext context)
at System.Web.HttpServerUtility.ExecuteInternal(IHttpHandler handler, TextWriter writer, Boolean preserveForm, Boolean setPreviousPage, VirtualPath path, VirtualPath filePath, String physPath, Exception error, String queryStringOverride)
— End of inner exception stack trace —
at System.Web.HttpServerUtility.ExecuteInternal(IHttpHandler handler, TextWriter writer, Boolean preserveForm, Boolean setPreviousPage, VirtualPath path, VirtualPath filePath, String physPath, Exception error, String queryStringOverride)
at System.Web.HttpServerUtility.Execute(String path, TextWriter writer, Boolean preserveForm)
at System.Web.HttpServerUtility.Transfer(String path, Boolean preserveForm)
at System.Web.HttpServerUtility.Transfer(String path)
at BAMPortal.navbar_ascx.TreeViewNav_NodeClicked(Object sender, TreeNodeEventArgs eventArgs)
at Microsoft.BizTalk.Bam.Portal.ClickableTreeView.OnTreeNodeClicked(TreeNode node)
at Microsoft.BizTalk.Bam.Portal.ClickableTreeView.RaisePostBackEvent(String eventArgument)
at System.Web.UI.WebControls.TreeView.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest()
at System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context)
at System.Web.UI.Page.ProcessRequest(HttpContext context)
at ASP.pages_view_aspx.ProcessRequest(HttpContext context)
at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

Observation: much information … yet without giving me many tips on the problem that was happening.

Actually the first error is what brings us a better sense of the error… it’s a permission problem to access the view… but why? and what was really causing this problem?

CAUSE
  • BAM Management Utility (BM.exe) doesn’t provide the capability to grant group permissions to BAM views instead you need to use user’s accounts. So these issues may occur if the user account which was granted permission to BAM objects are deleted from Active Directory or from Local Computers.

When you access to a certain view in BAM Portal the services invoked by the Portal will try to check the permission for ALL the users associated to this specific view and not only my user, so if a user was deleted from AD or from the local computer without first removing it from the view, the services will fail while attempting to map the account name with Security ID and you will get with this annoying problem: “Data is Null. This method or property cannot be called on Null values.”

You may experience any one or more of the following symptoms:

  • When you access to certain views in the BAM Portal
  • When you try to execute any kind of operation using BM.exe tool against certain views, like “bm.exe get-accounts”; “bm.exe remove-account” or “bm.exe remove-view”
SOLUTION
  • You have to manually delete those user accounts from SQL Server.
How can I really solved this problem?

So now I know the problem, the cause and the solution… but is it that simple?
… NO of course!

First problem: How can I really know with account(s) is causing the problem?

You can have many users in your organization associated with the view and may have passed months or years since the last time we associate the users account to this view… so it is really a problem to find with user is causing the problem.

  • Option 1: you can ask!
    • I know that if I ask who was deleting accounts in the AD or which accounts have been deleted, I will get the typical response… no one or no account has been deleted!!! Don’t go there is an endless road.
  • Option 2: You can use BM.exe!
    • Unfortunately we also can’t use BM tool to ask with account have permission to this view (bm.exe get-accounts), we get the following error:
      • ERROR: Failed to list permissions for BAM view.
        Data is Null. This method or property cannot be called on Null values.
  • Option 3: See in the documentation
    • Another endless road Sorriso. This type of documentation should exist, but few companies actually have them!

I finally decided to try to make a query directly in the BAM Primary Import database (BAMPrimaryImport) to attempt to get a list of all the account that were associated with this view in order to validate with my system administrators if they all existed in AD, this was the result:

WITH Query AS (
SELECT
    [UserName] = CASE princ.[type]
                    WHEN 'S' THEN princ.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE princ.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END,
    [DatabaseUserName] = princ.[name],
    [Role] = null,
    [PermissionType] = perm.[permission_name],
    [PermissionState] = perm.[state_desc],
    [ObjectType] = obj.type_desc,--perm.[class_desc],
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM
    --database user
    sys.database_principals princ
LEFT JOIN
    --Login accounts
    sys.login_token ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col ON col.[object_id] = perm.major_id
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE
    princ.[type] in ('S','U')
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT
    [UserName] = CASE memberprinc.[type]
                    WHEN 'S' THEN memberprinc.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE memberprinc.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END,
    [DatabaseUserName] = memberprinc.[name],
    [Role] = roleprinc.[name],
    [PermissionType] = perm.[permission_name],
    [PermissionState] = perm.[state_desc],
    [ObjectType] = obj.type_desc,--perm.[class_desc],
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM
    --Role/member associations
    sys.database_role_members members
JOIN
    --Roles
    sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
JOIN
    --Role members (database users)
    sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
LEFT JOIN
    --Login accounts
    sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]
LEFT JOIN
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col.[object_id] = perm.major_id
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT
    [UserName] = '{All Users}',
    [UserType] = '{All Users}',
    [DatabaseUserName] = '{All Users}',
    [Role] = roleprinc.[name],
    [PermissionType] = perm.[permission_name],
    [PermissionState] = perm.[state_desc],
    [ObjectType] = obj.type_desc,--perm.[class_desc],
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM
    --Roles
    sys.database_principals roleprinc
LEFT JOIN
    --Role permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col.[object_id] = perm.major_id
                    AND col.[column_id] = perm.[minor_id]
JOIN
    --All objects
    sys.objects obj ON obj.[object_id] = perm.[major_id]
WHERE
    --Only roles
    roleprinc.[type] = 'R' AND
    --Only public role
    roleprinc.[name] = 'public' AND
    --Only objects of ours, not the MS objects
    obj.is_ms_shipped = 0
)
SELECT * From  Query
WHERE Role like '%name_of_the_view%' AND ObjectType like 'View'

BAM-query-result

It may not be sophisticated or the best way but it served my purposes and was much easier and effective than be looking one by one in the SQL Server management Studio.

Now that I had a list of users, it was easy to validate with the system administrators which account had been deleted.

After so much effort and work trying to find the user that was causing the problem I found a simple and more effective solution to this problem in this post on MSDN Blogs: BizTalk Error: BAM Management Utility Errors, using this query:

USE BAMPrimaryImport
GO
Select Name,SID,SUser_SName(SID) as UserAccount from sysusers
WHERE ISLogin = 1 AND issqluser = 0 AND isntuser = 1

In the result for the DB roles, the third column for Windows users (and groups) should display the User Name (or group name) and can’t be null.

So if you execute the above query it will give the user(s) account(s) which was deleted from Domain Controller or Local Computer.

Second problem: How can I manually delete the account?

Yep … this question may seem a bit ridiculous … but the truth is, manually delete the account was not so simple after all… at least for me it wasn’t.

You can’t just delete that account from SQL Server. To do that you need to follow the steps:

  • Go to SQL Management Studio, expand the BAMPrimaryImport Database
  • Go to Security –> Roles –> Database Roles –> BAM_ManagementWS
  • Right click BAM_ManagementWS and go to properties option which will open a new window (Database Role Properties – BAM_ManagementWS)

BAM-ManagementWS

  • In that window go to Securables, choose the NT User account which needs to be deleted.

BAM-ManagementWS-properties-Securables

  • In the bottom portion you will see that NT User Account having “View Definition” Permission. Uncheck that option then click OK.
  • Now you should able to delete the user from Security –> User –> <NT User Account>

BAM-User-delete

However if you try to delete the user without following these steps, like I did :), you get stuck in another huge problem:

TITLE: Microsoft SQL Server Management Studio
——————————
Drop failed for User ‘domain\user’.  (Microsoft.SqlServer.Smo)
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
The database principal has granted or denied permissions to objects in the database and cannot be dropped. (Microsoft SQL Server, Error: 15284)

So we need now to manual revoke the Grant access to BAM_ManagementWS by execute the following query:

REVOKE VIEW DEFINITION ON USER::[domain\user] TO [BAM_ManagementWS] AS [domain\user]
GO
Security Considerations for the BAM Portal

Using the principle of least privilege, user accounts should have restrictive permissions to perform routine tasks in the BAM portal. Keep the following points in mind as you set up your user accounts for BAM to balance security with appropriate access for users.

User accounts

User accounts with minimum permissions are not able to use the BAM portal distributed navigation feature. To be able to use this feature, these accounts must have sufficient permissions to allow access to the Web services on the remote computer as well as on the local computer.

User accounts for the BAM Web services must have permissions to access all referenced databases and must be a member of the BAM_ManagementWS role in the referenced databases.

For the following user types, you should be aware of these considerations:

  • Domain Users: These users must have access permissions on remote computers that host BAM Primary Import databases that are being accessed.
  • Local User: Users who are assigned this role cannot use distributed navigation.
Administrator accounts

Administrators must be members of the securityadmin or sysadmin groups to grant permissions to domain users.

To run the BAM Management utility, you must be at least a database operator for the BAM databases.

Final Notes

I would like to thank Nino Crudele for the help that he gave me to solve this problem and to my coworker and friend José Barbosa who helped me creating these SQL scripts and as result solving the problem.

And a final note for all system administrator… please don’t delete Active Directory accounts… instead disable them!!

Finally I had some free time available to be able to update and fix some minor bugs which existed in the latest version of this adapter and already had been reported by community members.

I have to give a special thanks to maakku that reported a problem related to the Daily schedule properties… and the respective solution!!

So today a published a minor release of BizTalk Scheduled Task Adapter is available for download on CodePlex: BizTalk Scheduled Task Adapter v4.0

NOTE: the adapter is still in version 4.0, I have not modified the version. As I said earlier this is a minor release to solved small bugs.

codeplex-BizTalk-Scheduled-Task-Adapter-page

List of improvements in this minor release:

  • Daily scheduler review. Solved a small bug on Daily Properties: settings were not saved correctly when configuring daily schedule in admin console (see more here)
  • Support for 32 and 64 bit Host Instances.
  • Fix the version number of the adapter that was being recorded incorrectly as 3.0.0. After installing the adapter, in Programs and Features the version for the BizTalk Scheduled Task Adapter it was 3.0.0.

Note: The adapter was tested with success running in 32 and 64 bit Host Instances!

After conducting this event across various major European cities: Amsterdam (Netherland), Milan (Italy), and Stavanger (Norway) since Feb 2011, it is really exciting to announce that the next event will take place in London – England on January 16, 2013!

What is BizTalk Innovation Day/Event?

BizTalk Innovation Day (BID) or sometimes called BizTalk Innovation Event is a one-day event (sometimes we are lucky and we are able to do a two days event, like the event in Norway), that has been conducted since February 2011, focused purely on Microsoft BizTalk Server related topics. There are normally 5-6 speakers most of them are Microsoft Integration MVP’s (previously Microsoft BizTalk Server MVP’s). This type of events are normally free or with small fees to prevent that the event is not filled up quickly by people who don’t turn up.

IMPORTANTE NOTE: BizTalk Innovation Day was renamed to BizTalk Summit 2013, why? This event will be supported by BizTalk product group which will bring undoubtedly additional value to this event!

BizTalk Summit 2013 – London

The BizTalk Crew (Steef-Jan Wiggers, Nino Crudele, Tord Glad Nordahl, our host Saravana Kumar and me) are the speakers of this great event that will talk and show about many new things that are is happening in the Microsoft Integration stack: BizTalk Server 2013 (Windows Server 2012 and SQL server 2012) and BizTalk as PaaS.

I will do a presentation about Azure Service Bus EAI/EDI features. This will be probably the last time I’ll talk about this topic, or at least in the manner in which it is currently.

BizTalk-Summit-2013-London

This will be a one day event dedicated to BizTalk Server, especially on the new version of the product: BizTalk Server 2013 (still in Beta version) and all-around of Integration world (Integration capabilities on Windows Azure). So we invite you all to join us next 16th January, 2013 in London, England, where you can count with the following agenda:

09:30: Registration;

10:30: BizTalk 2013: The new cloud related adapters
by Steef-Jan Wiggers [Microsoft Integration MVP]

BizTalk 2013 will feature some new adapters, the WCF-BasicHttpRelay, WCF-NetTcpRelay, SB-Messaging and WCF-WebHttp. The first three have capabilities to configure connectivity with the Windows Azure Service Bus. This will enable you to create solutions that will enable communication between systems and applications across network boundaries. In this talk you will see the full potential of these adapters in various scenarios.

11:15: Break;

11:30: Improve your operations using BizTalk360
by Saravana Kumar [Microsoft Integration MVP]

BizTalk360 addresses some of the common problems organizations face today while managing a BizTalk infrastructure for day-to-day operations. In this session we’ll cover some of the key capabilities of BizTalk360, like advanced authorization, monitoring, throttling analyser etc. and see how Microsoft BizTalk Server customers can take advantage of the investments we have made in BizTalk360.

12:15: Lunch;

13:30: Introduction to the Azure Service Bus EAI/EDI features
by Sandro Pereira [Microsoft Integration MVP]

Historically, both EAI and EDI has been performed through BizTalk. With this preview we will give an early glimpse into how Microsoft envisions these types of integration scenarios being addressed in a Platform as a Service (PaaS) based environment.
In this session we’ll see EAI (Enterprise Application Integration) and EDI (Electronic Data Interchange) functionalities like: how to manage EDI message exchanges and trading partners with the Azure EDI Portal; how to support Flat files; edit Schemas and transformation maps

14:15: Break;

14:30: Windows Server 2012 and BizTalk Server 2013
by Tord Glad Nordahl [BizTalk Administration Expert]

For BizTalk Administrators and Developers it’s important to stay ahead of new releases and be aware of what’s happening. BizTalk Server 2013 will be out soon and the new improved features in SQL and windows Server 2012 is important to be aware of. There have been major updates in Failover clustering, DNS, Group Policy and Hyper-V and Networking. Tord will guide you through the updates and features so you are ready to initiate and start using BizTalk Server 2013.

15:15: Break;

15:30: BizTalk assessment and architecture review
by Nino Crudele [Microsoft Integration MVP]

Making a good BizTalk assessment and architecture review is a not a simple thing. BizTalk Server needs many different technologies in order to run.
There are many aspects to be considered and many things to inspect in order to make a complete BizTalk architecture review.

16:15: Break;

16:30: Q & A – Panel with the experts
Charles Young [Microsoft Integration MVP] and Jon Fancey [Microsoft Integration MVP] will be members of Q&A panellists

17:30: Closing notes;

17:30: Drinks and Networking;

The event only accommodate a limited number of attendees due to logistical constraints. So if you are interested please go ahead and confirm your seat. There is a £20 charge for the event, just to make sure the event is not filled up quickly by people who don’t turn up.

You can find all detail about this event here.

See you all there in January and thanks to Saravana, BizTalk360 and Microsoft UK for this great opportunity… and of course my company DevScope for supporting me in this kind of events.

soldout-red

Unfortunately the event is fully sold out, almost 1 month before the event. Waiting list is opened, if you are interested.

Next stage will be Porto, Portugal (March, 2013)