BAM error: Failed to list permissions for BAM view – System.Data.SqlTypes.SqlNullValueException: Data is Null.

I just encountered an error using the BAM views. None of the BAM views could be opened and I got following error:

An unspecified error has occurred.

Use the navigation bar on the left to access Business Activity Monitoring views.
If the problem persists, contact your System Administrator.

BamError

Unfortunately, this error doesn’t give much information on what the problem exactly is…
So of course, the first thing I did next was to check the event viewer. This gave me 3 error messages.

Current User: STG\edncnextEXCEPTION:System.Web.Services.Protocols.SoapException: Internal Server Error.

and

(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_error_aspx.ProcessRequest(HttpContext context)
   at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
   at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

Both of these errors doesn’t help out at all.

A third and final error message I found in the event viewer was:

Current User: Domain\edncnext

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)

This message doesn’t exactly give a lot of information, but it was the only one with some info in what the underlying problem should be. It had something to do with the permissions on the BAM view.I was however certain that my account had the necessary rights on all BAM views.

Cause

After some investigating I found that the problem was that an account was removed from Active Directory, but still had some BAM permissions defined on it.

Solution

First of all you’ll need to determine what user defined in the SQL BAM tables that was deleted in Active Directory.
When you are aware which account has recently been deleted from Active directory this would be easy… however in my case I wasn’t aware that there were any accounts removed from active directory.
In this case you can use a simple query to find the missing account, as explained in this blog post on msdn…

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

this will list all DB roles and the third column displays the windows users (and groups) with the User name (or group name). This third column should not contain any null values.
Where the value null appears, should be the account(s) that were deleted in Active directory.

You can use a command prompt to check the user account with this command:

net user UserName /domain

this will result in an error as shown below.

Command

So this clearly is the account that was deleted from Active Directory.

Now then… how to remove this account from BAM?

In the msdn blog post I mentioned earlier are 2 methods of manually deleting the user account.
There is a much simpler way to achieve this by using following SQL query

REVOKE VIEW DEFINITION ON USER::[domain\user] TO [BAM_ManagementWS] AS [domain\user]
GO

this should be enough to get your BAM views working again!

A special thanks to the Sandro Pereira blog post on this issue, which helped me to solve the problem quite quickly.

BTS2013 features explained: Dependency tracking

A while ago we started a series on explaining some of the new features that were introduced in BizTalk 2013 in more detail. We’ve already discussed the XslCompiledTransform API and the Improvements in dynamic send ports. A complete list of the new features in BizTalk 2013 can be found in our post when the product launched.
To continue the series of new features, we would like to digg a little deeper in the announced feature ‘Dependency tracking’… what does this mean?

The dependency tracking is a newly added feature in BizTalk 2013. The dependencies between artifacts can now be viewed and navigated in the Admin console, but what exactly will be shown and how does it work?

BizTalk artifact dependencies

First of, I’ll like to give a bit more details on dependencies in BizTalk. You’ll probably all know that a typical BizTalk Server application involves various artifacts such as orchestrations, send ports, receive locations, pipelines, schemas, maps, etc. . And also that all of these artifacts can have dependencies on each other. There is a useful post on msdn explaining the dependencies between these BizTalk artifacts and listing the dependencies of BizTalk artifacts as in the below table.

DependencyTable
As the table suggests, there are two modes of dependencies.

  • Uses (Using) – An artifact uses another artifact, for example, a send port uses a pipeline.
  • Used By (Used by) – An artifact is used by another artifact, for example, a send port is used by an orchestration.

As a consequence of these dependencies, there is a hierarchy of the BizTalk artifacts that needs to be followed on re-deploying these artifacts. And you’ll need to know which dependent artifacts need to be stopped or re-deployed.

Dependency tracking pane in BizTalk 2013

Now… as of BizTalk 2013, the dependency information of these artifacts is available in the BizTalk Server Administration console. It will display both kinds of dependencies (when artifacts use another artifact as well as if the artifact is used by another artifact) in a brand new dependency tracking pane.

How to view the dependency tracking pane?

  1. In the BizTalk admin console, go to the artifact of which you wish to have the dependencies shown.
  2. Now right-click on the artifact and choose View dependencies.ViewDependencies
  3. NOw you can see that the dependency statistics pane on the bottom will be filled. It will show both kinds of dependencies. Under Used by it will show the number of artifacts that use the selected artifact. While under Using the number of artifacts that use the selected artifact will be shown.
    DependencyStatisticsPane
  4. Clicking on the number of artifacts will bring up the list of the dependent artifacts. In the shown list, you again can bring up the dependencies and click through to any level of the depency tree you want. On top, you’ll see a trail of bread crumbs to show the level of depency you are viewing.DependencyTree

Note

Note however that the shown dependencies are only the directly dependent artifacts. So for example if a send port is part of a send port group, it will be show the dependent send port group. But if the send port group is again dependend on an orchestration, the dependent orchestration will only be shown on the dependencies of the send port group. So in the dependencies of the send port, you will not find the dependend orchestration directly.
You will need to go through the dependency tree using the above explained trail of bread crumbs to view all of its dependencies.

BTS2013 features explained: Improvements in dynamic send ports

In a previous post, I started a series on explaining some of the new features of BizTalk 2013 in more detail. We started with a more detailed explanation on why BizTalk 2013 will be using the XslCompiledTransform API. You can find a list of the new features that BizTalk 2013 introduces on our earlier post.
In this next post in the serie, I would like to explain the announced ‘Improvements in dynamic send ports’… What has been changed in this new version of BizTalk?

BizTalk 2010 (& earlier)

Dynamic send ports where introduced already in an earlier version of BizTalk. However, unlike a static send port, you didn’t have the possibility to choose a BizTalk host that the ports send handler would run on. The dynamic send port will use the default send handler that is configured for the adapter that is being used. Meaning we are tied to using the same default Host for each dynamic port that uses the same adapter, which could cause separation of concern issues with bigger systems.

DynSendBTS2010

Or in other words… when you wish to run a dynamic send port using a different host, you are obliged to change the default send handler of the specific adapter. But keep in mind this will effect all dynamic send ports that are using this adapter as well.

DefaultSendHandler

BizTalk 2013

BizTalk 2013 now has the ability to assign a host handler per adapter. This means you now have the possibility to configure a host to be used per adapter on a dynamic send port…  providing more control on how your workload gets executed with dynamic send ports. This change will bring better scales and performance for dynamic send ports.

DynSend2013

When adding a dynamic send port, you see there is a whole new Transport section in the General tab of the send port properties. By clicking on the Configure-button of the transport section, you’ll be able to choose which host must be configured as send handler for each available adapter.

DynSendHostConfig

Note however that the hosts that can be assigned to an adapter are the configured send handlers of the adapter. So you need to make sure that there is a send handler defined on the adapter configured to use your desired host. The chosen host no longer needs to be the default send handler defined for the adapter.

AdapterSendHandlers

First Cumulative Update for BizTalk 2013… available through Microsoft Update!

The Microsoft Team has just announced the release of the very first Cumulative Update For BizTalk 2013.

But an even more surprising news is that it is available using Microsoft Update!… This is the very first time a BizTalk Update can be obtained with Microsoft Update, as before it had to be done by downloading it manually through the hotfix download. An amazing improvement if you ask me, making it a whole lot easier for administrators.

The update will still be available through the hotfix download though.

The Content of the Cumulative Update

The very first CU for BizTalk 2013 brings 3 fixes:

  • User cannot perform certain database-related operations in BizTalk Server 2013
  • BAM tools cannot be configured in a multi-node BizTalk Server 2013 environment
  • The vertical scroll bar on the target schema does not work correctly when you use Visual Studio to design a BizTalk Server 2013 map

How to install using Microsoft Update

The following steps are applicable if you have Microsoft Update enabled in the machine where BizTalk Server is installed.

  1. Log into a server where BizTalk Server is already installed, and Microsoft Update is enabled
  2. Check for windows update
  3. Notice available fixes for BizTalk Server under optional updates
  4. Select the update and install

(Perform the above operations in each of the BizTalk nodes)

The original announcement can be found here.

BTS2013 features explained: XslCompiledTransform

In a previous post we announced the release of the new BizTalk 2013, listing all of the new features it will bring. In the upcoming posts I would like to explain in more detail some of these features, and the consequences it will bring with it.
First of all, one of the changes introduced in BizTalk 2013 is the use of the enhanced XslCompiledTransform API, instead of using the XslTransform API that is used in older BizTalk versions. This change would provide significant improvements in mapping engine performance for complex maps. Sounds great, but what is the difference between both?

First of all, I would like to note that the XslTransform API has become obsolete and has been replaced by XslCompiledTransform.

The new XslCompiledTransform API will boost the performance for complex maps. Although I would like to make a side note about this one… Although the overall performance of the XslCompiledTransform class is better than the XslTransform class, the Load method of the XslCompiledTransform class might perform more slowly than the Load method of the XslTransform class the first time it is called on a transformation. This is because the XSLT file must be compiled before it is loaded. For more information, see the following blog post: XslCompiledTransform Slower than XslTransform?

Using the XslCompiledTransform will compile the XSLT style sheet down to a common intermediate format. And once the style sheet is compiled, it can be cached and reused. Making the XslCompiledTransform considered the best choice for the “one Load, many Transforms” scenarios.

Sources:

http://blogs.msdn.com/b/antosha/archive/2006/07/16/xslcompiledtransform-slower-than-xsltransform.aspx
http://msdn.microsoft.com/en-us/library/66f54faw(v=vs.110).aspx
http://msdn.microsoft.com/en-us/library/system.xml.xsl.xslcompiledtransform.aspx
http://msdn.microsoft.com/en-us/library/system.xml.xsl.xsltransform.aspx

BizTalk Server 2013 is now RTM!

BizTalk Server 2013 is available on MSDN as of now!

BTS2013-MSDN

With this, the eighth release of BizTalk is a fact.

These are the new features that are announced for the BizTalk Server 2013 release:

  • Integration with Cloud Services – BizTalk Server 2013 Beta includes new out-of-the box adapters to send and receive messages from Windows Azure Service Bus, making it easy to build hybrid solutions. It also provides capabilities to host BizTalk endpoints in Azure through the Service Bus Relay providing a simple and secure way to connect external partners and application to BizTalk Server on-premises. New adapters include
    • WCF-BasicHttpRelay
    • WCF-NetTCPRelay
    • SB-Messaging
  • RESTful services – BizTalk Server 2013 Beta provides adapters to invoke REST endpoints as well as expose BizTalk Server artifacts as a RESTful service.
  • Enhanced SharePoint adapter – Integrating with SharePoint using BizTalk Server 2013 is now as simple as integrating with a file share. We have removed the dependency on SharePoint farms, while still providing backward compatibility.
  • SFTP adapter –Enables sending and receiving messages from an SFTP server.
  • ESB – The ESB capabilities previously introduced in the ESB Toolkit are now fully integrated with BizTalk Server and the ESB configuration experience is vastly simplified to enable a quick setup.
  • Dependency tracking – The dependencies between artifacts can now be viewed and navigated in the Admin console.
  • Improvements in dynamic send ports –You now have the ability to assign a host handler per adapter, providing better scales and performance for dynamic send ports.
  • XslCompiledTransform – The mapping engine now makes use of the enhanced XslCompiledTransform API, which provides significant improvements in mapping engine performance for complex maps.
  • Ordered Send Port improvements –We have made changes to the BizTalk runtime engine which significantly increases the performance of ordered send port scenarios, for example in HL7 solutions that use the MLLP Adapter.
  • BAM Alerts update – In previous releases of BizTalk Server, BAM Alerts feature had a dependency on SSNS (SQL Server Notification Services). With the current release of SQL Server (SQL Server 2012), SSNS is no longer available. However, we have made sure your existing BAM Alerts scenario work just the same even if your backend is targeting SQL Server 2012. If your backend is SQL Server 2008 R2, you will continue to require the dependency on SSNS.
  • Support for latest LOB version and protocol standards – Additionally, we also support the latest LOB versions and protocol standards. For B2B, the enhancements include
    • Support for X12 5040, 5050, 6020, 6030
    • Support for EDIFACT D06A, D06B, D07A, D07B, D08A, D08B, D09A, D09B, D10A, D10B
    • HL7 2.5.1

BizTalkServer2013

 

With this new release, Microsoft also changed their licensing model for BizTalk Server to a per-core licensing model. This per-core licensing model is consistent with their SQL Server licensing. The impact of the new licensing model for the customer will vary based on how BizTalk Server 2013 will be deployed. Customers running BizTalk Server software on processors with four cores or less will have their license costs remain consistent with BizTalk Server 2010, as core licenses are priced at one quarter the cost of a processor license. Customers running servers with higher capacity processors on the other hand will have their licensing costs grow with the increased power of their hardware. More information on the BizTalk 2013 new licensing model can be found in this document or in this blog.

For more information, please don’t hesitate to contact us on info@cnext.eu (more contact details can be found on http://www.cnext.eu).

BizTalk360 v6.0: new features explained

BizTalk360 released the brand new version v6.0 on March 8th, bringing several new features to the product.
In this blog post, we’ll address most of these new features that has been added in short.

A full overview of the new features can be found here.

1. Search and Action on Artifacts

This feature allows you to search for certain BizTalk artifacts, which can become really useful in large BizTalk applications.

searchArtifacts

You can search on these types of artifacts:

  • Applications
  • Orchestrations
  • Receive Ports
  • Receive Locations
  • Send Ports
  • Send Port Groups
  • Schemas
  • Pipelines
  • Transforms (BizTalk maps)

More details can be found on this BizTalk360 blog post.

2. Custom SQL query

This isn’t exactly a new feature in this version, it is however completely renewed.
Below screenshot will show you the difference between both versions. On the left side is a screenshot of version 5.0, while on the right side is version 6.0.

CustomSQLDiff

As you can see, there are some new functionalities added in the latest version.

First of all you can now export the query results to a CSV file.
Also, now there is the possibility to easily edit the query and execute the edited query again (using the link ‘Edit query’ as shown in the screenshot).

Another major new feature for these custom queries, is not shown in this screenshot but can be found in the ‘Monitor and Notify’ tab on the portal. The new version gives you the ability to create alerting on custom database queries that return a scalar result. A warning and error threshold level can be defined for this alert.
I think this gives BizTalk360 a lot of new possibilities to expand their active monitoring. One of these possibilities we were really missing in the previous version is for example some threshold alerting on the BizTalk Spool size, like explained in this BizTalk360 blog post.

3. SQL Job Outcome monitoring

In the previous version you only had the possibility to monitor if SQL jobs still had the desired status (enabled/disabled).
One of the useful new features is to also have alerting on the SQL Job outcome… So not only the job status can be monitored, but also alerts can be send when a job has failed.

I think this is a huge monitoring improvement, as in the previous version we tried to monitor the BizTalk sql jobs outcome using the MessageBox viewer report (integrated in the BizTalk360 platform).

SQLJobOutcome

4. Threshold monitoring window

The alarm settings have been expanded with the ability to restrict the threshold monitoring to certain days and timings.

AlarmSettings

5. Scheduled monitoring downtime

In the last version you were able to completely stop the alerting when doing some maintenance. You could configure this maintenance window for a certain time period.

With the latest version, the ability to schedule this maintenance window is also possible. You can define the maintenance window for a certain time period, but now you can also configure when this maintenance window will start.

MaintenanceSchedule

6. Monitoring dashboard

The monitoring dashboard has been updated and will also show the health status of the background monitoring service.

In the previous version we had to go to the settings window and choose ‘Monitoring Services’ under the BizTalk360 Health subcategory to have an overview of the health status of the background monitoring service. In our case for example, this posed the problem that the user we had defined to view the monitoring dashboard didn’t had the rights to check the health status of the monitoring service.

With Version 6.0 there still is the possibility to check the health of all monitoring services in the settings window… But the health status of the overall monitoring service will now also be visible in the monitoring dashboard. Which will be useful as this monitoring dashboard is displayed at a monitoring screen in our company, and now also shows the monitoring health status. While in the previous version it didn’t even had the rights to do so.

MonitoringDB

7. User Access Policy enhancements

The security settings has been expanded quite some bit. There are numerous new possibilities you can define rights to for the users.

In the below screenshot, you can see both the user access policies for version 5.0 (left screenshot) and version 6.0 (right screenshot).

SecuritySettings

As you can see in the screenshots, several access rights have been added in this new version. You can now also define separate rights for users on these features:

  • Monitoring Dashboard
  • Throttling analyser
  • Backup/DR visualizer
  • More advanced SQL query permissions (edit permission, list of queries etc) => when clicked on the link ‘Advanced’

These are only some of the features I found most useful… of course there are some more new features added as stated in the BizTalk360 blog post mentioned earlier.

Conclusion

BizTalk360 gives us huge benefits in monitoring all our environments. And these features will certainly improve our monitoring.
Still, there are some functionalities we think are missing and hope to see in one of the next versions:

  • Ability to save queries (message box, tracking, event viewer): queries need to be build all over again each time now
  • BizTalk query building enhancements: still missing the ability to ‘group by’ and increase the number of max matches to a self defined number
  • Alerting on throttling
  • Monitoring dashboards switching between defined alarms: this would be useful for the monitoring screen that we have set up
  • Layout changes in monitoring dashboard will reset on each refresh cycle: in the monitoring dashboard, you have the ability to move around all items as you want them to be (or zoom in/out)… however, on each refresh cycle (or on switching between alarms) the layout will reset to its default