Change Azure DevOps Server (TFS) Service Account Passwords

Sometimes, you will have to change all service account’s passwords at once either they are expiring or security compliance requiring you to do that. Although we can get on the server and change the service accounts, due to multiple components Azure DevOps Server (formally known as TFS) has, it was quite a trouble to do so.

Components:

• App Tier
• Build & Deployment groups agents
• SSRS

Interfaces:

• Admin console
• Windows services
• Pipeline (Build & Release) definitions

Environments

• DEV
• QA
• Prod

Solution

• Setup a pipeline to streamline the processes
  • PS to change passwords
  • PS to change SSRS data source
  • App tier
    • tfsconfig Accounts /UpdatePassword /account::$serviceAccountName /Password:$(ServiceAccountPassword-New) /continue
  • PS to change all agents
• Scripts
• 

How to show as a more specific connecting program for TFS on SQL server

Symptoms

We would like to know what sessions belong to TFS. So instead of default “.Net SqlClient Data Provider”, how to change it to something more meaningful? It is good for monitoring system resource usage on the SQL server.

Analysis

You can easily modify the web.config for an ASP.NET application by adding the “Application Name” to achieve this. However, TFS implemented a little bit differently. TFS actually puts ConnectionString into its table (dbo.tbl_Database) inside the database (tfs_Configuration)

Solution

 

Open up the dbo.tbl_Database table from Tfs_Configuration and add “Application Name=TFS_App_Tier” (or whatever you prefer) to those records.

SELECT
s.session_id,
s.host_name,
s.program_name,
c.client_net_address,
c.local_net_address,
db_name(s.database_id) as dbname,
s.open_transaction_count,
s.status,
s.login_name,
s.is_user_process,
c.net_transport,
c.encrypt_option,
c.auth_scheme,
c.net_packet_size,
c.client_tcp_port,
c.connection_id
FROM
sys.dm_exec_sessions as s
INNER JOIN sys.dm_exec_connections c on s.session_id = c.session_id

Build A Custom vNext Build Task

It really comes handy when you can build your own build tasks. Either for encapsulation, re-usability, or just to look clean in your build definitions (or even as part of Low-Code movement).

You can find some references from Microsoft here.  But that’s for VSTS. Here I am going to provide a simple example for TFS on-premises.

First, you need some TFS build modules. Download them from GitHub’s Microsoft/vsts-task-lib

Second, need the command line tool for uploading the package to your TFS server. You can download and install from

npm i -g tfx-cli

Third, create the actual build script file, the one you want this custom task to execute during the build. You can specify parameters which will be passed through the task interface.

[CmdletBinding()]param()
Trace-VstsEnteringInvocation $MyInvocationtry {     Import-VstsLocStrings “$PSScriptRoot\Task.json” Import-Module -Name “$PSScriptRoot\ps_modules\Invoke-MsBuild\Invoke-MsBuild.psm1” Import-Module -Name “$PSScriptRoot\ps_modules\MSBuildHelpers\MSBuildHelpers.psm1”
[string] $ProjectFile = Get-VstsInput -Name ProjectFile -Require    [string] $OutputPath = Get-VstsInput -Name OutputPath -Require    [string] $MSBuildParameters = Get-VstsInput -Name MSBuildParameters    [string] $UseDataScripts = Get-VstsInput -Name MSBuildParameters Write-Host “ProjectFile:***$ProjectFile***”    Write-Host “OutputPath:***$OutputPath***”    Write-Host “MSBuildParameters:***$MSBuildParameters***” $MSBuildParameters = $MSBuildParameters + ” /p:OutputPath=$OutputPath” $SourceDir = (Get-Item $ProjectFile).Directory.FullName Write-Host “SourceDir:***$SourceDir***” ### Build Project file ### if ((Invoke-MsBuild -Path “$ProjectFile” -MsBuildParameters “$MsBuildParameters” -ShowBuildOutputInCurrentWindow).BuildSucceeded -ne $true) { Write-Error “*** ERROR – Build did NOT complete successfully! – $ProjectFile ***” } else { Write-Warning “*** Build completed successfully! – $ProjectFile ***” } ### Copy hand-picked predeploy scripts to BuildOutput folder based on PreDeploy.txt $ScriptDirName = “Scripts\Pre-Deploy” $PreDeployFilename = “$SourceDir\$ScriptDirName\Pre-Deploy.txt” Write-Output “PreDeployFilename:***$PreDeployFilename***” [System.IO.File]::ReadLines($PreDeployFilename) | %{$src=”$SourceDir\$ScriptDirName\$_”; $des=”$OutputPath\$ScriptDirName\$_”; New-Item “$des” -ItemType File -Force; Write-Output “Copy $src”; Copy-Item “$src” “$des” -Force -Recurse} | Out-String | Write-Warning
### Copy hand-picked predeploy scripts to BuildOutput folder based on PostDeploy.txt $ScriptDirName = “Scripts\Post-Deploy” $PostDeployFilename = “$SourceDir\$ScriptDirName\Post-Deploy.txt” Write-Output “PostDeployFilename:***$PostDeployFilename***” [System.IO.File]::ReadLines($PostDeployFilename) | %{$src=”$SourceDir\$ScriptDirName\$_”; $des=”$OutputPath\$ScriptDirName\$_”; New-Item “$des” -ItemType File -Force; Write-Output “Copy $src”; Copy-Item “$src” “$des” -Force -Recurse} | Out-String | Write-Warning      } finally {    Trace-VstsLeavingInvocation $MyInvocation }

 

Forth, create a task.json file to define the new custom task.

{
“id”: “XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX”,
“name”: “BuildDatabase”,
“friendlyName”: “Build Database”,
“description”: “Build Database”,
“author”: “My Name”,
“helpMarkDown”: “Build Database”,
“category”: “Build”,
“visibility”: [
“Build”
],
“demands”: [],
“version”: {
“Major”: “0”,
“Minor”: “1”,
“Patch”: “1”
},
“minimumAgentVersion”: “1.95.0”,
“instanceNameFormat”: “Build Database $(message)”,
“inputs”: [
{
“name”: “ProjectFile”,
“type”: “filePath”,
“label”: “Project File”,
“defaultValue”: “”,
“required”: true,
“helpMarkDown”: “Relative path from repo root of the project file.”
},
{
“name”: “OutputPath”,
“type”: “string”,
“label”: “Output Path”,
“defaultValue”: “”,
“required”: true,
“helpMarkDown”: “Path of the Output directory.”
},
{
“name”: “MSBuildParameters”,
“type”: “string”,
“label”: “MSBuild Arguments”,
“defaultValue”: “”,
“required”: false,
“helpMarkDown”: “Additional arguments passed to MSBuild.”
},
{
“name”: “DeployScripts”,
“type”: “string”,
“label”: “Include Pre/Post-Deploy Scripts”,
“defaultValue”: “false”,
“required”: false,
“helpMarkDown”: “If to run pre/post deploy scripts manually without using build action.”
}
],
“execution”: {
“PowerShell3”: {
“target”: “BuildDatabase.ps1”
}
}
}

Fifth (optional), prepare a icon file for showing up on the Build Task list.

Sixth, upload to the server

tfx build tasks upload –task-path .\BuildDatabase –auth-type basic –username <domain\username> –password <password> –service-url http://<tfsserver&gt;:8080/tfs

That’s it. You will then see your custom tasks showing up on the available task list when you try to add build step.

And you can see how the parameters in your build scripts look like at the front end.

Clean/Delete unwanted records from the project collection database – TFS 2010

Delete records from tbl_Attachment table

SELECT [TestRunId]
,[TestResultId]
,[IterationId]
,[ActionPath]
,[AttachmentId]
,[CreationDate]
,[IsComplete]
,[CompressedLength]
,[UncompressedLength]
,[CompressionType]
,[FileName]
,[Comment]
,[AttachmentType]
,[TmiRunId]

—- DELETE

FROM [Tfs_ecom].[dbo].[tbl_Attachment]
where 1=1
–and convert(char(10), CreationDate, 101) >= ’06/08/2010′
and convert(char(10), CreationDate, 112) <= ‘20101231’

 

Delete records from tbl_AttachmentContent table

SELECT ac.[AttachmentId]
,ac.[OffsetFrom]
,ac.[OffsetTo]
,a.uncompressedlength/1024/1024 ‘Size in MB’
,ac.[CreationDate]
, a.AttachmentId
, convert(int, convert(char(8), ac.CreationDate, 112)) ‘Creation Date’
,a.FileName

— DELETE [Tfs_ecom].[dbo].[tbl_AttachmentContent]

FROM tbl_Attachment a

INNER join [Tfs_ecom].[dbo].[tbl_AttachmentContent] ac

on ac.AttachmentId = a.AttachmentId
–where a.AttachmentId is null
–and convert(int, convert(char(8), ac.CreationDate, 112)) > 20111231
and convert(int, convert(char(8), ac.CreationDate, 112)) <= 20101231

 

References:

• Download Test Attachment Cleaner
• Use Test Attachment Cleaner

Distributed SCM capability

As we all knew, big companies like Google, Ebay, Amazon use distributed their capacity geographically to scale the business needs including computing, data processing, shipping, inventory, etc. Will a distributed SCM capability work? Can the brains of SCM profession network like our brain networks?