TFS/VSTS Deployment Scenario

JaBaranJaBaran Posts: 4 New member
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?

Answers

  • AlexYatesAlexYates Posts: 264 Rose Gold 2
    Use different agents for different environments. Give each agent only the creds it needs to deploy to one environment.
    Alex Yates
    DevOps Mentor and Coach

    Director of DLM Consultants
    Creator of Speaking Mentors
    Microsoft Data Platform MVP
    Friend of Redgate
    Twitter / LinkedIn
  • JaBaranJaBaran Posts: 4 New member
    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.
  • AlexYatesAlexYates Posts: 264 Rose Gold 2
    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.
    Alex Yates
    DevOps Mentor and Coach

    Director of DLM Consultants
    Creator of Speaking Mentors
    Microsoft Data Platform MVP
    Friend of Redgate
    Twitter / LinkedIn
  • JaBaranJaBaran Posts: 4 New member
    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.
  • JaBaranJaBaran Posts: 4 New member
    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")
    }
Sign In or Register to comment.