Freigeben über

BAM Web Services and SQL Server mixed mode authentication

  • Artikel

This is an issue I see every once in while so let's see what causes it and how to diagnose and fix it.

You have created and deployed a BAM definition with some BAM views in it but when you hit the BAM Portal with IE all you see in the "My Views" pane on the left is an error message "Views or Activites may be missing because one or more database(s) could not be contacted." followed by "No view to display" message. Now what?

Your first step to troubleshooting such issues is the Windows Event Log on the machine hosting the IIS server running the BAM Portal and the BAM web services. The BAM event log entries will be in the Application event log. You can filter on event source equal to "BAM Portal" or "BAM Web Service". I will usually look at the last few errors from "BAM Web Service".

So you open the Windows Event Viewer and you see one error from "BAM Web Service" with event ID 12033. Double-click on it and the description says:

Referenced database 'BAMPrimaryImport' on server 'SERVERNAME' is not accessible. The error is:

System.NullReferenceException: Object reference not set to an instance of an object.
at Microsoft.BizTalk.Bam.WebServices.AsyncResult.End(AsyncResult asyncResult)
at Microsoft.BizTalk.Bam.WebServices.Management.GetViewSummaryAsyncResult.End(IAsyncResult result)
at Microsoft.BizTalk.Bam.WebServices.Management.BamManagementService.EndGetViewSummaryForDatabase(IAsyncResult result)
at Microsoft.BizTalk.Bam.WebServices.Management.BamManagementService.GetViewSummaryForCurrentUser().

Now what?

The second step to troubleshooting BAM Web Services issues is to... enable tracing. To turn on tracing go to the "Program Files\Microsoft BizTalk Server 2006\BAMPortal\BAMManagementService" directory (or "...\BAMQueryService") and open the web.config file with notepad. Uncomment the <system.diagnostics> element towards the end of the file. By default the traces will be saved in "C:\temp\BamManagementServiceTrace.log" or "C:\temp\BamQueryServiceTrace.log". Make sure that the IIS worker process user account (usually Network Service or ASPNET) and the BAM web service user have write permissions for the trace log file directory (C:\temp by default).

Turn on tracing for the BAM Management Web Service and refresh the BAM Portal home page in IE. Now open the trace file "C:\temp\BamManagementServiceTrace.log" and search for the string "Error" from the top of the file to find the first error. In this case the first error is:

<BizTalk2006> 06/13/2006 17:00:36 6 Microsoft.BizTalk.Bam.WebServices.TraceHelper ThrowHelperSoapException Error 11007 Cannot determine the SID for the user.

Bingo! This is the root cause of the problem.

Now some history behind this problem. The BAM Web Services authenticate all users using Windows authentication and authorize them based on their Windows identity. But there is a special case - DBO. DBO is a special account which has permissions to all BAM views. So the BAM Web Services will try to find the Windows identity for the DBO user for the BAM Primary Import database and they will fail if this is a SQL account.

BAM does not support anything other than Windows authentication for SQL Server. If everything else seems to work, there is no guarantee that it actually does and it will continue to work in the future. True, the error is not intuitive but it is correct.

How to resolve the problem now that we have identified it? It is really easy indeed. Just change the DBO accounts for all BAM databases to Windows accounts. You can do this using the sp_changedbowner system stored procedure.

To set DBO to the "domain\user" account run:

use BAMPrimaryImport
go
sp_changedbowner 'domain\user'
go

Navigate to the BAM Portal and verify that you see all BAM views and no errors in the left pane on the home page.

There is a pretty nice BizTalk Installation Guide, which you can use the next time you setup BizTalk Server to avoid such issues.

Comments