Update all binding thumbprints

We have 20+ applications and have to update to a new certificate. To avoid having to do a new build and release of all of these applications, some of which haven't been updated for some time, I chose to create a powershell script to update all send ports on the fly.

The script does not stop or start host instances. This could easily be incorporated; check my other blog entry on starting and stopging host instances.

The script uses the BizTalk ExplorerOM to access the settings which means nothing extra needs to be installed on the BizTalk servers.

This script looks long because it includes so much confirmation in the way of output for testing before the final run. The real logic is only 8 lines, including 4 lines of variable declarations.

This example changes two thumbprints at once. It could easily be modified up or down.

$oldClientCert = "ee aa bb 11 22 33 44 55 66 77 88 99 00 ff dd cc ab cd ef 01"
$newClientCert = "ne wt hu mb pr in tg oe si nh er e0 00 00 00 00 00 00 00 00"
$oldServiceCert = "aa bb cc dd ee ff 00 11 22 33 44 55 66 77 88 99 12 23 34 56"
$newServiceCert = "34 2a 15 53 3e 7d 6a 0c 51 20 e4 50 6b 53 df 72 84 55 aa 6a"
  
[void] [System.reflection.Assembly]::LoadWithPartialName("Microsoft.BizTalk.ExplorerOM")  
$Catalog = New-Object Microsoft.BizTalk.ExplorerOM.BtsCatalogExplorer  
$Catalog.ConnectionString = "SERVER=DBINSTANCENAME;DATABASE=BizTalkMgmtDb;Integrated Security=SSPI"

#EnumerateSendPorts $Catalog  
 $port = $catalog.SendPorts[1]
 Write-host "B4 ----> " $port.PrimaryTransport.TransportTypeData

 $catalog.SendPorts | % {

# Line below replaces thumbprints  - UPDATES ORIGINAL VALUE - BUT NO SAVE
$_.PrimaryTransport.TransportTypeData=_
($_.PrimaryTransport.TransportTypeData.Replace($oldServiceCert,$newServiceCert)).Replace($oldClientCert,$newClientCert);

# Line below replaces thumbprints and prints out the new TransportTypeData string - NO UPDATE TO ORIGINAL VALUE
#($_.PrimaryTransport.TransportTypeData.Replace($oldServiceCert,$newServiceCert)).Replace($oldClientCert,$newClientCert);
   }
$port = $catalog.SendPorts[1] 
Write-host "After ----> " $port.PrimaryTransport.TransportTypeData

#No changes are saved until the following line is run
$Catalog.SaveChanges(); 

When testing comment out the the last line to skip saving the updates. To just output the updates, comment out the row updating the original value and uncomment the No Update line.

Don't forget to change the connection string to point to the correct management database instance!

Blog software may force some line breaks - and I added one underscore (_) to indicate I broke the line there.

BizTalk - comparing bindings files with excel source

We dynamically generate BizTalk bindings during in our build process. The process uses a binding file and replaces urls and thumbprints with values from a an excel spreadsheet.

The process for updating both of these files is manual so it is natural to find they get out of synch. Manually browsing a binding file is not an easy task so I wrote a powershell script that extracts and compares portnames between the two files.

In our case only the sendports get dynamically updated.
First extract the portnames from the binding file:

  $BindingFileName ="D:\bindings\App1~Binding~Template.xml"
  if ( -not (Test-Path -path $BindingFileName) ){
      "Can't Find"
      $BindingFileName
 exit
 } 
# Load binding information from file
$Bindings = [xml](get-content $BindingFileName)

# extract the ports
$BindingPorts = select-xml $Bindings -xpath "//SendPort" 

# get just the port names
$BindingPortNames = $BindingPorts | foreach { $_.node.Name } | sort-object –Unique 

Now that I have a sorted list of ports from the binding file, I need to get the ports from the excel file. Excel does not need to be installed on the server but the Excel drivers for oledb do need to be installed. Note: This driver is a 32 bit driver. use the -runas parameter or run in the x86 ISE.

$ExcelFileName = "D:\excel\App1~PortConfig.xls"
 
$OleDbConn = New-Object "System.Data.OleDb.OleDbConnection"
$OleDbCmd = New-Object "System.Data.OleDb.OleDbCommand"
$OleDbAdapter = New-Object "System.Data.OleDb.OleDbDataAdapter"
$DataTable = New-Object "System.Data.DataTable"

$OleDbConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""$ExcelFileName"";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
$OleDbConn.Open()

$OleDbCmd.Connection = $OleDbConn
$OleDbCmd.commandtext = "Select Port from [SENDPORT_WCF$]"
$OleDbAdapter.SelectCommand = $OleDbCmd

$RowsReturned = $OleDbAdapter.Fill($DataTable)
$OleDbConn.Close()

#Output something so user sees something is happening 
ForEach ($row in $DataTable) {
Write-host $row.Port
}

$ExcelPortNames = $DataTable | foreach {$_.Port} | sort-object –Unique

The excel file has different tabs for different send ports. This line:

$OleDbCmd.commandtext = "Select Port from [SENDPORT_WCF$]"

specifies which column (Port) to select from which sheet [SENDPORT_WCF$]. 

The final step is to compare the two lists of portnames.

Compare-Object $ExcelPortNames $BindingPortNames -includeequal | ft inputobject, @{n="file";e={ if ($_.SideIndicator -eq '=>') { "binding" } else {if ($_.SideIndicator -eq '<=')  { "excel" } else {"=="}} }} | Out-File "d:\temp\Bank 2.0 bindings comparison.txt" 

Note that this is one long line that has been wrapped by the blog software.
The final result is a table that looks like this:

InputObject         file   
-----------         ----   
WcfSendPort_app1 ==     
WcfSendPort_app2 ==     
WcfSendPort_app3 ==     
WcfSendPort_appx binding
WcfSendPort_appy binding
WcfSendPort_appz binding
WcfSendPort_app1z excel  
WcfSendPort_app2z excel  

I'll leave it as an exercise for the reader to process a whole directory full of bindings files. :). One could also add parameters and send the file names on the comand line.

The complete code:

#Param(
#  [string]$BindingFileName
#)

#Binding files
  $BindingFileName ="D:\bindings\App1~Binding~Template.xml"
  if ( -not (Test-Path -path $BindingFileName) ){
      "Can't Find"
      $BindingFileName
 exit
 } 
 
# Load binding information from file
$Bindings = [xml](get-content $BindingFileName)

# extract the ports
$BindingPorts = select-xml $Bindings -xpath "//SendPort" 

# get just the port names
$BindingPortNames = $BindingPorts | foreach { $_.node.Name } | sort-object –Unique 
 
#EXCEL 
 
$ExcelFileName = "D:\excel\App1~PortConfig.xls"
 
$OleDbConn = New-Object "System.Data.OleDb.OleDbConnection"
$OleDbCmd = New-Object "System.Data.OleDb.OleDbCommand"
$OleDbAdapter = New-Object "System.Data.OleDb.OleDbDataAdapter"
$DataTable = New-Object "System.Data.DataTable"

$OleDbConn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""$ExcelFileName"";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
$OleDbConn.Open()

$OleDbCmd.Connection = $OleDbConn
$OleDbCmd.commandtext = "Select Port from [SENDPORT_WCF$]"
$OleDbAdapter.SelectCommand = $OleDbCmd

$RowsReturned = $OleDbAdapter.Fill($DataTable)
$OleDbConn.Close()

#Output something so user sees something is happening 
ForEach ($row in $DataTable) {
Write-host $row.Port
}

$ExcelPortNames = $DataTable | foreach {$_.Port} | sort-object –Unique

#COMPARISON

Compare-Object $ExcelPortNames $BindingPortNames -includeequal | ft inputobject, @{n="file";e={ if ($_.SideIndicator -eq '=>') { "binding" } else {if ($_.SideIndicator -eq '<=')  { "excel" } else {"=="}} }} | Out-File "d:\temp\App1_bindings_comparison.txt"