Dates being displayed incorrectly in the ESB Portal

One of the big selling points of the ESB Portal is the fact that it is so easy to resubmit messages, potentially even editing them prior to the resubmission.  This takes away a lot of the pain I previously had to deal with when thinking of retry mechanisms (one of the patterns I used before was to have the orchestration in error dehydrate, have an email sent out to IT administrators with HTTP links in them to either resume, suspend, or terminate said orchestration.  This could be a pain to implement though and tended to muddy up your orchestrations with non-core process logic).

However, one can’t just allow for the resubmission of messages which could potentially be edited without some kinds of auditing feature.  This is one of the areas where the ESB Portal shines, because it can audit all message resubmissions, listing the time of the resubmission, the resubmitter, the status of the resubmission, and the unedited and edited message.  It was more than a little irritating when I found that the dates being displayed on the Audit Log and the Audit Resubmitted Message Viewer page were showing incorrectly, upto years into the future in fact!  What’s even more irritating was that the dates on all the other pages appeared to show up correctly.

Last I checked it was still 2012

A quick query on the AuditLog table in the ESBException database showed that the date was actually being stored correctly, albeit in UTC.  So that leaves to reason that it is just being displayed incorrect.

Checking around the rest of the database, it is quickly apparent that most (or all)  of the dates in the ESB databases are held in UTC, and they’re definitely being converted to local time correctly in the rest of the portal, so what gives?  Time to do some code inspection and debugging.

If you take a look at the AuditLog.ascx file in the Admin Folder in the ESB.Portal project, you’ll see that a javascript method is used to convert the UTC data to local time as below.

Next step was to do some debugging and I was pretty surprised by the output.

As you can see very clearly, the UTCToLocalDate method is called with a parameter value of “23/02/2012 9:06:19 a.m.” but when a new javascript date variable is spun up passing this value in as the constructor, the new variable comes out with a totally unexpected value.  Obviously there are restrictions on what sort of string values you can pass in as constructors to the javascript date object because it just isn’t happy with the date time format that was used in this case.

So I figured I had two options to fix this problem.  Either I could get smart in the javascript and manipulate the utc string variable so that it conforms to a javascript date format, or I could see how the remaining ESB portal pages were handling their UTC to Local Date format conversions and implement a similar solution on these pages.  I chose the latter because it sounds that much easier.  I inspected the FaultViewer.ascx page in the faults directory in the ESB.Portal project and found that it was instead using a .NET helper class to the do the UTC To Local conversions and the date being passed in as a parameter was of the same format as the date passed in to the javascript method.

Ok, so to fix the Audit Log page, edit the AuditLog.ascx file in the admin folder in the ESB.Portal project so that the date section looks likes the below.

And now to fix the Resumbitted Message Viewer page, edit the ResubmittedMessageViewer.ascx file in the admin folder in the ESB.Portal project so that the resubmitted date section looks like the below.

I acknowledge that this most probably is an issue to do with the locale in our environment but I am loathe to make any changes to production servers, so this is the best fix for me.  Our servers are all set to the correct time zone, and the culture in the web.config files for the ESB.Portal and supporting services was set appropriately to EN-NZ,  but when I checked the SQL Server setup the language was set to American English, so this could be a part of the problem.

That said, this problem might also be New Zealand wide, or it could possibly even be global, I have no idea.  If any of you experience the same problem please do let me know where you are from, what time zone your server is set to, and what language your SQL Server is set to.

ESB Toolkit 2.1 Multi-Server Security Considerations

A recent challenge I had to face was to get the ESB Toolkit 2.1 installed on a multi server environment where BizTalk Server 2010 and the SQL Server database are hosted on seperate servers.  We wanted for BizTalk Administrators to use the ESB Portal to view faults, to edit and resubmit messages to the MessageBox, and to create and manage alerts and subscriptions.  The BizTalk Administrators should be able to access the portal from their PCs rather than just on the BizTalk server, as they would be clicking links in alert emails to quickly view fault details and action faulted messages.  I managed to get this working and below is an account of how I managed to achieve this, I hope this helps others who are facing similar challenges.

I am assuming that you have already installed the ESB Toolkit BizTalk application, ESB Portal, ESB Admin/Exception/Itinerary databases, and supporting web services.  There are a lot of blog posts out there on how to do this, I recommend you don’t use the cmd scripts provided by the installer and instead build the installer in the ESB.Portal solution and use that to deploy the portal instead as detailed here – http://www.google.co.nz/url?sa=t&rct=j&q=esb%20toolkit%202.1%20installation%20guide&source=web&cd=2&ved=0CCUQFjAB&url=http%3A%2F%2Fdownload.microsoft.com%2Fdownload%2F9%2FC%2F5%2F9C5AEC46-717F-49B6-A7BC-36B0930B63FF%2FInstalling%2520BizTalk%25202010%2520ESB%2520Toolkit%25202.1.htm&ei=Z2BYT5LkDIWdiAeJxamsDQ&usg=AFQjCNGA97tfJuXd_RTGX2JrtXh1azt_vg (sorry, this isn’t a very nice link, and you might have to save the htm file to your PC and open it manually if you’re using later versions of web browsers, but it is very informative).

The biggest challenge in this scenario is the fact that the ESB Portal web application refused to forward the impersonated credentials to the SQL Server since it is on another server.  This is known as the double hop issue, and is one faced quite often in multi server setups, be they BizTalk, sharepoint, or any other kind of application that relies on forwarding impersoned credentials across servers.  The double hop issue is quite well explained here – http://support.microsoft.com/default.aspx?scid=kb;en-us;329986.  Luckily I managed to find a pretty good blog post that helped me work with our IT Infrastructure team to work around this problem, see the post at http://blogs.technet.com/b/meamcs/archive/2011/11/15/esb-2-1-portal-minimal-access-configuration-kerberos.aspx.  At this stage you should have a new AD User (lets call this Test-ESBPortalUser for the rest of the blog post) created and should setup the SPNs and delegated trust between IIS and SQL Server for this username as detailed in sections 2.2.1-2.2.3 in the aforementioned blog post (our IT Infrastructure guy found this a very straightforward task using the instructions in the aforementioned blog, so you should be able to turn to someone in an equivalent role at your company in case you are not comfortable with these concepts).

Now here is where you may diverge somewhat from me.  I decided that I wanted this Test_ESBPortalUser account to have the minimum access required to the BizTalk databases so I added had it added to the BizTalk Application Users AD Group.  I later found that this group didn’t have all the required permissions and had to manually grant special permissions to this user (detailed further later).  You might have better luck trying to add the user to the BizTalk Server Administrators group, but I really didn’t want to do that.

The next step is to get impersonation working so that all the ESB web applications are able to access the SQL Server database.  You’ll quickly find that even if you enable impersonation in IIS or in the web.config to use the authenticated user, and you set the web applications app pool to a user that has permission to the required databases, or if you access the ESB Portal using credentials that have access to the required databases, the ESB Portal will still fail to load with 401 unauthorized type errors.  Well, according to http://qa.social.msdn.microsoft.com/Forums/en-US/biztalkesb/thread/2a1519c1-bab3-40fb-a645-a7e54f606b22 I found that one must actually enter the username and password of the impersonated user in the web.config file (or through IIS which will just write it to the web.config file).  This is quite a terrible way to store credentials because they are exposed in plain text.  Luckily Mark Brimble at Datacom NZ (thanks a ton Mark!) managed to give me an idea that I explored further and works quite well.

Microsoft has provided a command line tool called aspnet_setreg.exe that you can use to encrypt your credentials in the registry and have your web application look up and decrypt those credentials securely.  Download the tool from here – http://support.microsoft.com/kb/329290 and follow the below instructions.

• Open a command prompt (you might have to run as administrator if UAC is enabled) window and browse to the folder where you saved the utility.
•  Type in the following command, replacing the <username> and <password> with the relevant values for your Test-ESBPortalUser equivalent) – aspnet_setreg.exe -k:SOFTWARE\Microsoft\ESBPortal -u:”xxxxx” -p:”xxxx”
• Open registry editor.
• Browse to the following key – HKLM\SOFTWARE\Microsoft\ESBPortal\ASPNET_SETREG (note that if you are doing this on a 64-bit machine the key you’re after will be HKLM\SOFTWARE\Wow6432Node\Microsoft\ESBPortal\ASPNET_SETREG even though the output from the command might say otherwise).
• Right click on the key and select permissions.
• Grant read access to the Network Service account on the machine.

What we have done is store the impersonation credentials in the registry, and ensured that only the Network Service account (and administrators of course) are able to access this key.  That’s a whole lot better than plain text in a web.config file.  Now the next trick is to update the relevant web.config files to make use of these encrypted credentials.  What you want to do is to replace the <identity impersonate=”true”/> sections in these web.config files with <identity impersonate=”true” password=”registry:HKLM\SOFTWARE\<Wow6432Node\>Microsoft\ESBPortal\ASPNET_SETREG,password” userName=”registry:HKLM\SOFTWARE\<Wow6432Node\>Microsoft\ESBPortal\ASPNET_SETREG,userName” /> instead (the <Wow6432Node\> section is only required if you are on a 64-bit system).  This will allow these web applications to make use of the encrypted credentials for impersonation.  The applications who’s web.config files you want to update are listed below.

• ESB.Portal
• ESB.UDDI.Service
•  ESB.BizTalkOperationsService
• ESB.ItineraryServices.WCF
• ESB.ResolverServices
• ESB.ResolverServices.WCF

You will also want to setup authorization on the ESB.BizTalkOperationsService and ESB.Portal applications in IIS such that only the BizTalk Server Administrators are allowed to access these applications.  There is an additional section in the ESB.Portal web.config (System.Webserver/Security/Authorization) where you will need to manually add permissions for the BizTalk Server Administrators role.

Getting closer to done now but theres still a bit more to go 🙂 Now it’s time to make sure we have the correct authentication settings configured for all the web applications in IIS.  I’ll group the applications which have common settings together, assume that any authentication providers I don’t mention should be disabled.

• Windows Authentication Enabled (Right click and choose providers and only Negotiate should be selected, Right click and choose advanced settings and ensure that Kernel mode authentication is enabled and extended protection is off), and ASP.NET authentication enabled
  • ESB.Bam.Service
  • ESB.BizTalkOperationsService
  • ESB.Exception.Service
  • ESB.UDDI.Service
• Windows Authentication Enabled (Right click and choose providers and only Negotiate should be selected, Right click and choose advanced settings and ensure that Kernel mode authentication is enabled and extended protection is off), and ASP.NET authentication enabled, Anonymous authentication enabled
  • ESB.ExceptionHandlingService
  • ESB.ItineraryService
  • ESB.ItineraryService.Generic.Response.WCF
  • ESB.ItineraryService.Generic.WCF
  • ESB.ItineraryService.Response
  • ESB.ItineraryService.Response.WCF
  • ESB.ItineraryServices.WCF
  • ESB.ResolverServices
  • ESB.ResolverServices.WCF
  • ESB.TransformServices
  • ESB.TransformServices.WCF
• Windows Authentication Enabled (Right click and choose providers and Negotiate and Negotiate:Kerberos should be selected, Right click and choose advanced settings and ensure that Kernel mode authentication is disabledand extended protection is off), and ASP.NET authentication enabled
  • ESB.Portal

Lets ensure that we have all the web applications running in the correct application pools.  Below are the list of app pools you should have setup with their relevant configurations, and the web applications that should be running under these app pools.

• CoreEsbAppPool – Running under relevant BizTalk Isolated Host User account and in Integrated mode
  • ESB.ItineraryServices
  • ESB.ItineraryServices.Response
  • ESB.TransformServices
• CoreEsbNetworkAppPool – Running under relevant BizTalk Isolated Host User account and in Integrated mode
  • ESB.ResolverServices
• CoreEsbOpsAppPool – Running under relevant BizTalk Isolated Host User account and in Integrated mode
  • ESB.BizTalkOperationsService
•  CoreEsbWCFAppPool – Running under relevant BizTalk Isolated Host User account and in Integrated mode
  • ESB.ItineraryService.Generic.Response.WCF
  • ESB.ItineraryService.Generic.WCF
  • ESB.ItineraryService.Response.WCF
  • ESB.ItineraryServices.WCF
  • ESB.ResolverServices.WCF
  • ESB.TransformServices.WCF
• EsbPortalNetworkAppPool – Running under relevant Network Service account and in Classis mode
  • ESB.Bam.Service
  • ESB.Exceptions.Service
  • ESB.Portal
  • ESB.UDDI.Service

Ok, next thing we need to do is give the missing SQL permissions to the Test-ESBPortalUser impersonation account.  Create the login for the user in SQL Server, and create user mappings for the BizTalkMgmtDb database with the BTS_Operators and public roles, and to the ESBException database with the ESBPortalAdmin and public roles.  Edit the ESBPortalAdmin role on the ESBExceptionDB such that it has select, delete, insert access on the alert, alertcondition, and alertsubscription tables.

Lastly, enter the services mmc and set the BizTalk ESB Exception Notification service to run under the Test-ESBPortalUser.

I’m hoping this is enough to get you going, please post a comment if you find that I have missed anything, got anything wrong or made incorrect assumptions, could possibly reduce permissions even further, or if you just need help in general.  I’m hopefully looking at doing a production install of the ESB Toolkit in the next week, and I will update this blog post if I find that I had to do anything different.  Good luck.

ESB Portal for Exception Management – First Impressions

I have recently been working on implementing the ESB Portal, and this has been my prime inspiration to start this blog.  I’ll get to the reason after a bit of background.

About two years ago, when I was still pretty new to the BizTalk world, I was partnered up with some external consultants on a pretty large project when we had to decide on how to manage exception logging and notification in the integration layer.  The options were researched by the external party, and it pretty much came down to using the ESB Portal or to build our own set of orchestrations/schemas/helper classes to help us handle exceptions.  The recommendation given to us at the time was to do our own thing because the ESB Toolkit was an unknown quantity and had the potential to blow out the budget for the project since no one knew quite how long it would take to get it up and running.

About 3 months ago, I installed the ESB Toolkit onto my isolated development VM.  The entire solution includes a BizTalk Application, supporting web services, a windows service that handles alerts, and a portal site.  I found the installation to be quite easy, and I had it up and running in no time.  I immediately noticed some pretty strange behaviours on the portal, but at the same time I was very impressed by it, seeing great potential to reduce effort in the handling of exceptions and in implementing retry mechanisms.  I felt a tinge of regret that we didn’t implement this during that large project when we first implemented BizTalk Server 2010.

And then I tried installing the portal to our test system….and what a wake up call that was.  Forget all the regret I had previously experienced because I very quickly got used to seeing the below image.

Trust me, you get used to seeing this…and you will grow to hate it.

 

Unlike my dev environment, the test system is a bit more complicated

• It is a multi server environment with seperate servers for BizTalk and SQL Server
• There are multiple hosts, each running under their own credentials
• Aforementioned host accounts aren’t administrators on the BizTalk Server
• Aforementioned host accounts are in the BizTalk Application Users group rather than in the BizTalk Server Administrators group (and pretty much every other BizTalk group) as they were on my dev machine
• Aforementioned host accounts aren’t sysadmins on the SQL Server Instance

After a lot of scouring the internet, and with a little help from my friends (cue the Beatles) I finally got it up and running.  And even when I had it up and running, I realized that it wasn’t secure enough and in my crusade to tighten that up I had to get used to seeing the above error message for another two days.

I now have a great template for a multi server environment, and while it includes elements from quite a few blog posts and Microsoft installation documents, it certainly doesn’t strictly follow the setup as prescribed by any of these parties.

This is why my experience with the ESB Portal has encouraged me to startup this blog; I feel like I have something new (mostly borrowed but new in that it is all in one place) to contribute which I hope will help people.  I have a plan to go through quite a few blog posts covering the ESB Portal over the next few weeks and will eventually focus on BizTalk in general.  Look out for more posts soon.

You’ll get there eventually, just be patient.  It’s worth the pain.