Posts Tagged ‘BAM’

Today while doing a BAM POC to one of my clients, I found a number of problems/errors, some of them unexpected and uncommon, while trying to deploy my BAM definition file.

In order to contextualize the problem, for this POC we are using only one BizTalk Server machine with BAM Portal, one dedicated SQL Server and Analysis Server and Integration Server in another dedicated server. So, more or less a simple Multi-Computer Environment.

In this post I will address the most unusual of them:

Deploying Activity… Done.
Deploying View… ERROR: The BAM deployment failed.
Could not load file or assembly ‘Microsoft.SqlServer.ASTasks, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. The system cannot find the file specified.

CAUSE

Well, the cause the cause is quite obvious by its description. Microsoft.SqlServer.ASTasks assembly is missing or some of its dependencies, although, the environment has been installed, configured and functioning correctly, i.e., everything was working properly with exception of BAM deployment.

My first suspicion (which ultimately turned out to be right), and because, as I told before, SQL Server is remote was that not all the SQL Server Client Tools components were installed in the BizTalk Server machine.

This suspicion was confirmed when I open the SQL Server 2014 Management Studio console. Because the machine only had the “Management Tools – Basic” feature installed.

SQL-Server-Management-Tools-Basic-feature

The “differences” between basic and complete are:

  • Management Tools – Basic: includes support for the Database Engine and SQL Server Express, SQL Server command-line utility (SQLCMD) and SQL Server PowerShell provider
  • Management Tools – Complete:  as the name implies, you get it all. It adds support Reporting Services, Analysis Services and Integration Services technologies.

Because support for Analysis Services is required in BizTalk Server machines while deploying the view definition, this error occurs.

SOLUTION

To solve this problem, you must install also, on BizTalk Server machine, the “Management Tools – Complete” shared feature. To do that you need:

  • Clicking in the “Compatibility View” button that appears in the Address bar to display the site in Compatibility View.
  • Run SQL Server 2014 setup program.
  • On the SQL Server Installation Center, click Installation.
  • Click New Installation or Add Features to an Existing Installation.
  • Review the information on the Setup Support Rules screen, and then click OK.
  • On the Product Key screen, enter your product key and click Next.
  • On the License Terms screen, select I accept the license terms, and then click Next.
  • On the Setup Support Files screen, click Install.
  • On the Feature Selection screen, select the following features, and then click Next.
    • Shared Features
      • Management Tools – Basic
      • Management Tools – Complete

Once “Management Tools – Complete” is installed, you will be able to deploy your BAM definition file.

When trying to deploy BAM definition by:

  • Open a command prompt as follows: Click Start, click Run, type cmd, and then click OK.
  • Navigate to the tracking folder by typing “C:\Program Files (x86)\Microsoft BizTalk Server <version>\Tracking” at the command prompt. Press ENTER.
  • Type “bm deploy-all -DefinitionFile:<BAM definition file>”
  • Press ENTER.

I got the following error:

C:\Program Files (x86)\Microsoft BizTalk Server 2013\Tracking>bm.exe deploy-all -DefinitionFile:Tracking.xls
Microsoft (R) Business Activity Monitoring Utility Version 3.10.229.0
Copyright (C) Microsoft Corporation. All rights reserved.

Using ‘BAMPrimaryImport’ BAM Primary Import database on server ‘BIZTALK’…
Deploying Activity… Done.
Deploying View… ERROR: The BAM deployment failed.

Internal error: The operation terminated unsuccessfully.
OLE DB error: OLE DB or ODBC error: Login failed for user ‘Domain\UserAccount’.; 42000.
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of ‘bam_MessageView’, Name of ‘bam_MessageView’.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of ‘MessageView_ProcessDate’, Name of ‘MessageView_ProcessDate’ was being processed.
Errors in the OLAP storage engine: An error occurred while the ‘Hour’ attribute of the ‘MessageView_ProcessDate’ dimension from the ‘BAMAnalysis’ database was being processed.

OLE DB error: OLE DB or ODBC error: Login failed for user ‘Domain\UserAccount’.; 42000.
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of ‘bam_MessageView’, Name of ‘bam_MessageView’.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of ‘MessageView_ProcessDate’, Name of ‘MessageView_ProcessDate’ was being processed.
Errors in the OLAP storage engine: An error occurred while the ‘Week’ attribute of the ‘MessageView_ProcessDate’ dimension from the ‘BAMAnalysis’ database was being processed.
OLE DB error: OLE DB or ODBC error: Login failed for user ‘Domain\UserAccount’.; 42000.
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of ‘bam_MessageView’, Name of ‘bam_MessageView’.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of ‘MessageView_DestinationParty’, Name of ‘MessageView_DestinationParty’ was being processed.
Errors in the OLAP storage engine: An error occurred while the ‘DestinationPartyName’ attribute of the ‘MessageView_DestinationParty’ dimension from the ‘BAMAnalysis’ database was being processed.
OLE DB error: OLE DB or ODBC error: Login failed for user ‘Domain\UserAccount’.; 42000.
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of ‘bam_MessageView’, Name of ‘bam_MessageView’.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of ‘MessageView_PipCode’, Name of ‘MessageView_PipCode’ was being processed.
Errors in the OLAP storage engine: An error occurred while the ‘PipCode’ attribute of the ‘MessageView_PipCode’ dimension from the ‘BAMAnalysis’ database was being processed.
Server: The current operation was cancelled because another operation in the transaction failed.
OLE DB error: OLE DB or ODBC error: Login failed for user ‘Domain\UserAccount’.; 42000.
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of ‘bam_MessageView’, Name of ‘bam_MessageView’.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of ‘MessageView_ProcessDate’, Name of ‘MessageView_ProcessDate’ was being processed.
Errors in the OLAP storage engine: An error occurred while the ‘Year’ attributeof the ‘MessageView_ProcessDate’ dimension from the ‘BAMAnalysis’ database was being processed.
OLE DB error: OLE DB or ODBC error: Login failed for user ”Domain\UserAccount’.; 42000.
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of ‘bam_MessageView’, Name of ‘bam_MessageView’.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of ‘MessageView_ActivityName’, Name of ‘MessageView_ActivityName’ was being processed.
Errors in the OLAP storage engine: An error occurred while the ‘ActivityName’ attribute of the ‘MessageView_ActivityName’ dimension from the ‘BAMAnalysis’ database was being processed.
OLE DB error: OLE DB or ODBC error: Login failed for user ‘Domain\UserAccount’.; 42000.
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of ‘bam_MessageView’, Name of ‘bam_MessageView’.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of ‘MessageView_ProcessDate’, Name of ‘MessageView_ProcessDate’ was being processed.
Errors in the OLAP storage engine: An error occurred while the ‘Day’ attribute of the ‘MessageView_ProcessDate’ dimension from the ‘BAMAnalysis’ database was being processed.
OLE DB error: OLE DB or ODBC error: Login failed for user ‘Domain\UserAccount’.; 42000.
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of ‘bam_MessageView’, Name of ‘bam_MessageView’.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of ‘MessageView_SourceParty’, Name of ‘MessageView_SourceParty’ was being processed.
Errors in the OLAP storage engine: An error occurred while the ‘SourcePartyName’  attribute of the ‘MessageView_SourceParty’ dimension from the ‘BAMAnalysis’ database was being processed.
OLE DB error: OLE DB or ODBC error: Login failed for user ‘Domain\UserAccount’.; 42000.
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of ‘bam_MessageView’, Name of ‘bam_MessageView’.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of ‘MessageView_ProcessDate’, Name of ‘MessageView_ProcessDate’ was being processed.
Errors in the OLAP storage engine: An error occurred while the ‘Minute’ attribute of the ‘MessageView_ProcessDate’ dimension from the ‘BAMAnalysis’ database was being processed.

BAM-Deploy-OLE-DB-error-or-ODBC-error-Login-failed-for-user

CAUSE

The main reason for the error above happen is when the SQL Server service account does not have read access to BAMStarSchema database.

SOLUTION

To fix this issue you have two options.

First option:

  • Open SQL Server Management Studio and expand BAMStarSchema database
  • Expand “Security”, right-click on the “User” option, And select “New User…”
  • Configure the SQL Server service account to have db_datareader Role access as shown below

BAM-BAMStarSchema-database-db_datareader-Role-access

Second Option:

  • Open SQL Server Management Studio and execute the following script (Replacing the SQL Server service account)
USE BAMStarSchema
GO
EXEC sp_addrolemember 'db_datareader', 'Domain\SQL Server service account'
GO

After I deploy a BAM activity to a new BizTalk Server 2010 environment I create a job called “BAM Generic Import data” to import all BAM data information to OLAP Cubes that will be presented on Aggregations tab in the BAM Portal.

However when I try to manual execute the job I got an error messages saying:

“The cube “MyView” was not processed. Please run the cube DTS to process the cube”

When I went to the event viewer I saw several Bam Event Provider warning messages with the following details:

Bam-Event-Provider-warning-messages

Log                Job History (BAM Generic Import data)
Step ID                0

Server                MyServer\BIZTALK
Job Name                BAM Generic Import data
Step Name                (Job outcome)
Duration                00:00:00
Sql Severity                0
Sql Message ID                0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted                0

Message

The job failed. Unable to determine if the owner (Domain\User) of job BAM Generic Import data has server access (reason: Could not obtain information about Windows NT group/user ‘Domain\User’, error code 0x2. [SQLSTATE 42000] (Error 15404)).

CAUSE

This error most likely when the machine account that runs the job (BAM Generic Import data) doesn’t have permission to query the AD.

Job-BAM-Generic-Import-Data-ower

SOLUTION 1

I would recommend requesting to the AD administrator access to this user or change owner that runs this job to a low-privileged domain account that has proper permissions on the AD – Members of the Domain should be enough.

SOLUTION 2

However in my case the AD administrator was in vacation and the other unavailable and my user account indeed didn’t have permission to query the AD (don’t ask me why) so the only solution that I found to try to solve the problem was to change the owner to the SQL Login System administrator (sa) that, lucky, wasn’t locked.

Job-BAM-Generic-Import-Data-ower-fixed

And problem solved … at least momentarily until we can apply the solution 1

After installing some upgrades in the BizTalk Server machine, in this particular case after installing Internet Explorer 10 I started to receive the following error message when I try to access BAM Portal:

“Failed to get data. If available, errors returned from the provider are listed below”

BAM-Portal-Failed-to-get-data

However without no error was listing… so what’s the problem and how can I solve it?

Additional I also get this error messages and bad behaviors:

BAM-Portal-Pivot-Table-Provider-MSOLAP-error

BAM-Portal-Pivot-Table-Provider-MSOLAP-error-2

CAUSE

Sometimes a website you’re visiting, in this case BAM Portal, doesn’t look like you expect it to. Images might not show up, menus might be out of place, and text boxes could be jumbled together. This can be caused by a compatibility problem between Internet Explorer and the site you’re on. When a site is incompatible with Internet Explorer, you’ll see the Compatibility View button in the Address bar.

Note: Not all website display problems are caused by browser incompatibility. Interrupted Internet connections, heavy traffic or network connections can also affect how a page is displayed.

SOLUTION

To solve this problem you must turn on Compatibility View for BAM Portal in the IE10. You can do this by:

  • Clicking in the “Compatibility View” button that appears in the Address bar to display the site in Compatibility View.

BAM-Portal-tap-Compatibility-View

Note: If you don’t see the button, there’s no need to turn on Compatibility View.

Once you turn on Compatibility View, Internet Explorer will automatically show that site in Compatibility View each time you visit. You can turn it off by tapping or clicking the button again. Or, you can clear the entire list of sites using Compatibility View by deleting your browsing history.

When we try to access BAM Portal, sometimes Office Web Components Display Cross-Domain warning messages such as:

“This Web site uses a data provider that may be unsafe. If you trust the Web site, click OK, otherwise click Cancel.”

This-website-uses-data-provider-that-may-be-unsafe

Or this other common warning messages

“This page accesses data on another domain. Do you want to allow this?”

Or even:

“Failed to get data. If available, errors returned from the provider are listed below… Safety settings on this machine prohibit accessing a data source on another domain.”

CAUSE

This are some of the normal warning message that the Microsoft Office 2003 Web Components displays when a user try to accesses to BAM Portal, some because a scripted Web page tries to run ADO code from an untrusted provider (accessing data from a domain other than the one from which the Web page originates)

For ADO 2.7 and earlier you may receive: “This page accesses data on another domain. Do you want to allow this?”

However, for ADO 2.8 and later, the preceding message no longer appears. Instead, the following message appears in this context: “This Web site uses a data provider that may be unsafe. If you trust the Web site, click OK, otherwise click Cancel.”

The purpose for the alert is to prevent a Web page author from directing users to a potentially malicious page that uses the security context of the user to access data for which the author does not have access.

SOLUTION

To avoid this messages in Microsoft Internet Explorer, you can add a secure Web site to your Trusted Sites zone on the Security tab of the Internet Options dialog box:

  • In the Internet Explorer window, click “Tools”, then click “Internet Options”.
  • Click the “Security” tab, and then select the “Trusted sites” zone.

Add-BAM-Portal-Trusted-Sites-Zone-Security-tab

  • Click “Sites” button, and then “Add” the BAM Portal to the Trusted sites zone

Add-BAM-Portal-Trusted-Sites-Zone

It´s a common factor that before users can view data from a BAM model in the BAM Portal website, they must be granted access to the views. However, and contrary to what I thought, the user that makes the deploy of the BAM Definition don’t necessarily have access to this particular view in the BAM Portal! (I was being misled because usually I am the owner… hehe)

BAM-Portal-Missing-View

Instead the only user that always has access to the view and cannot be added to or removed from the view(s) is the Database Owner (BAMPrimaryImport)!

You can check who the database owner is by running the following query:

SELECT SUSER_SNAME(owner_sid),name
FROM sys.DATABASES

So after I run this query I realize that another user was the owner and to solve this “problem” I had to give permissions to my user to access this View in BAM Portal by running the following BAM Management Utility command:

bm.exe add-account -AccountName:DOMAIN\USER -View:MyView

BAM-Portal-With-View

Mystery solved Sorriso

Last day I was able to convince my client to use, for the first time, BAM for tracking and monitoring of specific processes. As I anticipated, 1-hour work resulted in a major impact (for better) on the people responsible for these tasks in the organization.

However after I deploy my BAM Definition and tracking profile in production environment and everything was working well, i.e., processes were running successfully and data was being tracking (I was able to see the tracking data in database)…

If you don’t know, and contrary to what I thought also, the only user that always has access to the view and cannot be added to or removed from the view(s) is the Database Owner (BAMPrimaryImport). So the user that made the deploy of this BAM Definition don’t necessarily have access to this particular view in the BAM Portal!

In this particular case I’m also the Database Owner, but when I try to access to BAM Portal the access was constantly being denied me and always asking to enter my credentials when browsing to it… even when I gave access to another domain user to this view and tested with these credentials the problem remained.

After examining the logs in event I found this information message:

BAM-Web-Event-Information

With the following details:

Event code: 4007
Event message: URL authorization failed for the request.
Event time: 23-04-2013 15:55:52
Event time (UTC): 23-04-2013 14:55:52
Event ID: 053c6e752b6a4de8ae400a9a9d7d26b1
Event sequence: 10
Event occurrence: 9
Event detail code: 0

Application information:
Application domain: /LM/W3SVC/1/ROOT/BAM-1-130112015742350508
Trust level: BAMPortal_Minimal
Application Virtual Path: /BAM
Application Path: D:\Program Files (x86)\Microsoft BizTalk Server 2010\BAMPortal\
Machine name: MyMachine

Process information:
Process ID: 9560
Process name: w3wp.exe
Account name: DOMAIN\bts-bam-ap

Request information:
Request URL: http://localhost/BAM
Request path: /BAM
User host address: 192.168.***.***
User: DOMAIN\MYUSER
Is authenticated: True
Authentication Type: Negotiate
Thread account name: DOMAIN\MYUSER

Custom event details:

CAUSE

Well, unfortunately, this problem or similar problems can happen for many reasons:

Fortunately for me I have an E2E test environment which is an almost exact replica of PROD with which I could compare to see what was the problem and that was working well Sorriso.

One thing I was sure: I had permission problems!

The first thing was to analyze the basic settings of the application pool like: credentials or .net version and so on… however everything was properly configured and equal to the test environment.

After a few minutes I remembered the basics… if you remember the BizTalk Configuration experience, you use the BizTalk Server configuration tool to specify whether BAM is enabled, and to specify the Web service accounts, the Windows groups that can view portal, and the Web site that will host the portal.

BAM-Portal-Configuration

That you also can see in “.Net Authorization Rules” under BAM website:

BAM-IIS-Net-Authorization-Rules

Using the principle of least privilege, user accounts should have restrictive permissions to perform routine tasks in the BAM portal. BizTalk BAM Portal Users is the group, at least for me but this may change according to your configurations, where you defined the users or groups that can access to BAM Portal Web site.

SOLUTION

In my case, after checking in AD, there was no one configured to have access to BAM Portal.

To solve this problem you have to configure the users or groups that you want to have access to BAM Portal under the “BizTalk BAM Portal Users” in your Active Directory.

After this operation everything work fine! Exactly as it should.

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!!

When trying to configure BAM features in BizTalk Server 2010 (64 bit) with a remote SQL Server 2008 R2 (64 bit) I was getting these two errors:

When trying to configure “BAM Archive Database”

Microsoft SQL Server Data Transformation Services (DTS) 2008 with SP1 or higher for BAM Archiving is not installed on the local machine. Please install Microsoft SQL Server 2008 Integration Services. (Microsoft.BizTalk.BAM.CfgExtHelper.ToolsHelper)

BAM-Archive-Database-error

And when trying to configure “BAM Analysis Database”

Microsoft SQL Server Data Transformation Services (DTS) 2008 with SP1 or higher for BAM Archiving is not installed on the local machine. Please install Microsoft SQL Server Integration Services 2008 with SP1 or higher. (Microsoft.BizTalk.BAM.CfgExtHelper.ToolsHelper)

BAM-Analysis-Database-error

Both errors with the same additional information:

——————————
ADDITIONAL INFORMATION:
Could not load file or assembly ‘Microsoft.SqlServer.ManagedDTS, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. The system cannot find the file specified. (Microsoft.BizTalk.Bam.CfgExtHelper)

CAUSE

As Kent Weare said in his post: “This error makes it sound like you need to install SQL Server Integration Services (SSIS) which is a little misleading.”

The documentation on MSDN indicates the following:

What 64-bit SQL Server components are required to configure BAM tools?

The configuration wizard is a 32-bit process; therefore it requires certain components which allow it to communicate with 64-bit SQL Server. You must install the following SQL Server client components to enable configuration of BAM tools:

  • Connectivity Components
  • Management Tools
  • Legacy Components

SOLUTION

  • Install the SQL Server 2008 R2 Management Tools (Basic & Complete) features as shown in the screenshot onto the BizTalk Server 2010:

SQL-Server-2008-R2-Management-Tools

Tags: BizTalk | BAM | Configuration | Errors and Warnings, Causes and Solutions

When trying to deploy BAM activity into a new BizTalk Environment:

  • “C:\Program Files (x86)\Microsoft BizTalk Server …\Tracking>bm.exe deploy-all -DefinitionFile:BAM.SMSExpress.xls”

It happened to me the following error:

“Microsoft (R) Business Activity Monitoring Utility Version 3.9.469.0
Copyright (C) Microsoft Corporation. All rights reserved.
Using ‘BAMPrimaryImport’ BAM Primary Import database on server ‘BTS2010LAB01’…
Deploying Activity… Done.
Deploying View… ERROR: The BAM deployment failed.
The BAM Star Schema database has not been configured. Run bm.exe setup-databases to configure the database.

CAUSE

  • One of the possible causes of the problem is that Analysis Services form BAM aggregations was not configured.

SOLUTION

  • Open BizTalk Server Configuration Console. (All Programs → Microsoft BizTalk Server … → BizTalk Server Configuration)
  • Select the option “BAM Tools” in the left menu and check the option “Enable Analysis Services for BAM Aggregations”
  • Configure “BAM Analysis Database” and “BAM Star Schema Database”
    BAM-Tools-Configuration
  • Click in “Apply Configuration”
  • On the Summary screen, Click “Next”
    BAM-Tools-Configuration-summary
  • On the completed screen, click “Finish”

Tags: BizTalk | BAM | Deploy | Errors and Warnings, Causes and Solutions