Dangers when upgrading the Codeplex SFTP adapter

I encountered some dangers that one can run into when upgrading the BizTalk SFTP adapter that can be found on Codeplex.

On one installation where the adapter is used did the issue of the adapter stop working frequently arise. When the issue was fixed in a subsequent version of the adapter, it was upgraded, but the problem remained.

When I looked at the server, I noticed that the adapter never had got upgraded, even though they very well installed the new version. I then replicated what I believe to be the steps taken during the upgrade in order to fully understand what had happened.

I learned that when upgrading version 1.3.3 to 1.4.0, it is necessary to completely uninstall the previous version before installing the new. This was never done during the upgrade process. The old version remained installed and the setup program for the new version reported back that the installation was successful. The BizTalk administrator never noticed the dual entries in the Add/Remove programs window nor the old timestamps in the adapter installation folder (which was how I noticed it from the beginning).

Uninstalling the two adapter entries in the control panel and then reinstalling the new version solved the issue as can be seen in the installation directory.

I then also noticed that when properly upgrading the adapter, receive locations bound to the SFTP adapter will not start unless you for each and every one open the adapter properties windows and save it. This is due to a difference in the properties that will make the bindings fail otherwise, rendering the port to shut down.

BizTalk 2010 and Dynamics AX 4.0

We just did a quick check for a client investigating the possibility to upgrade their BizTalk 2006 platform to the latest (2010) version. The main issue was whether the vast amount of integrations to their Ax 4.0 system would still work.

Some of the integrations are made using the Ax AIF adapter in BizTalk and while their is a lot of information on the web that doesn't say that BizTalk 2010 can work with the Ax 4.0 adapter, there is not a single document that explicitly says that the two are incompatible even though you more or less can assume so based on the information available. Mainly because BizTalk 2010 requires Windows Server 2008 and the adapter is not supported on this platform. The adapter is not supported on 64-bit systems at all and I doubt there is many admins looking at installing a fresh new integration platform today and not putting it on a 64-bit Windows.

We checked with Microsoft just to be sure and received the answer that BizTalk 2010 and Ax 4.0 cannot be integrated using the adapter. Not even Ax 2009 is fully supported by BizTalk 2010 as of today which is an interesting fact. Instead are we investigating the amount of work needed to change the adapter based flows to MSMQ integrations instead. The upgrade of the integration platform is more important than to keep an outdated adapter working..

BizTalk 2010 certification

I've been waiting for a new exam for BizTalk to appear since the "current" one has been for 2006r2 for quite a while. Today I took a new look and found that Exam 70-595: Developing Business Process and Integration Solutions by Using Microsoft BizTalk Server 2010 is available. The old exams for 2006 and 2006r2 are set for retirement this summer.

Time to update the skills that I rarely (or never) use such as EDI and RFID and then it's off to take the test.

Recycle IIS application pools using PowerShell

I have been working on a collection of deployment scripts for BizTalk solutions using PowerShell. One functionality is installation of services hosted in the IIS. After installation, the application pool need a restart in order to properly pick up the new/updated services.

At first, I just recycled all available application pools by the following command line

& $env:windir\system32\inetsrv\appcmd list apppools /xml | & $env:windir\system32\inetsrv\appcmd recycle apppools /in

First we list all available application pools and then we pipe this list as input into the recycle command.

While nice, it is unnecessary to recycle those application pools that are unaffected by our installation.

The main finesse in my scripts are that they are picking up all information on what to do from a configuration file. This XML file includes among other things the application pool for each service to install in IIS. Based on this I updated my recycle command to recycle each and every application pool that were specified in the configuration file. In order to not restart an application pool more than once, I pipe the foreach with sort-object and get-unique.

# List to hold the apppools
$appPools = New-Object System.Collections.ArrayList

# Loop through the objects in the xml file
# and extract the apppool name and add to the list
ForEach($wcfSetup in $xmlFile.DeployConfiguration.WcfSetups.WcfSetup)
{
    [void]$appPools.Add($wcfSetup.ApplicationPool.Trim())
}

# Recycle each unique apppool in the list
foreach($appPool in $appPools | sort-object | get-unique)
{
    & $env:windir\system32\inetsrv\appcmd recycle apppool /apppool.name:"$appPool"
}

Creating a multidimensional strongly typed array in Powershell

When getting stuck for a short while trying to figure out how to create a strongly typed multidimensional array in Powershell I tried to find an example on the net just to find that there simply just is no example of it to be found. Maybe it's too easy? The Technet page on the New-Object cmdlet gave me what I needed.

The way I later on created my two dimensional array was like this:

$d = New-Object 'Object[,]' 10, 20

This is however created as an Object array while I needed int. I changed the code to this:

$d = New-Object 'Int32[,]' 10, 20

Then I thought that it should be possible to streamline it a bit so I got this:

$d = Int32[,] 10, 20

Simple enough. When I see it, I wonder why I couldn't figure it out quicker.

Recycle application pools using appcmd.exe in IIS 7

While hacking along on some deployment scripts today I encountered an issue when I needed to recycle specific application pools via code.

In IIS 7, appcmd.exe was added to enable a programmatic way of interacting with the IIS. On Technet, the syntax to recycle an app pool is as follows:

appcmd recycle apppool /apppool.name: string

This will however result in the following error message:

ERROR ( message:The attribute "apppool.name" is not supported in the current command usage. )

Which can feel a bit strange. I then noticed that the article on Technet has a typo and the space between /apppool.name: and the name of the application pool should not be there. Without it the command will execute perfectly.

As an addition, application pools with spaces in their names can be recycled by simply wrapping the name in quotes as so:

appcmd recycle apppool /apppool.name:"My Application Pool Name" 

I added a comment about the issue in the Technet article for others with the same issue. I guess there is not a way to submit corrections to articles other than comments?

Calling stored procedures from BizTalk (and other applications) and the FMTONLY flag

Most developers will run into the issue of not being able to generate metadata from a stored procedure even though it is perfectly valid and can be accessed and run without a hitch from the SQL Server Management Studio or directly from code. A lot of the time there will be an error message on the lines of

Error while retrieving or generating the WSDL. Adapter message: Retrieval of Operation Metadata has failed while building WSDL at 'TypedProcedure/dbo/FetchTestData'

Microsoft.ServiceModel.Channels.Common.MetadataException: Retrieval of Operation Metadata has failed while building WSDL at 'TypedProcedure/dbo/FetchTestData' ---> System.Data.SqlClient.SqlException: Invalid object name '#temp01'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
...

In some cases there will be no error at all (SSIS is prone to this for example).

The above error message will give us something to go on though. When reviewing the stored procedure we can see that a temporary table is created and filled with data. Later on this table is used in a select in order to return data to the client. Nothing unusual, still it fails. The stored procedure looks like this (never mind the necessity of the temp table, it is just a demo of the issue):

ALTER PROCEDURE [dbo].[FetchTestData]
(@a4 varchar(4))
AS
SELECT t1, t2, t3 INTO #temp01 FROM Table_1
SELECT t1, t2, t3 FROM #temp01
RETURN

The next step is then to run profiler during a run of the generate wizard to see what is actually happening in the background. How does the metadata get generated by the wizard and why does it fail?

When running the generation, we can see the following bits in the trace

SET FMTONLY OFF; SET FMTONLY ON;
exec [dbo].[FetchTestData] @a4=NULL
SET FMTONLY OFF;

Now we are getting somewhere. The FMTONLY setting is used in order to not process any rows but just return response metadata to the client. However, our stored procedure uses a temporary table which FMTONLY=ON will cause to not be created since any modifications are allowed to be made. When trying to do a select on the temporary table, it will fail since it never was created causing the error messages mentioned above.

There is a way around this issue though. Since we know what is happening, we can revert the execution of SET FMTONLY ON that the adapter does before the execution of the procedure. We should however not just add a command of SET FMTONLY OFF to the beginning of our procedure. Actually executing the entire procedure which such a solution will result in might not be a good choice. If we only do a select on data it is fine, but if the procedure also includes insert, update and delete statements, these will be called as well.

Instead, we check for the FMTONLY flag early on and if it is set, we switch it off when needed and then switch it back on again. Our modified and metadata-generation-secure procedure now look like this:

ALTER PROCEDURE [dbo].[FetchTestData]
(@a4 varchar(4))
AS
DECLARE @FmtOnlyIsSet bit = 0
IF (1=0) BEGIN SET @FmtOnlyIsSet = 1 END
IF @FmtOnlyIsSet = 1
   SET FMTONLY OFF

SELECT t1, t2, t3 INTO #temp01 FROM Table_1

IF @FmtOnlyIsSet IS NULL
   SET FMTONLY ON

SELECT t1, t2, t3 FROM #temp01

RETURN

What magic is done here?

First, we declare a variable that can hold the current setting for the FMTONLY flag, we call it @FmtOnlyIsSet and set it to false by default.

The IF (1=0) bit may look a bit off, but is in fact quite clever. When FMTONLY is set to ON, all conditional statements will be ignored but the content will be executed. This since all possible return paths has to be checked. By checking for an impossible match (1=0) we can be sure that the statement inside the IF will be run only if FMTONLY is ON. Hence we set our FMTONLY flag to true here.

Then we simply check if our flag is set to true when needed and if so, switch off the FMTONLY setting. Afterwards we do the same check and switch it back on. This part is important due to what I mentioned above. If we don't switch the FMTONLY setting back on, all statements will be run just as in a normal execution of the procedure which might not be wanted.


It is noteworthy to know that the FMTONLY setting not only will be used during metadata generation in development, but also when actually calling the procedure from the application. I noticed this when using typed datasets in BizTalk server with the WCF-SQL adapter. I couldn't do alterations to the stored procedure which I handled by instead doing a mock procedure to generate schemas from. Then I assumed that I could safely call the original procedure from BizTalk but I still got the invalid object error message. A quick look in Profiler showed that the adapter will do two passes to the stored procedure. First once with FMTONLY set to ON and then one without to actually execute the code.

My guess is that the adapter is smart enough to do a check that the signature for the procedure matches the previously generated and deployed metadata before executing code that could change data in the database. If the returned dataset wouldn't match the schema, we would know before any code has been executed.

I have only seen this when using typed datasets though which make sense. By using the technique described above, it isn't an issue at all. I'd rather like the idea that the contract is checked first before executing the procedure. It is also not posing a performance hit. The result from the metadata extraction will be cached so only the first call will need to fetch metadata. I am still not sure for how long the metadata is cached before it is refreshed in a call but it seems to hold it for quite a while.

BizTalk in the cloud - Integration as a service

I totally missed it, but a rough two weeks ago it was published on the BizTalk Server Team Blog that the future is in the cloud.

When I attended the European BizTalk Conference and the sessions based on the book Applied Architecture Patterns on the Microsoft Platform, I got the feeling that Azure is something I should start working with. Now I'm sure that it is so. Especially since the BizTalk/Azure hybrid will be relased as a CTP sometime during the spring/summer of 2011.

As I concluded during the conference, it is at this point (and in the nearest future) not a replacement we have in front of us, but an addition of tools to build solutions with. The same is written by Daniel Probert in his blogpost on the subject.

I'm also happy to see that the cloud is not viewed as the "solution to everything". The future integration platform from Microsoft will be offered as an on-premises product based on AppFabric. I'm looking forward to this since I believe it will solve a lot of problems I'm facing today regarding complex low-latency processes that will work extremely well in an AppFabric on-site platform.

So regarding integration in the future, we now have a pretty clear direction to head in and I believe after reading the announcement from Microsoft that it is the right path. Since most of the platform is going to the cloud, so should also the integration, while still having an option to keep things off-cloud if security, performance and other requirements dictate so.

Error when importing bindings: "Failed to update binding information."

When importing bindings into a BizTalk application, the following error message might appear:

TITLE: Import Bindings
------------------------------
Failed to update binding information. (mscorlib)
------------------------------
ADDITIONAL INFORMATION:
Cannot update send port "MoAGSendPort". (Microsoft.BizTalk.Deployment)

Cannot update transport information (address "C:\temp\SHS\ut\1.mb510i1_%SourceFileName%"). (Microsoft.BizTalk.Deployment)

The following items could not be matched up to hosts due to name and/or trust level mismatches:
Item: 'FILE' Host: 'SendHost' Trust level: 'Untrusted'
You must do one of the following:
1) Create hosts with these names and trust levels and try again
2) Re-export the MSI without the binding files and have a post import script apply a suitable binding file. (Microsoft.BizTalk.Deployment)

While the message might be correct regarding the host name or trust level, a more common reason to the failure is that the host doesn't have an adapter handler specified that matches the bindings.

In the Admin Console, browse to BizTalk Server 2009 Administration > BizTalk Group > Platform Settings > Adapters and then look at the adapter mentioned in the message. In my case it says "Item: FILE", so the File adapter is where I'm heading and it is indeed missing a send handler for the SendHost host.

To add a send handler, right-click on the adapter (or in the detail view of the window) and select New > Send Handler...

Then select the host that needed the specific send handler and click Ok.

Restart the host instance in question and then try to import the binding again.

WCF exception Could not establish trust relationship for the SSL/TLS secure channel with authority 'server:port'

I was recently working with some WCF services using the wshttp binding and therefore calling them over SSL. I had a certificate set up, but when trying to browse the wsdl in my test client, I couldn't browse the metadata. In the eventlog, I found the following error messages.

Exception Information Type[SecurityNegotiationException] Source[mscorlib] Message[Could not establish trust relationship for the SSL/TLS secure channel with authority 'server:port'.]

Exception Information Type[WebException] Source[System] Message[The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.]

Exception Information Type[AuthenticationException] Source[System] Message[The remote certificate is invalid according to the validation procedure.]

The next step was to try to browse the wsdl in Internet Explorer, where I got this:

Now it made sense. I tried to browse the service using an endpoint URL of https://localhost:11001/path when the certificate I was using was issued to the actual server name as can be seen both in Internet Explorer when checking the certificate information as well as in the MMC Certificate snap-in.

In other words, even if localhost and my servers full name can be used interchangeably in most cases, it isn't so when we are talking about security certificates where the server name is quite vital. After switching to the correct endpoint URL, in my case https://server1.domain.com:11001/path, it worked as expected.

It should be noted that there is a way of bypassing the certificate verification in the client by setting the ServerCertificateValidationCallback property as below:

using System.Net.Security;
using System.Security.Cryptography.X509Certificates;

System.Net.ServicePointManager.ServerCertificateValidationCallback += delegate(object sender, X509Certificate cert, X509Chain chain, SslPolicyErrors sslError)
{
return true;
};

This is however quite dangerous and should not be used in production code. A good practice if code as this is used is to wrap it in #if DEBUG statements to keep it from getting to production (but then you risk having all tests go through without any problems and have a hard to find error in the production environment that cannot be replicated in test).