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

auwi

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.

auwi

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.

DanC

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

ErinD

@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)

DanC

Hi @ErinD

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

Nestea

I have the same problem with Octopus deploy.  Any suggestion?

Nestea

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 \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>, \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>, \Octopus Deploy Tentacle\Work\20240402074132-2344151-170\Script.ps1: line 23 

April 2nd 2024 15:41:35Error

at <ScriptBlock>, \Octopus Deploy Tentacle\Work\20240402074132-2344151-170\Script.ps1: line 13 

April 2nd 2024 15:41:35Error

at <ScriptBlock>, \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";

& { 

# see https://documentation.red-gate.com/sca3/automating-database-changes/add-ons/octopus-deploy/deploy-a-database-package-to-a-single-environment-using-octopus-deploy-and-powershell#DeployadatabasepackagetoasingleenvironmentusingOctopusDeployandPowerShell-2.Setuptheprojectvariables

# 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