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.

Advertisements

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.