Timeout Errors ‘Extracting Schema’ when running SQLPackage for a Migration to Azure DevOps Services

The Problem

Whilst doing a migration from an on-premised TFS to Azure DevOps Services for a client I had a strange issue with SQLPackage.exe.

I had previously completed the dry run of the migration without any issues and started the live migration with a fully defined process and timings for a each stage.

When I came to export the detached Team Project Collection DB I ran the same command as I had for the dry run

& "C:\Program Files\Microsoft SQL Server\150\DAC\bin\SqlPackage.exe" /sourceconnectionstring:”Data Source=localhost\SQLExpress;Initial Catalog=Tfs_DefaultCollection;Integrated Security=True” /targetFile:C:\temp\Tfs_DefaultCollection.dacpac /action:extract /p:ExtractAllTableData=true /p:IgnoreUserLoginMappings=true /p:IgnorePermissions=true /p:Storage=Memory 

I had expected this to take around 30 minutes. However it failed after 10 minutes with an error when trying to export the schema ‘Timeout, cannot reconnect to the database’.

This was strange as nothing had changed on the system since the dry-run. I tried all of the following with no effect

  • Just running the command again, you can hope!
  • Restarted SQL and ran the command again
  • Tried the export from SQL Management Studio as opposed to the command line , this just seems to hang at the same point.

The Solution

What resolved the problem was a complete reboot of the virtual machine. I assume the issue was some locked resource, but not idea why.

Fix for ‘System.BadImageFormatException’ when running x64 based tests inside a Azure DevOps Release

This is one of those blog posts I write to remind my future self how I fixed a problem.

The Problem

I have a release that installs VSTest and runs some integration tests that target .NET 4.6 x64. All these tests worked fine in Visual Studio. However, I got the following errors for all tests when they were run in a release

2020-04-23T09:30:38.7544708Z vstest.console.exe "C:\agent\_work\r1\a\PaymentServices\drop\testartifacts\PaymentService.IntegrationTests.dll"

2020-04-23T09:30:38.7545688Z /Settings:"C:\agent\_work\_temp\uxykzf03ik2.tmp.runsettings"

2020-04-23T09:30:38.7545808Z /Logger:"trx"

2020-04-23T09:30:38.7545937Z /TestAdapterPath:"C:\agent\_work\r1\a\PaymentServices\drop\testartifacts"

2020-04-23T09:30:39.2634578Z Starting test execution, please wait...

2020-04-23T09:30:39.4783658Z A total of 1 test files matched the specified pattern.

2020-04-23T09:30:40.8660112Z   X Can_Get_MIDs [521ms]

2020-04-23T09:30:40.8684249Z   Error Message:

2020-04-23T09:30:40.8684441Z    Test method PaymentServices.IntegrationTests.ControllerMIDTests.Can_Get_MIDs threw exception:

2020-04-23T09:30:40.8684574Z System.BadImageFormatException: Could not load file or assembly 'PaymentServices, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. An attempt was made to load a program with an incorrect format.

2020-04-23T09:30:40.8684766Z   Stack Trace:

2020-04-23T09:30:40.8684881Z       at PaymentServices.IntegrationTests.ControllerMIDTests.Can_Get_MIDs()

2020-04-23T09:30:40.9038788Z Results File: C:\agent\_work\_temp\TestResults\svc-devops_SVRHQAPP027_2020-04-23_10_30_40.trx

2020-04-23T09:30:40.9080344Z Total tests: 22

2020-04-23T09:30:40.9082348Z      Failed: 22

2020-04-23T09:30:40.9134858Z ##[error]Test Run Failed.

Solution

I needed to tell vstest.console.exe to run x64 as opposed to it’s default of x32. This can be done with a command line override –platform:x64

image

Swapping my Azure DevOps Pipeline Extensions release process to use Multistage YAML pipelines

In the past I have documented the build and release process I use for my Azure DevOps Pipeline Extensions and also detailed how I have started to move the build phases to YAML.

Well now I consider that multistage YAML pipelines are mature enough to allow me to do my whole release pipeline in YAML, hence this post.

image

My pipeline performs a number of stages, you can find a sample pipeline here. Note that I have made every effort to extract variables into variable groups to aid reuse of the pipeline definition. I have added documentation as to where variable are stored and what they are used for.

The stages are as follows

Build

The build phase does the following

  • Updates all the TASK.JSON files so that the help text has the correct version number
  • Calls a YAML template (build-Node-task) that performs all the tasks to transpile a TypeScript based task – if my extension contained multiple tasks this template would be called a number of time
    • Get NPM packages
    • Run Snyk to check for vulnerabilities – if any vulnerabilities are found the build fails
    • Lint and Transpile the TypeScript – if any issue are found the build fails
    • Run any Unit test and publish results – if any test fail the build fails
    • Package up the task (remove dev dependencies)
  • Download the TFX client
  • Package up the Extension VSIX package and publish as a pipeline artifact.

Private

The private phase does the following

  • Using another YAML template (publish-extension) publish the extension to the Azure DevOps Marketplace, but with flags so it is private and only assessible to my account for testing
    • Download the TFX client
    • Publishes the Extension to the Marketplace

This phase is done as a deployment job and is linked to an environment,. However, there are no special approval requirements are set on this environment. This is because I am happy for the release to be done to the private instance assuming the build phase complete without error.

Test

This is where the pipeline gets interesting. The test phase does the following

  • Runs any integration tests. These could be anything dependant on the extension being deployed. Unfortunately there is no option at present in multistage pipeline for a manual task to say ‘do the manual tests’, but you could simulate similar by sending an email or the like.

The clever bit here is that I don’t want this stage to run until the new private version of the extension has been published and is available; there can be a delay between TFX saying the extension is published and it being downloadable by an agent. This can cause a problem in that you think you are running tests against a different version of the extension to one you have. To get around this problem I have implemented a check on the environment this stage’s deployment job is linked to. This check runs an Azure Function to check the version of the extension in the Marketplace. This is exactly the same Azure Function I already used in my UI based pipelines to perform the same job.

The only issue here is that this Azure Function is used as an exit gate in my UI based pipelines; to not allow the pipeline to exit the private stage until the extension is publish. I cannot do this in a multistage YAML pipeline as environment checks are only done on entry to the environment. This means I have had to use an extra Test stage to associate the entry check with. This was setup as follows

  • Create a new environment
  • Click the ellipse (…) and pick ‘approvals and checks’
  • Add a new Azure Function check
  • Provide the details, documented in my previous post, to link to your Azure Function. Note that you can, in the ’control options’ section of the configuration, link to a variable group. This is a good place to store all the values, you need to provide
    • URL of the Azure Function
    • Key to us the function
    • The function header
    • The body – this one is interesting. You need to provide the build number and the GUID of a task in the extension for my Azure Function. It would be really good if both of these could be picked up from the pipeline trying to use the environment. This would allow a single ‘test’ environment to be created for use by all my extensions, in the same way there are only a single ‘private’ and ‘public’ environment. However, there is a problem, the build number is picked up OK, but as far as I can see I cannot access custom pipeline variables, so cannot get the task GUID I need dynamically. I assume this is because this environment entry check is run outside of the pipeline. The only solution  can find is to place the task GUID as a hard coded value in the check declaration (or I suppose in the variable group). The downside of this is it means I have to have an environment dedicated to each extension, each with a different task GUID. Not perfect, but not too much of a problem
    • In the Advanced check the check logic
    • In control options link to the variable group contain any variables used.

Documentation

The documentation stage again uses a template (generate-wiki-docs) and does the following

Public

The public stage is also a deployment job and linked to an environment. This environment has an approval set so I have to approve any release of the public version of the extension.

As well as doing the same as private stage this stage does the following

Summary

It took a bit of trial and error to get this going, but I think I have a good solution now. The fact that the bulk of the work is done using shared templates means I should get good reuse of the work I have done. I am sure I will be able to improve the template as time goes on but it is a good start

My Azure DevOps Pipeline is not triggering on a GitHub Pull request – fixed

I have recently hit a problem that some of my Azure DevOps YAML pipelines, that I use to build my Azure DevOps Pipeline Extensions, are not triggering on a new PR being created on GitHub.

I did not get to the bottom of why this is happening, but I found a fix.

  • Check and of make a note of any UI declared variables in your Azure DevOps YAML Pipeline that is not triggering
  • Delete the pipeline
  • Re-add the pipeline, linking to the YAML file hosted on GitHub. You might be asked to re-authorise the link between Azure DevOps Pipelines and GitHub.
  • Re-enter any variables that are declared via the Pipelines UI and save the changes

Your pipeline should start to be triggered again

Experiences setting up Azure Active Directory single sign-on (SSO) integration with GitHub Enterprise

Background

GitHub is a great system for individuals and OSS communities for both public and private project. However, corporate customers commonly want more control over their system than the standard GitHub offering. It is for this reason GitHub offers  GitHub Enterprise.

For most corporates, the essential feature that GitHub Enterprise offers is the use Single Sign On (SSO) i.e. allowing users to login to GitHub using their corporate directory accounts.

I wanted to see how easy this was to setup when you are using Azure Active Directory (AAD).

Luckily there is a step by step tutorial from Microsoft on how to set this up. Though, I would say that though detailed this tutorial has a strange structure in that it shows the default values not the correct values. Hence, the tutorial requires close reading, don’t just look at the pictures!

Even with close reading, I still hit a problem, all of my own making, as I went through this tutorial.

The Issue – a stray / in a URL

I entered all the AAD URLs and certs as instructed (or so I thought) by the tutorial into the Security page of GitHub Enterprise.

When I pressed the ‘Validate’ button in GitHub, to test the SSO settings, I got an error

‘The client has not listed any permissions for ‘AAD Graph’ in the requested permissions in the client’s application registration’

This sent me shown a rabbit hole looking at user permissions. That wasted a lot of time.

However, it turns out the issue was that I had a // in a URL when it should have been a  /. This was because I had made a cut and paste error when editing the tutorial’s sample URL and adding my organisation details.

Once I fixed this typo the validation worked, I was able to complete the setup and then I could to invite my AAD users to my GitHub Enterprise organisation.

Summary

So the summary is, if you follow the tutorial setting up SSO from AAD to GitHub Enterprise is easy enough to do, just be careful of over the detail.

Where did all my test results go?

Problem

I recently tripped myself up whist adding SonarQube analysis to a rather complex Azure DevOps build.

The build has two VsTest steps, both were using the same folder for their test result files. When the first VsTest task ran it created the expected .TRX and .COVERAGE files and then published its results to Azure DevOps, but when the second VsTest task ran it over wrote this folder, deleting the files already present, before it generated and published it results.

This meant that the build itself had all the test results published, but when SonarQube looked for the files for analysis only the second set of test were present, so its analysis was incorrect.

Solution

The solution was easy, use different folders for each set of test results.

This gave me a build, the key items are shown below, where one VsTest step does not overwrite the previous results before they can be processed by any 3rd party tasks such as SonarQube.

steps:
- task: SonarSource.sonarqube.15B84CA1-B62F-4A2A-A403-89B77A063157.SonarQubePrepare@4
   displayName: 'Prepare analysis on SonarQube'
   inputs:
     SonarQube: Sonarqube
     projectKey: 'Services'
     projectName: 'Services'
     projectVersion: '$(major).$(minor)'
     extraProperties: |
      # Additional properties that will be passed to the scanner,
      sonar.cs.vscoveragexml.reportsPaths=$(System.DefaultWorkingDirectory)/**/*.coveragexml
      sonar.cs.vstest.reportsPaths=$(System.DefaultWorkingDirectory)/**/*.trx


… other build steps


- task: VSTest@2
   displayName: 'VsTest – Internal Services'
   inputs:
     testAssemblyVer2: |
      **\*.unittests.dll
      !**\obj\**
     searchFolder: '$(System.DefaultWorkingDirectory)/src/Services'
     resultsFolder: '$(System.DefaultWorkingDirectory)\TestResultsServices'
     overrideTestrunParameters: '-DeploymentEnabled false'
     codeCoverageEnabled: true
     testRunTitle: 'Services Unit Tests'
     diagnosticsEnabled: True
   continueOnError: true

- task: VSTest@2
   displayName: 'VsTest - External'
   inputs:
     testAssemblyVer2: |
      **\*.unittests.dll
      !**\obj\**
     searchFolder: '$(System.DefaultWorkingDirectory)/src/ExternalServices'
     resultsFolder: '$(System.DefaultWorkingDirectory)\TestResultsExternalServices'
     vsTestVersion: 15.0
     codeCoverageEnabled: true
     testRunTitle: 'External Services Unit Tests'
     diagnosticsEnabled: True
   continueOnError: true

- task: BlackMarble.CodeCoverage-Format-Convertor-Private.CodeCoverageFormatConvertor.CodeCoverage-Format-Convertor@1
   displayName: 'CodeCoverage Format Convertor'
   inputs:
     ProjectDirectory: '$(System.DefaultWorkingDirectory)'

- task: SonarSource.sonarqube.6D01813A-9589-4B15-8491-8164AEB38055.SonarQubeAnalyze@4
   displayName: 'Run Code Analysis'

- task: SonarSource.sonarqube.291ed61f-1ee4-45d3-b1b0-bf822d9095ef.SonarQubePublish@4
   displayName: 'Publish Quality Gate Result'

You need to pass a GitHub PAT to create Azure DevOps Agent Images using Packer

I wrote recently about Creating Hyper-V hosted Azure DevOps Private Agents based on the same VM images as used by Microsoft for their Hosted Agent.

As discussed in that post, using this model you will recreate your build agent VMs on a regular basis, as opposed to patching them. When I came to do this recently I found that the Packer image generation was failing with errors related to accessing packages.

Initially, I did not read the error message too closely and just assumed it was an intermittent issue as I had found you sometime get random timeouts with this process. However, when the problem did not go away after repeated retries I realised I had a more fundamental problem, so read the log properly!

Turns out the issue is you now have to pass a GitHub PAT token that has at least read access to the packages feed to allow Packer to authenticate with GitHub to read packages.

The process to create the required PAT is as follows

  1. In a browser login to GitHub
  2. Click your profile (top right)
  3. Select Settings
  4. Pick Developer Settings
  5. Pick Personal Access Tokens and create a new one that has read:packages enabled

image

Once created, this PAT needs to be passed into Packer. If using the settings JSON file this is just another variable

{
"client_id": "Azure Client ID",
"client_secret": "Client Secret",
"tenant_id": "Azure Tenant ID",
"subscription_id": "Azure Sub ID",
"object_id": "The object ID for the AAD SP",
"location": "Azure location to use",
"resource_group": "Name of resource group that contains Storage Account",
"storage_account": "Name of the storage account",
"ssh_password": A password",
"install_password": "A password",
"commit_url": "A url to to be save in a text file on the VHD, usually the URL if commit VHD based on",

"github_feed_token": "A PAT"

}

If you are running Packer within a build pipeline, as the other blog post discusses, then the PAT will be another build variable.

Once this change was made I was able to get Packer to run to completion, as expected.

Cannot queue a new build on Azure DevOps Server 2019.1 due to the way a SQL cluster was setup

I have recently been doing a TFS 2015 to Azure DevOps Server 2019.1 upgrade for a client. The first for a while, I have been working with Azure DevOps Service mostly of late. Anyway I saw an issue I had never seen before with any version of TFS, and I could find no information on the Internet.

The Problem

The error occurred when I tried to queue a new build after the upgrade, the build instantly failed with the error

‘The module being executed is not trusted, Either the owner of the database of the module need to be granted authenticate permission, or the module needs to be digitally signed. Warning: Null value is eliminated by an aggregate or other SET operation, The statement has been terminated’.

The Solution

It turns out the issue was the the client was using a enterprise wide SQL cluster to host the tfs_ databases. After the Azure DevOps upgrade the DBAs has enabled a trigger based logging system to monitor the databases and this was causing the error.

As soon as this logging was switched off everything worked as expected.

I would not recommend using such a logging tool for any ‘out the box’ database for a product such as TFS/Azure DevOps Server where the DBA team don’t own the database schema’s changes, as these databases will only occur if the product is upgraded

Strange issue with multiple calls to the same REST WebClient in PowerShell

Hit a strange problem today trying to do a simple Work Item update via the Azure DevOps REST API.

To do a WI update you need to call the REST API

  • Using the verb PATCH
  • With the Header “Content-Type” set to “application/json-patch+json”
  • Include in the Body the current WI update revision (to make sure you are updating the current version)

So the first step is to get the current WI values to find the current revision.

So my update logic was along the lines of

  1. Create new WebClient with the Header “Content-Type” set to “application/json-patch+json”
  2. Do a Get call to API to get the current work item
  3. Build the update payload with my updated fields and the current revision.
  4. Do a PATCH call to API using the client created in step 1 to update the current work item

Problem was at Step 4 I got a 400 error. A general error, not too helpful

After much debugging I spotted the issue was that after Step 2. my WebClient’s Headers had changed, I had lost the content type – no idea why.

It all started to work if I recreated my WebClient after Step 2, so something like (in PowrShell)

Function Get-WebClient

{

param
(

[string]$pat,
[string]$ContentType = "application/json"

)

$wc = New-Object System.Net.WebClient
$pair = ":${password}"
$bytes = [System.Text.Encoding]::ASCII.GetBytes($pair)
$base64 = [System.Convert]::ToBase64String($bytes)
$wc.Headers.Add(“Authorization”,"Basic $base64")
$wc.Headers["Content-Type"] = $ContentType
$wc

}


function Update-WorkItemTitle {

param

(

$baseUri ,
$teamproject,
$workItemID,
$pat,
$title

)

$wc = Get-WebClient -pat $pat -ContentType "application/json-patch+json"
$uri = "$($baseUri)/$teamproject/_apis/wit/workitems/$($workItemID)?api-version=5.1"

# you can only update a work item if you also pass in the rev, this makes sure you are updating lastest version
$jsondata = $wc.DownloadString($uri) | ConvertFrom-Json

$wc = Get-WebClient -pat $pat -ContentType "application/json-patch+json"

$data = @(
@{
            op    = "test";
            path  = "/rev";
            value = $jsondata.Rev
},
@{
            op    = "add";
            path  = "/fields/System.Title";
            value = $title
}
) | ConvertTo-Json

$jsondata = $wc.UploadString($uri, "PATCH", $data) | ConvertFrom-Json
$jsondata

}

Authentication loops swapping organisations in Azure DevOps

I have recently been getting a problem swapping between different organisations in Azure DevOps. It happens when I swap between Black Mable ones and customer ones, where each is back by different Azure Active Directory (AAD) but I am using the same credentials; because I am either a member of that AAD or a guest.

The problem is I get into an authentication loop. It happens to be in Chrome, but you might find the same problem in other browsers.

It seems to be a recent issue, maybe related to MFA changes in AAD?

I used to be re-promoted for my ID when I swapped organisations in a browser tab, but not asked for further authentication

However, now the following happens

  • I login to an organisation without a problem e.g https://dev.azure.com/someorg using ID, password and MFA
  • In the same browser window, when I connect to another organisation e.g. https://dev.azure.com/someotherorg 
  • I am asked to pick an account, then there is the MFA challenge, but then go back to the login
  • …. and repeat.

The fix is to go in the browser tab to https://dev.azure.com. As you are already authenticated you will be able to sign out, then all is OK, you can login again.

The other options is to make even more use of Chrome People; one ‘person’ per customer, as opposed to my current usage on one ‘person’ per ID