Posts Tagged Bugs

Symptoms of a failed ADFS farm install

Problems

This week I worked through what appears to be a situation where an ADFS farm install was not successful or finished incompletely. This event has not been typical in my experience with ADFS so I am simply putting these observations out there for others to be aware of.  After an ADFS farm install had occurred from the command-line, various things about working with it were not working as expected. I looked into the ADFS configuration using the ADFS 2 mmc and found these symptoms. I was not actually the person who did the initial scripted install so I am not aware of what went wrong.

  1. No certificates at all had been selected for the encrypting, token signing, and token decrypting certificates. I know with the UI assisted configuration of ADFS that you must choose a certificate for encrypting and the other 2 are generated ones. For the scripted farm install, I am wondering if there is validation or not or if this was simply a weird event.
  2. Some of the federation endpoint addresses were not showing as expected. For example, the federation metadata address showed as “/FederationMetadata/FederationMetadata.xml” rather than the normal “/FederationMetadata/2007-06/FederationMetadata.xml”.
  3. All of the enabled endpoints were giving me 503 service unavailable errors rather than the 400 bad request errors in the browser. The 400 bad request errors are actually the expected ones. This was very similar to the following old forums thread: http://social.technet.microsoft.com/Forums/en-US/windowsserver2008r2management/thread/ef642548-7c1a-427d-972f-df3dd4f2c829/. The 503 error can occur when the ADFS site is running under the wrong app pool identity but changing this did not resolve the problem for me.
  4. When trying to access the federation metadata page from the address given in step 2, I also received a 503 error. I did not see any more informative error messages in the event logs when the 503 error was occuring.

Resolution

To resolve these issues I simply redid a farm installation by script and this time I was handling the installation myself. I do not think this problem was solely user error but might possibly have been some optional parameters or issues with the fsconfig command-line. The documentation on using fsconfig is somewhat poor so I am guessing there could be some things that could go wrong.

If I encounter this problem again or am able to reproduce it I might try creating some scripts to identify the problem. I am wondering if there might also be other indicators of a failed ADFS install, if you know any please let me know.

Thanks!

Advertisements

,

Leave a comment

Integrating BizTalk 2010 with CRM 2011 Online Organization Service

Introduction

After making the post earlier today about how to interact with the CRM 2011 Online Discovery Service I remembered this was only part of the story. After getting the security information back from the discovery service it is necessary to call the organization service to work with the entities. Some of the organization service functionality is actually new to CRM 2011.

In CRM 4 the Discovery Service existed already but some aspects of the Organization Service are different. I found that the generation of the schemas for the organization service to also be more difficult.

Walkthrough

The service URI for the organization service is https://organization.crm.dynamics.com/XRMServices/2011/Organization.svc but this only gives you part of the WSDL. If you take the WSDL from this address into Visual Studio it will not generate the BizTalk schemas successfully. In fact if you try to add a service reference using the WSDL from this address you will get an error and in the app.config will see comments mentioning that svcutil did not understand the policy assertions.

I looked at the WSDL generated from the above service reference and noticed there was a referenced WSDL file. So if you then try https://organization.crm.dynamics.com/XRMServices/2011/Organization.svc?wsdl=wsdl0, this will give you the full body of the WSDL you actually need to generate the schemas. I copied the much longer WSDL file to my Sky Drive so you can generate your schemas based off of this: http://7wsmza.bay.livefilestore.com/y1pTIOWFbPSpk8WAcLftCF5h8WDa20KbBQ2HXgATcW_iHovw3UEeT74hLQ5HyfXXLcKfAODZDV_Qbc2QqwwkqyOI9uPtvaAMb1l/CrmOrganizationServiceWsdl0.wsdl?download&psid=1. Here is the updated download with my generated artifacts: http://7wsmza.bay.livefilestore.com/y1psUis8O6hlmLvyslbbSrRU38tbauTd7zsnVexj1zVfoDkWtijWO60RoNR-2RSTrMyiSJScK7JpwpGN-s0zU4p28vuseU-4LbC/CrmDiscAndOrgSvc.zip?download&psid=1. The organization service will generate quite a few port types.

When I tried compiling after generating the schemas for the organization service, I received a large number of errors. This also occurs when adding a service reference to the organization service. I have a feeling that you may need to reference a CRM assembly to reuse the types appropriately when generating the schemas, but I do not really know how this works at this point.

Conclusion

So now you have the schemas for how to call the Discovery and the Organization Services from BizTalk. It looks like all that is left is just an orchestration and a few ports once you can get the organization service schemas to compile. Thanks!

, ,

2 Comments

Kayxo BizTalk Exchange Adapter Tips

Introduction

I have been working on an effort to add some automation around processing of emails. In the past I have worked with the built-in POP3 adapter and also the N Software mail adapters found here: https://www.nsoftware.com/products/biztalk/adapters/emails.aspx. The built-in BizTalk mail adapters are not secure because POP3 and SMTP send cleartext passwords. In organizations that use mail functionality in BizTalk it is very important to implement this securely. N Software provides secure receive email support over POP3 and IMAP but these options are not always enabled on Exchange servers.

Today I renewed my search for a good MAPI/Exchange adapter solution. In this post I am going to look at the Kayxo (MAPI) Exchange Adapter and attempt to aggregate some information that is just not in a very good form on the web right now. I will also give some details on some so-far undocumented configurations and how well they are working.

Downloading the Trial

I have been down this road before and find it is typically very hard to get good information about the available BizTalk MAPI adapters in the marketplace. One of the vendors, Kayxo has a very bad website (www.kayxo.com). On many occasions I have not been able to get the site to work at all. Most of the time the home page just gives you a few email links to get more information about the company. If you do some more search engine investigation, you find there is more information, just not linked off the home page. A good overview page is here: http://www.kayxo.com/products/keab_biztalk_partners.php. If you click on the free trial it brings us to a page that looks like an old table of contents: http://www.kayxo.com/downloads/downloads.php. Here is the link to the install guide in case their site dies again: http://www.kayxo.com/downloads/keab_docs/KEAB_InstallationGuide.pdf.

If you try these links you notice PHP errors all over the page – like they have not updated their site in a while. This kind of spotty experience does not fare well if you are looking for a custom adapter, but keep reading, the story does get better.

On the download page there is a link to download the BizTalk adapter (it has a black background) but when clicking on it you just get another PHP/MySql error message (http://www.kayxo.com/downloads/kayxo_exchange_adapter_biztalk.php):

Warning: mysql_connect() [function.mysql-connect]: Lost connection to MySQL server during query in /home/content/k/a/y/kayxo/html/kayxo/includes/configuracion.php on line 8
Could not connect: Lost connection to MySQL server during query

Apparently they are not very good at working with PHP. 🙂 So I tried clicking on the other links and noticed a naming convention for the downloads. So I tried a few attempts at figuring out the download path. Here it is: http://www.kayxo.com/downloads/keab.zip. I was able to download the 8 MB file and this was the start of a fairly good trial experience.

Installation

After downloading the file you can then open the installer and it loads for a while. Back on the overview page (mentioned above), there is a link to the install guide for the adapter. Be sure to download this – it helps make the install process go smoother. The install guide mentions supporting BizTalk 2006/2009. So far in my experience, I have been able to install it successfully on BizTalk 2009 where CU 1 existed. My other config specs were W2K8 [R1], SQL 2008 SP1 CU 1.  On my VM I have Office 2010 installed. The install guide for the Kayxo installer says Only Outlook 2003 and 2007 are supported but I was able to get everything in the trial to install successfully with Office 2010.

[Important!] One of the steps of the configuration is to specify the credentials to login into Exchange with. It seems like this would be easy, just specify the domain\username and password, but the wizard has the domain\username part grayed out and just pulls it from the current thread. You actually have to login with these credentials in order for it to pull the correct credentials. So this means you should only try out this adapter on a computer that is part of the same domain as your email server.

There are a couple steps during the installation that seemed a little odd, so my tip for the install is to be sure to read every word that appears on the screen. Right before the install there is a wizard that has you specify a few setup options. One of the screens asks for the database information. Here is the screenshot from the install guide:

The default value given for server is localhost. So if you do not read the heading (very easy to miss), it looks like you are specifying your IIS server name. The first time through here I missed the heading and what do you know, it cannot find a SQL Server at localhost. This is actually the page you specify the database name. So remove “localhost” and provide the SQL Server instance name here. Then you will get to run the installer. The installer creates the program directory, registers 2 adapters with the BizTalk administration console, and adds some extensibility inside Visual Studio. There is actually quite a bit of functionality provided with the trial but you do have to discover some of it on your own.

If you open the install folder at C:\Program Files (x86)\Kayxo\KEAB you will see the installed files. There is an SDK folder which gives you a good idea of what the BizTalk messages and schemas will look like. For example, if you then open SDK\KEAB_Sdk_KeabSampleMove\Folders\Inbox\MailSample_PutInsideInFolder.xml, you will see a sample file. Here is this same sample file. If you look at the file it is based roughly off of a name/value approach with a little bit more structure. The format basically revolves around the MAPI properties. One tool I have found helpful to understand these is called Outlook spy: http://www.dimastr.com/outspy/. In MAPI there are hundreds of properties for the different objects in Exchange. You need to know quite a bit about the MAPI properties in order to effectively use the Kayxo adapter.

To explain more of this, you can open some files within the Kayxo install folder to get a better handle on some of the details. Open C:\Program Files (x86)\Kayxo\KEAB\MapiProperties.xml to find a listing of all of the different MAPI properties that the adapter knows about. Each has a tag associated with it. I am guessing the parsing engine creates a sort of keyed dictionary for the MAPI properties and then can serialize the properties as part of the message. The tool found in the start menu known as the KEAB Write Message Creator is helpful too for generating a message structure based off the MAPI properties.

After the installer finishes, there is a tricky configuration wizard to work through. Unfortunately the install guide does not really help us that much from a diagnostic perspective. You have to read the install guide word-for-word all the way through to find out enough tips to work through the configuration wizard.

Configuration

If you have Outlook already loaded on your computer and you can connect to your mail server then you will need to be sure that your profile is not in Cached mode. I am not sure what this means, but it is important to know. Here are some directions for disabling this in Outlook 2010:

  1. Open Outlook 2010, authenticate
  2. Click File\Account Settings\Account Settings button
  3. Find your profile and then click Change…
  4. Under your Server find the “Use Cached Exchange Mode” checkbox and be sure it is cleared.
  5. Click Next, and then Finish

You should right-click “Run as administrator” to run the “KEAB Configuration” start menu item to start the configuration wizard. Not doing this will result in you getting some access denied error messages. Then you just have four pages of a wizard to fill out. These pages are documented well in the install guide.

I will be trying to expand this content with some more screenshots and will provide other tips here as well.

Additional Documentation

I did some more search engine investigation and found some good YouTube videos on the adapter: http://www.youtube.com/results?search_query=keab+biztalk&aq=f. The send and receive port ones show a very rich adapter experience so be sure to check these out.

Thanks,

,

Leave a comment

BizTalk 2009 CU 1 Now Available

The latest BizTalk 2009 cumulative update (CU) rollup hotfix pack has been released. This is the first installment for BizTalk 2009. Here is the Microsoft KB article: http://support.microsoft.com/kb/2429050. This includes numerous VS development environment fixes and a wide assortment of adapter support updates. This rollup pack also includes support for the HIPAA 5010 EDI schemas.

Thanks,

,

Leave a comment

New BizTalk Service Pack Model: Cumulative Updates

Introduction

Recently I heard from Microsoft that there is now a new release model for hotfix rollups for BizTalk. The new model is similar to the service pack model (like with BizTalk 2006 R2 SP1) but is known as a Cumulative Update (CU). From what I have heard this is to provide greater frequency to the collective hotfix releases and a more predictable iteration for releases. This model is similar to what is being done with SQL Server and SharePoint Server. This new model of releases applies to all currently supported BizTalk versions including BizTalk 2006 R2 SP1, BizTalk 2009 and BizTalk 2010. Cumulative updates should be applied after running the older rollup of SP1 for BizTalk 2006 R2. It may seem like old news since BizTalk 2006 R2 SP1 is already at CU 3 but I had not heard many announcements about this or people mention CU 3 so I think most people do not know that the rollup release model was changed.

The BizTalk Developer Center Support page at http://msdn.microsoft.com/en-us/biztalk/aa937674.aspx mentions a little more about the new CU model, that the expected frequency is every 2 months, and that each subsequent CU will include all previous CU hotfixes. I was wondering what was the best way to find all of the latest CU releases and the way that worked for me was to search at support.microsoft.com for “BizTalk cumulative update”, so here is the link for this: http://support.microsoft.com/search/default.aspx?query=biztalk+cumulative+update&catalog=LCID%3D1033&mode=r. The released CU versions show up at the beginning of the search results, ordered, nice and easy.

At this time the CU releases are not being localized so if you are using a localized version of BizTalk you will need to install the English version of the CU. If you encounter any issues with the English CU release on your non English system you should report this as a bug so that a corrected localized version of the CU may be created.

Related Resources

BizTalk 2006 R2 SP1 CU 3: http://support.microsoft.com/kb/2286501/en-us 

Thanks,

, , ,

Leave a comment

BizTalk 2010 Party Migration Tool Issues & Workarounds

Introduction

Back during the BizTalk 2010 beta I was testing out the Party Migration tool. This tool enables a smoother migration path from BizTalk 2006 R2 and BizTalk 2009 to BizTalk 2010. It is very useful once you get it to run successfully. Back during the beta I experienced quite a few issues and apparently these issues remain in the final version (RTM) of the tool. This post will describe these issues and give the full steps for a workaround. I am using very similar steps I used during the beta here.

Issues

The party migration tool can be found on the install media at <install media path>\core\BT Server\PartyMigrationTool\PartyMigrationTool.exe. The help description found at http://technet.microsoft.com/en-us/library/aa578307(BTS.70).aspx mentions this should be run from a CD.  It would be nice to know this when trying to run the tool but this is not mentioned in the error messages.

Back during the beta the files were distributed just as they are now with the BizTalk 2010 Developer edition – as an extractable exe that does not come as an ISO. This is problematic because the intention for running this tool is that it will be loaded on an ISO or removable media. So you will need to either burn an ISO or use a workaround.

This tool does require you to right-click Run as Administrator or to execute it from an elevated prompt. Otherwise you will get odd CAS errors:

Request for the permission of type ‘System.Security.Permissions.UIPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’ failed.. For more details please see the event log.

Initially, if you are using the BizTalk 2010 Developer edition or have not burned an ISO, and try to open the PartyMigrationTool.exe you will receive assembly not found errors like this:

System.IO.FileNotFoundException was unhandled
Message: Could not load file or assembly ‘Microsoft.BizTalk.Migration.PartnerManagement, Version=3.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35’ or one of its dependencies. The system cannot find the file specified.

If you run off the ISO or removable media everything works but there is another way for this to work.

Workaround

  1. If you are using BizTalk Developer edition or did not make an ISO, go to the PartyMigrationTool folder. Keep this open.
  2. You will need to add 3 assemblies to the GAC next. I found it easiest to copy these three assemblies to the PartyMigrationTool folder to keep everything together. All of these assemblies are found on the BizTalk 2010 install media. The assemblies you will need are:
    a. Microsoft.BizTalk.B2B.PartnerManagement.dll in \core\BT Server\MSI\Program Files\Developer Tools
    b. Microsoft.BizTalk.Migration.PartnerManagement.dll in \core\BT Server\MSI\Program Files
    c. Microosft.BizTalk.Migration.PartnerManagement.XmlSerializers.dll in \core\BT Server\MSI\Program Files
  3. [Note]: I had previously recommended modifying the PartyMigrationTool.exe.config to redirect the assemblies but upon retrying with the RTM bits I thought it was just easier to GAC everything necessary. 2.a. fails anyway because there is not an entry for it in the config.

I did want to mention one other note. When you finally get the tool running, you will need to connect to SQL Server using Windows authentication, SQL authentication is not supported.

Thanks,

, , , , , ,

6 Comments

Installation Challenges for BizTalk 2010 RTM

Introduction

Today I kicked off the install of BizTalk 2010 RTM. On Friday of last week the BizTalk 2010 downloads were made available to MSDN customers and over the weekend the ESB Toolkit 2.1 RTM downloads also became available (http://www.microsoft.com/downloads/en/details.aspx?FamilyID=8b24d2a7-f079-4123-8428-7699e732a736). For some reason the Standard and Enterprise edition downloads are now almost twice the size of the Developer edition download.

In this post I just wanted to document a couple installation differences and challenges I encountered when installing BizTalk 2010 RTM. I decided to try to install onto the same VM where I had installed BizTalk 2010 Beta. Usually this has worked for me in the past unless there was some uninstall problem. For the timid, please start with a clean VM image.

Challenges

Here are the things you should do if you are installing on a VM where BizTalk existed already:

  1. Remove all existing installs with BizTalk in the name.
  2. Remove Microsoft Enterprise Single Sign-On
  3. Remove all existing BizTalk databases
  4. Remove all existing BAM DTS packages – Open SQL Management Studio under Integration Services and check the packages under MSDB. Remove all of them. These are not removed by the uninstaller.
  5. Open the SQL Configuration manager and disable Shared Memory from the connectivity options. *new*

Next  I will give a little extra background and mention a few errors I got.

My first step was to uninstall anything with “BizTalk” in the name. So I did this. I started the installer next and got a message that a previous version of  Microsoft Enterprise Single Sign-On was installed and asked to remove this. I am guessing the version information in the registry must have changed between Beta and RTM. So I allowed it to uninstall and remove the SSO component. It ran a little longer but then I got an error about SSO:

The following platform components failed to install and will need to be manually installed before setup can proceed: Enterprise Single Sign-On Administration.

This problem was a little difficult to understand. I handled the problem by uninstalling SSO before running the BizTalk installer again. I had been installing BizTalk over the virtual network share VMWare provides but apparently this causes a problem for SSO. I copied the install files locally and then it no longer had a problem.

Another thing I found out changed with SSO between Beta and RTM is that now it is not acceptable for the SSO install to use the Shared Memory SQL connectivity option. Perhaps this is a security fix. Here is the error I got when trying to setup SSO on BizTalk 2010 with Shared memory enabled:

Failed to create the SQL database ‘SSODB’ on SQL Server ‘<machine name>\<instance name>’ (with SSO Administrator account ‘SSO Administrators’). (SSO)
For help, click: http://go.microsoft.com/fwlink/events.asp?ProdName=Microsoft+BizTalk+Server+2010&ProdVer=3.9.469.0&EvtSrc=SSO&EvtID

ADDITIONAL INFORMATION:
(0xC0002A21) An error occurred while attempting to access the SSO database.
(SSO)
For help, click: http://go.microsoft.com/fwlink/events.asp?ProdName=Microsoft+BizTalk+Server+2010&ProdVer=3.9.469.0&EvtSrc=SSO&EvtID

An error occurred while attempting to access the SSO database. See the event log (on computer ‘<machine name>’) for more details.
(SQL: 0x000000E9: A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.)) (SSO)

For help, click: http://go.microsoft.com/fwlink/events.asp?ProdName=Microsoft+BizTalk+Server+2010&ProdVer=3.9.469.0&EvtSrc=SSO&EvtID

I had Shared Memory enabled when installing the BizTalk 2010 beta. This is actually now documented in the BizTalk install guide (I saw it in the Win 7 one): http://www.microsoft.com/downloads/details.aspx?FamilyID=b77d6a4f-8b41-470e-a58c-730dc5859b38. I also saw this in a good visual walkthrough BizTalk 2010 install guide made by Jay Kladiva at http://www.microsoftconnectedsystems.net/downloads/BizTalk%202010%20Installation%20and%20Configuration%20Guide%20-%20Development%20and%20Test%20Build.docx.

It is interesting that Shared Memory actually has to be disabled because enabling Shared Memory or Named Pipes is typically the remedy for errors like this. I am guessing when the BizTalk installer sees this option for connectivity and tries to use it.

Once I get through the rest of the installation I will post back here if I encounter any other issues. Thanks!

,

1 Comment

Failed to Enable constraints in Admin Console

I was having trouble on my DEV server today and got a failed to enable constraints error when trying to expand the Applications node. This is on a Windows Server 2008 R2, BizTalk 2009, SQL 2008 server with BizTalk running on a separate box than SQL but on a VM. I was running a heavy Commerce Server import process and the available memory was getting really low. Here is a screenshot of the error:
 
 
It was pretty worrisome when it happened because I was trying to get a big mission critical process running and it is hard to get much done without using the Admin console. I had reported this issue during the BizTalk 2009 beta but was unable to reproduce it again after the fixes were implemented so I assumed the issue was resolved. I think the problem becomes more likely to occur under low memory conditions.
 
I had seen Nick Heppelston’s post at http://www.modhul.com/tag/admin-console/ and wanted to post that his process worked fine for me. I stopped all of my BizTalk hosts from the Windows Service Control Manager and then stopped both the Distributed Transaction Coordinator (DTC) and the Windows Management Instrumentation (WMI) Windows services. I had to restart the BizTalk admin console in order to reload everything – there was not an automatic refresh. Nick had reported this as a fix for BizTalk 2006 so I wanted to confirm that this process worked successfully on BizTalk 2009 as well.
 
Thanks,

,

Leave a comment

Top 10 BizTalk Hotfixes

I was at the MVP Summit just a few weeks ago and wanted to help publicize a few important fixes coming out of Redmond that can help your BizTalk development. Some hotfixes get combined into a rollup and others do not. Also, for some reason not all of the fixes make it into the KBAlertz that I subscribe to so you should periodically check around like at the BizTalk Customer Response Team blog (http://blogs.msdn.com/biztalkcrt/default.aspx).
 
I was thinking through all of the recent ones I had downloaded and realized these would make a good top ten list. 🙂 I will make some comments along the way. Be sure to watch which version I reference because not all fixes apply to both 2006 R2 and 2009. Another good tip is to save the email for your hotfix password with your hotfix so you have the password for later. Passwords on hotfixes expire, saved emails do not. 🙂 Just make sure to encrypt that hotfix password file…
 
Here is the list (in reverse for fun):
 
10. To start off with, I strongly recommend you download BizTalk 2006 R2 SP1 because it includes so many hotfixes all rolled up: http://support.microsoft.com/default.aspx/kb/974563.
 
9. On the forums I recently helped handle a situation for someone where the MQSC adapter was not working for 64-bit on BizTalk 2009. A hotfix had been released which overcame this problem on BizTalk 2006 R2 SP1: http://support.microsoft.com/default.aspx/kb/939202 The forum poster went ahead and installed the hotfix on their BizTalk 2009 server and it overcame the issue anyway, even though the hotfix was only targeted for BizTalk 2006 R2 SP1. Sometimes you can check the released version information on the hotfix to see if it matches with your server, but other times you just do not know the result of installing an older hotfix on a newer system.
 
8. The next one provides the software for installing SQL Notification Services with BizTalk 2009: http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=953752.
 
7. The BizTalk team released the HIPAA 5010 extension pack earlier this year as part of a hotfix: http://support.microsoft.com/kb/973415
 
6. When I worked on an EDI project there was an annoying problem where the GS02 and GS03 identifiers would get mixed up during the BizTalk EDI processing. This was resolved here: http://support.microsoft.com/kb/954557
 
5. This one is only really useful if you are working with VS 2010 RC and BizTalk vNext – Get intellisense back – http://support.microsoft.com/kb/980610. Hopefully it gets applied in VS 2010 RTM and does not need to come to the world as a Windows Update.
 
4. On BizTalk 2009, UDDI support only works on the default instance of SQL Server. Silly! There is a fix at this point for 32-bit but not 64-bit: http://support.microsoft.com/kb/975684.
 
3. If you have been using BizTalk 2009, you may have noticed some issues in VS when compiling and developing. I think these issues were introduced when the VS BizTalk extensions were updated to work with BizTalk 2009. Some people in the MVP community helped Microsoft identify and test out these issues. There are two fixes that are relevant to mention: http://support.microsoft.com/kb/977428. I did not personally have this problem, but many expressed this was a problem for them.
 
2. The other VS issue for BizTalk 2009 hotfix was resolved with http://support.microsoft.com/kb/979153. I encountered this one many times on larger projects, especially when using expression shapes in orchestrations.
 
1. Now for a really good one that everyone should install. I frequently encounter red herrings in doing diagnostic work and I am excited when hotfixes come out that actually solve the root problem. Here is one that clears up some compilation issues that surface as out of memory exceptions: http://support.microsoft.com/default.aspx/kb/975118. This is a good one to install on both developer and server boxes.
 
Happy hotfixing!
 

, ,

Leave a comment

WCF-SQL Adapter’s Database Schema Gotcha

 
Introduction
 
On a recent project I was working with BizTalk 2009 and SQL 2008 and the customer was interested in moving some database objects to a new SQL database schema (for example, from dbo to MySchema). In this post, I refer to types of schemas: BizTalk schemas (XSDs) and database schemas (SQL security mechanism).
 
I had been interacting with these database objects using the WCF-SQL adapter from the BizTalk Adapter pack. Typically, BizTalk’s port abstraction will take out all description of the physical connectivity details and provide you with a connection independent message type. Connectivity details are stored in the port properties and persisted as binding information. Through the use of the Adapter pack wizard, BizTalk schemas are created for interacting with various adapter data sources such as with the WCF-SQL adapter and SQL databases.
 
Initial Observations
 
So initially I expected the database schema change would not be a problem because the database object details were all specified as properties of the port. I modified the following port properties to point to the new schema, expecting this to work:
 
  • BizTalk WCF-Custom SOAP Action Headers such as TypedProcedure/dbo/MyProcCall to TypedProcedure/MySchema/MyProcCall
  • BizTalk binding properties with SQL information such as the properties notificationStatement, pollingStatement, or polledDataAvailableStatement, etc.

I restarted my BizTalk application and hosts and I started getting errors that referred to my old database schema. At this point I realized that the database schema is actually hard coded during the BizTalk schema generation process. This is a significant gotcha; that the changing of the database schema for custom objects that BizTalk uses requires a regeneration of the BizTalk schemas. The next section describes the steps I went through to correct the old database schema references after moving my database objects to the new database schema:

Resoluton

In addition to the deployment steps done above, I had to update several of my BizTalk solution artifacts. For my BizTalk solution, I was working with 5 different database objects and I did not want to delete all of my BizTalk schema files (approx. 15 different files) and go through the adapter wizard so many times. I decided to opt for a find and replace solution instead. Here are the steps I did to replace the old database schema references:

  • Open up the base and referenced BizTalk schemas using the Open With…, XML Editor. If you are interacting with rows of data then you may have separate referenced schema files that may also have the old schema in them.
  • Do find/replace and search for "/dbo" or whatever old database schema you are replacing. The "/" comes from the SOAP Action Header.
  • Save the schema changes.
  • Once you replace one database schema reference, your BizTalk schema will be in an inconsistent state until all of the database schema references have been updated. Try opening the BizTalk schema in the BizTalk schema designer and if you get a warning when opening you will know that there are still old database schema references to migrate. When all of the old database schema references are replaced you will no longer get a warning
  • Recompile
  • Then investigate the BizTalk map and orchestration files because these also contain hard coded references to the old database schema.
  • It would seem that a recompile of your BizTalk solution would regenerate the .cs files for the BizTalk maps and orchestrations, but I found this to not be the case. An approach I found that worked was to open any BizTalk maps and make an insignificant change (add a functoid and then remove it) just to change the file status to edited and then to save the map. This would refresh the generated map code.
  • Also, I opened all of my BizTalk orchestrations and double-clicked all of the Transform shapes and then checked the button "Open the map when I click the OK button" to change the file status to edited. This refreshed the generated orchestration code. Even though no actual changes were made the generated code was refreshed.
  • You could alternately remove all of the generated files such as those named like *.odx.cs or *.btm.cs
  • One disadvantage of this find & replace technique is that your old generated binding files will still refer to the old database schema so be sure to discard those.

Conclusion

This post has shown that a change to the database schema for SQL objects can have a major ripple effect on a BizTalk solution that uses the WCF-SQL adapter. I do not expect that changes to database schemas occurs very often so you may never encounter this but if you do, be prepared for a little bit of work to get the updated database schema names back into your solution. Thanks for reading!

, ,

1 Comment