What are the challenges you face when working across database platforms? Take the survey
Options

SQL Change Automation: releast to Azure SQL DB with AAD authentication only

I am currently investigating whether SQL Change Automation can deploy to an Azure SQL Server that only allows Azure AD authentication via Azure DevOps using the Redgate-provided extension. The options listed below are the only ones allowed for authentication(screenshot). I hope someone could share their experience on how they achieved this via Azure DevOps using the Redgate extension.


Best Answer

  • Options
    auwiauwi Posts: 2 New member
    Hi @DanC

    i was able to find a way using the Azure Active Directory Password Authentication.
    i need An AD account that has a non-interactive or disabled 2factor auth like a service account. if it is coming from onprem AD just make sure to sync that account to AAD to able to use in AZDO.

Answers

  • Options
    Hi @auwi

    Unfortunately, this method of auth isn't supported by SQL Change Automation in the pipelines.

    If you wish to use Azure AD for deployments I would advise using Flyway

    Kind regards

    Dan Calver | Redgate Software
    Have you visited our 
    Help Center?

  • Options
    ErinDErinD Posts: 3 Bronze 1
    edited September 4, 2023 12:07AM
    @DanC, from your comment in July, are there plans to support Azure AD password authentication in Azure DevOps in the future? I'm receiving the following error, when I try to use AAD (Entra ID). This occurs both with an explicit connection, as well as with a service connection. As it is an option in the task assistant, why isn't it supported?


    Error: Could not load file or assembly 'Microsoft.IdentityModel.Abstractions, Version=6.22.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)
  • Options
    Hi @ErinD

    Unfortunately, there's no current plans to support this, and I would advise having a look into Flyway

    Kind regards

    Dan Calver | Redgate Software
    Have you visited our 
    Help Center?

  • Options
    NesteaNestea Posts: 2 Bronze 1
    I have the same problem with Octopus deploy.  Any suggestion?
  • Options
    NesteaNestea Posts: 2 Bronze 1
    Hello, Guys.  

    I found this issue and I think it's related to my case.  But only difference is I'm using Octopus deploy.  Is there any documentation on how to implement this kind of setup in Octopus deploy? 

    Hope to hear from you soon! 

    Btw, here's the error I got after shifting to AAD auth. 

    Sqlcmd:  invalid argument "DeployPath=D:\\Octopus Deploy Tentacle\\Applications\\ClientName\\QA\\ClientNameDB\\1.1.3424_3\\db\\project\\bin\\Debug\" ForceDeployWithoutBaseline=False DefaultFilePrefix=Database DefaultDataPath=\"" for "-v, --variables" flag: parse error on line 1, column 96: bare " in non-quoted-field 
    April 2nd 2024 15:41:35Error
    NotSpecified: A deployment error occurred: sqlcmd.exe exited with a non-zero exit code.  
    April 2nd 2024 15:41:35Error
    At D:\Octopus Deploy Tentacle\Work\20240402074132-2344151-170\Script.ps1:23 char:5 
    April 2nd 2024 15:41:35Error
    +     &"$OutputPath\db\project\bin\Debug\Database_DeployPackage.ps1" 
    April 2nd 2024 15:41:35Error
    +     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
    April 2nd 2024 15:41:35Error
    at <ScriptBlock>, D:\Octopus Deploy Tentacle\Applications\ClientName\QA\ClientNameDB\1.1.3424_3\db\project\bin\Debug\Database_DeployPackage.ps1: line 234 
    April 2nd 2024 15:41:35Error
    at <ScriptBlock>, D:\Octopus Deploy Tentacle\Work\20240402074132-2344151-170\Script.ps1: line 23 
    April 2nd 2024 15:41:35Error
    at <ScriptBlock>, D:\Octopus Deploy Tentacle\Work\20240402074132-2344151-170\Script.ps1: line 13 
    April 2nd 2024 15:41:35Error
    at <ScriptBlock>, D:\Octopus Deploy Tentacle\Work\20240402074132-2344151-170\Bootstrap.Script.ps1: line 2050 
    April 2nd 2024 15:41:35Error
    at <ScriptBlock>, <No file>: line 1 
    April 2nd 2024 15:41:35Error
    at <ScriptBlock>, <No file>: line 1 
    April 2nd 2024 15:41:35Fatal
    The remote script failed with exit code 1 

    and here's the script I used to deploy my script.. 

    # Azure specific; using SQL Credentials vs. integrated auth
    $stepName = "CopyOver SCA DB Package - tenant";
    $projectName = $OctopusParameters['Octopus.Project.Name'];
    $currentRelease = $OctopusParameters['Octopus.Release.Number'];
    $OutputPath = $OctopusParameters["Octopus.Action[$stepName].Output.Package.InstallationDirectoryPath"];
    $TenantDBName = "#{DatabaseName}";
    $SCAProjectName = "Database";
    $DBStartDate=(GET-DATE)

    Write-Host "projectName is $SCAProjectName";
    Write-Host "Database Name is $TenantDBName";

    & { 
    # Since we're not using the cmdlets, and calling directly, we have to pass the '$UseWindowsAuth' along with the credentials
        $UseWindowsAuth = $false
    #$DatabaseUserName = '#{SQLServerUsername}';
        #$DatabasePassword = '#{SQLServerPassword';
        $DatabaseServer="#{DatabaseServer}"; $DatabaseName=$TenantDBName; $ForceDeployWithoutBaseline="False"; $ReleaseVersion=$currentRelease;
        $Environment = "#{Octopus.Environment.Name}";
        &"$OutputPath\db\project\bin\Debug\Database_DeployPackage.ps1" 
    }
    $DBEndDate=(GET-DATE)
    #display processing time with the current database with format of hh:mm:ss.
    Write-Host ("Processed {1} with the duration of (hh:mm:ss) {0:c}" -f (NEW-TIMESPAN –Start $DBStartDate –End $DBEndDate), $TenantDBName)



    Thank you!
    Nestea
Sign In or Register to comment.