witadmin and Visual Studio 2017

Ordinarily, witadmin tool is stored under %programfiles(x86)%\Microsoft Visual Studio XX.0\Common7\IDE path as per https://docs.microsoft.com/en-us/vsts/work/customize/reference/witadmin/witadmin-customize-and-manage-objects-for-tracking-work, but in case of the Visual Studio 2017 it's stored under %programfiles(x86)%\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\CommonExtensions\Microsoft\TeamFoundation\Team Explorer. Notice it's now stored in the same folder where all Visual Studio extensions are stored. This makes sense considering that Process Editor is now a Visual Studio extension instead of a separate install/download. It's a small, but important change. Just thought I'd share. Thanks for reading.

Another task in VSTS/TFS

As I help people create awesome build and release pipelines, I have found myself quite often needing to download something from somewhere to do something awesome. So, I have decided to write a build task to make it easier. Along came the Download File task: https://marketplace.visualstudio.com/items?itemName=automagically.DownloadFile. This task allows you to download a file from anonymous sources or authenticated sources like Artifactory, or Nexus, or whatever. It's simple and yet pretty good task.

Later, I have realized that quite often when I download the file as a part of my build/release, I need to extract that file. So, I have released another task called Download and Extract File: https://marketplace.visualstudio.com/items?itemName=automagically.DownloadExtractFile. Yes, I know it's not very creative name, but it's very descriptive. This task allows you to download a file from anonymous or authenticated sources and then extract it to the specified folder. This is a better task, I think.

Thanks for using my tasks. And, again thanks for reading.

Azure AD sync errors for administrative user accounts

This is a follow up blog post to the "Insufficient access rights to perform the operation error in Azure AD Connect" blog post I did a little while back. The original blog post covered Azure AD sync errors. For the most part, anyways, because we kept seeing sync errors for specific users. After doing a bit of digging, we have learned that the users that kept getting sync errors belong to Active Directory Administrators or Domain Admins groups. This lead our troubleshooting journey to learning about Active Directory Protected Groups.

As it turns out, if a user is a member of Active Directory Administrators or Domain Admins groups, then Active Directory will overwrite any ACL changes that you make with predefined ACL template on a regular basis. So, if we make ACL changes like grant additional permissions on those accounts or enable permissions inheritance on those accounts in order to allow Azure AD Connector to update source anchor (ms-ds-consistencyGuid) attribute, this change will be overwritten by Active Directory, which brings us back to square one. To bypass this issue, you can do the following:

  • Change ACL template in Active Directory to include the changes you need like Replicate Directory Changes and Replicate Directory Changes All permissions for Azure AD Connector account and write permissions for source anchor (ms-ds-consistencyGuid) attribute. This would work, but in my opinion, it's a bit too drastic
  • Or, remove the users from Active Directory Administrators or Domain Admins groups, if you can.
  • Or, you can make the permissions changes on those accounts and immediately force Azure AD Connect sync using the following PowerShell command: Start-ADSyncSyncCycle -PolicyType Initial. Azure AD Connect should have enough time to write to source anchor attribute and complete the sync without errors. After the initial sync is complete, AD can reset ACL back on those account all it wants since we only need to write to source anchor attribute once.

I prefer the last option, since it's simple and it works. More information about AD protected groups can be found at https://technet.microsoft.com/en-us/library/2009.09.sdadminholder.aspx

Thanks for reading.

Insufficient access rights to perform the operation error in Azure AD Connect

If you're getting Insufficient access rights to perform the operation in your Azure AD Connect synchronization logs, do the following:

  • Make sure you have the latest version of Azure AD Connect installed: https://www.microsoft.com/en-us/download/details.aspx?id=47594
  • If you're syncing passwords, make sure that your sync service account has Replicate Directory Changes and Replicate Directory Changes All permissions in your on premises Active Directory
  • Make sure that your sync service account has write permissions on your sourceAnchor attribute (which is most likely set to ms-ds-consistencyGuid). You can do that either using the user interface, or PowerShell, which is easier:

    $accountName = "DOMAINNAME\USERNAME" #[this is the account that will be used by Azure AD Connect Sync to manage objects in the directory.


    $cmd = "dsacls '$ForestDN' /I:S /G '`"$accountName`":WP;ms-ds-consistencyGuid;user'"

    Invoke-Expression $cmd

  • Make sure that inheritance is turned on for the AD objects that get errors in the synchronization logs. To do that
    • Open Active directory Users and Computers
    • Enable the Advanced features in the View settings and,
    • Open up the user object that can't sync.
    • Go to the security tab and then into advanced
    • Check to make sure the box is checked to inherit permissions. But before you do that make sure that the enabling inheritance will not bring down some permissions that you do not want to be there

That's all. Thanks for reading.

VSTS/TFS task to execute SQL scripts

I have published VSTS/TFS build task that allows you to execute SQL script as a part of your build or release pipelines. What makes this task different from similar tasks in VSTS marketplace is that you can execute SQL script in the number of ways:

  • Execute SQL script from the agent using integrated Windows authentication
  • Execute SQL script from the agent using SQL authentication
  • Execute SQL script from the remote server using Windows authentication, which is handy when you have to do cross domain deployments and/or when you do not have SQL client tools installed on the agent
  • Execute SQL script from the remote server using SQL authentication, which is handy when you do not have SQL client tools installed on the agent

The tool is free and it works. You can download it from VSTS marketplace.

Please let me know if you have any comments or find any bugs. Thanks for reading.

Two times I #HitRefresh

I’ve been an independent, consultant since 1993. Which means it’s coming up to the 25th anniversary of the first time I #HitRefresh on my career. At that time I was working as an employee with a fairly large data centre in the financial industry, primarily writing teller systems in C for the Credit Unions of Ontario. At this time these systems ran on DOS and OS\2. I #HitRefresh by moving into the consulting world building applications for Windows written in PowerBuilder.

The second time I #HitRefresh was around 2000 when I latched onto the .net wave and learned C# and the .net framework. Along with many other new and exciting tools. Most important to me Team Foundation Server.

This was a big change from the world I had become accustomed. My friend and colleague Barry Gervin and I started speaking at user groups, attending the PDC conferences and going to each others house once a week to teach each other different parts of the .net framework. We eventually started a company that helped out customers adopt this technology. That was the beginning of ObjectSharp Consulting. We offered consulting and training. Some of that training we authored, and some was Microsoft Official Curriculum.

Although there have been many #HitRefresh moments in my career this was the biggest one. I am sure there will be more. Looking back it amazes me how this industry has changed since the mid 80’s when I began writing code. 

This post is part of a series of #HitRefresh moments. Read more at www.HitRefreshbook.com.

Quick and easy custom filtered burn downs

Today I am going to show you how to create quick little burn down charts right off of a work item query.

As you may well know the burn down chart in TFS is based on the remaining work field in the task work item. It queries all the tasks in the corresponding iteration and shows how the remaining work changes each day during the iteration.

This is great if you are a scrum team, it's all you need to know.

I have a lot of customers that use TFS that are not using Scrum or are even that agile. However they love the idea of a burn down report. However they want to see it with different filters applied.

Perhaps by individual or by activity or Area Path. Remember not everyone follows the agile manifesto 100%. Don’t judge. :)

Back to our quick burn downs.

First we need to create a work item query that defines the scope of our burn down. Since it’s a query and not the iteration backlog we can grab any tasks we want we can create a burn down that crosses teams and iterations.

So lets keep it simple for now and create a query that gets all the tasks for the current iteration.


Now add the a few columns to the query. We’ll need Remaining Work since that is the key to our Burn Down. Lets also grab Activity, Area Path and Assigned To for our example


Now over to the Charts section of the query. (Make sure you have saved the query so you can make a chart.)

Create a new chart and select a Line Trend report.

Under Values change Count to Sum and select Remaining Work as the field to Sum.

Pick your Rolling period. This is how far back you want to start the burn down.

Now select your Group By field. This is where you can define if you want to see the burn down by Activity or Team Member or Area Path.


There is no projection beyond today or an ideal trend line, but I am sure you can visualize that.

With charts you can make a bunch of these, each with their own “group by” field to augment the burn down for your teams iteration.  

Writing to the Build Report in TFS 2015

This is a cool trick for writing something directly onto the build report. 

As an example I will write out the heading “ObjectSharp” and under it I’ll put the website. So it will come out looking like this.













The syntax follows this format:

##vso[area.action property1=value;property2=value;...]message

For more examples and syntax look here. For my example I simply added an inline Powershell task. With the following powershell.

$TempFile = [System.IO.Path]::GetTempFileName()
$fullurl = "http://www.ObjectSharp.com" | set-content $Tempfile
Write-Host "##vso[task.addattachment type=Distributedtask.Core.Summary;name=ObjectSharp;]$Tempfile" 

How would I use this. In the past I have used it to write code metric results to the build report. I have also used it to write the URL to a location in Nexus Package Manager where a build artifact was uploaded ready for deployment.

Global Azure BootCamp


On Saturday, April 22, 2017, communities across the world will be holding Azure Bootcamps. There are already 132 planned!

Here is a list of events happening in Canada: Calgary, Montreal, Ottawa, Quebec, Toronto, Vancouver

Check time elapsed and time remaining for running SQL job

Just thought I'd share this handy SQL statement that allows you to check for the time elapsed and time remaining for the running SQL job. It would help you to get a better idea how much more time you'd have to wait for the script to complete. Anyway, here is a script that checks time elapsed and time remaining for the running backup SQL job

SELECT db_name(database_id), command, percent_complete, 'elapsed' = total_elapsed_time / 60000.0, 'remaining' = estimated_completion_time / 60000.0

FROM sys.dm_exec_requests

WHERE command like 'BACK%'