TFS/VSTS Deployment Scenario

JaBaran

What are the options for deploying as a AD User?

I wouldn't want my general release agent user to have access to all databases, and the DBAs don't want to run in mixed authentication. Is there a means of doing impersonation or changing the run as user during the deployment steps for the scripts?

AlexYates

Use different agents for different environments. Give each agent only the creds it needs to deploy to one environment.

JaBaran

While I agree it would work, I'd need some where around 500 different agents just for the number of databases, and then another 3 times that for environments. Being able to pass credentials as a part of the Release task would be ideal, but I don't see that as an option unless I'm missing something, and that is really what I'm hoping for.

AlexYates

Well you could use SQL Auth and pass the creds through, but that hardly feels like wise security choice.

If you want to use WinAuth you'll need to have the agent run as a particular user.

JaBaran

As stated before the admins don't want to do mixed mode, and they prefer AD for everything that isn't a vendor requirement.

Looks like we need to figure out how to wrap the call to execute as another user, I was hoping for something more supported.

JaBaran

Since I can't attach a file, below you can see what we did to allow for impersonation to another user from the release agent, should you have the need. We did create a separate TFS task to support it, but this is the meat of it. I would love to see something supported that accomplishes the same thing in the near future from RedGate. We are effectively doing 1 additional step from what is done by default since we are reproducing a fair amount of work.



[CmdletBinding(DefaultParameterSetName = 'None')]
param()
$global:ErrorActionPreference = 'Stop'
Import-Module -Name "$PSScriptRoot\ps_modules\TaskHelpers"

[string]$packagePath = Get-VstsInput -Name PackagePath
[string]$releaseVersion = Get-VstsInput -Name ReleaseVersion
[string]$databaseServer = Get-VstsInput -Name DatabaseServer
[string]$databaseName = Get-VstsInput -Name DatabaseName
[bool]$useWindowsAuth = Get-VstsInput -Name UseWindowsAuth -AsBool
[string]$databaseUserName = Get-VstsInput -Name DatabaseUserName
[string]$databasePassword = Get-VstsInput -Name DatabasePassword
[string]$azureTargetServer = Get-VstsInput -Name AzureTargetServer
[string]$azureTargetDatabase = Get-VstsInput -Name AzureTargetDatabase
[string]$targetTypeOption = Get-VstsInput -Name TargetTypeOption
[bool]$useWindowsAuthImpersonation = Get-VstsInput -Name UseWindowsAuthImpersonation -AsBool

function GetVariableSetterFromVariable($name){
$variable = Get-Variable -Name $name

if($variable -ne $null){
return GetVariableSetter -name $variable.Name -value $variable.Value
}else{
return ""
}
}

function GetVariableSetter($name, $value){
switch ($value.GetType().Name){
"String"{
return "Set-Variable -Name $name -Value `"$($value.Replace('"','``"'))`";`n"
}
"Boolean"{
return "Set-Variable -Name $name -Value `$$value;`n"
}
}
}

if($useWindowsAuthImpersonation){
Write-VstsTaskVerbose -Message 'Building package deployment script environment'
if (-not $packagePath.EndsWith('.ps1')) {
$msg = 'Unable to deploy. Ensure package to deploy is a ''.ps1'' file.'
Exit-WithError $msg
}

$variables =
(GetVariableSetterFromVariable -name packagePath) +
(GetVariableSetterFromVariable -name releaseVersion) +
(GetVariableSetterFromVariable -name databaseServer) +
(GetVariableSetterFromVariable -name databaseName) +
(GetVariableSetterFromVariable -name useWindowsAuth) +
(GetVariableSetterFromVariable -name databaseUserName) +
(GetVariableSetterFromVariable -name databasePassword) +
(GetVariableSetterFromVariable -name azureTargetServer) +
(GetVariableSetterFromVariable -name azureTargetDatabase) +
(GetVariableSetterFromVariable -name targetTypeOption)

Get-ChildItem Env: | ForEach-Object {
$variables += GetVariableSetter -name $_.Name -value $_.Value
}

Write-VstsTaskVerbose -Message 'Writing package deployment script environment'
$script = Join-Path $env:SYSTEM_DEFAULTWORKINGDIRECTORY "impersonationscript.ps1"
[System.IO.File]::WriteAllText($script,
$variables +
"& $packagePath"
)

$command = (join-path ((Get-Item -Path ".\" -Verbose).FullName) "psexec.exe") +
" -accepteula -d -h " +
"-u `"$databaseUserName`" " +
"-p `"$databasePassword`" " +
"powershell " +
"`"$script`""

Write-VstsTaskVerbose -Message 'Executing the package deployment script'
Invoke-Expression $command
}else{
& (Join-Path $PSScriptRoot "DeployReadyRollDatabase.ps1")
}