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

BizTalk Security – Adding permissions to the BizTalk Operator role

Every BizTalk administrator has probably already received complaints from BizTalk operators that operators can see the number of suspended messages but can’t view the message itself. Another common remark might be that the operators cannot use the Orchestration debugger. Debugging in production is not advised but with the debugger you can at least verify where the orchestration has halted.

Off course, for organisations where IT operations needs to comply with security regulations like Sarbanes Oxley or other compliance rules, the Microsoft best practices for BizTalk security apply. These can be found here. When these compliance rules do not apply for your organisation and after checking with the security responsible you can tackle these problems.

The super operator

There is a way to make it possible for operators to view and save messages with the administration console and let them use the orchestration debugger. Most of the BizTalk security resides in SQL server. In the form of two roles: BTS_OPERATOR for the Operators and BTS_ADMIN_USERS for the BizTalk Administrators. These roles are defined at database level. They can be found in every BizTalk database.

When configuring the BizTalk group these roles are created for operators and administrators with the relevant permissions (securables) on database objects. The Windows groups specified for operators and administrators in the BizTalk group configuration are given a SQL login and granted the accompanying role.Each role has its own securables. These securables are permissions on objects such as stored procedures and tables.

The BizTalk Administrators have a lot more of these securables. Here under you will find the steps to creating a super operator role that delivers extra permissions to operators, for example the permission to save messages.

1. Create a windows group for the super operator.

First we need to create a windows local group, if you do not use active directory accounts and groups, or an active directory group.
Add the members who deserved the super operator rights. These members must already be member of the operator windows group.
This because the super operator group is only an extension to the operator permissions.

2. Create the SQL login for the super operators.

  • Open the SQL management studio and connect to the SQL server that is hosting the BizTalk group databases.
  • Open the server security tab and create a new login by right clicking login and selecting new login.
  • In the login textbox you specify the group you created in step 1.
  • On the user mapping tab you check the checkbox for every BizTalk database.

In this way a user is created for the group in every BizTalk database.


3. Create the super operator role.

A role must be created for the super operator in the necessary biztalk databases. In this scenario we only need to create a role in the BizTalkManagementDB and the BizTalkMessageBoxDB.
For other scenarios it might be possible to create such a role in the BAM databases too.

  • In the SQL management studio expand the Messagebox Database and right click on the roles node.
  • Select new database role.
  • Name this role BTS_SUPEROPERATOR. The owner can be DBO.
  • Add the group you created in step 1 to the role members.
  • Do the same in the BizTalkManagement database.

4. Adding the securables for saving/viewing messages permissions.

In the messagebox database doubleclick the super operator role. Open the tab securables and add the securables according to the screenshot following these steps:

  • Click add.
  • Select specific objects, click ok.
  • In object types check the stored procedures checkbox, click ok.
  • Click the browse button and put a check next to the stored procedures you see in the screenshot.
  • Select every securable one by one and grant the role the execute right.

Now you need to add the securables to the role in the management database.
Follow the steps above but this time add the securables seen in the next screenshot.

That’s it. With the new super operator group created we have an extra level of security. This can be really handy because there are only two roles out of the box. Now there are regular BizTalk operators, BizTalk super operators with save permissions and the BizTalk administrators.

5. Adding another permission, the Orchestration debugger.

To give the newly created BizTalk super operators this additional permission you just have to add some extra securables to the SQL super operator role. Add these securables to the super operator role in the BizTalkDTADb, and grant the execute right to the role for each securable:

We will continue to search for extra permissions to add to the super operator role. These permissions will be posted soon. If you have also found out which securables accompany certain rights or if you have any questions about this topic, feel free to comment them on this post.