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

• 
  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

  DanC July 31, 2023 14:07

  - 0 +
• 
  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.

  auwi August 03, 2023 03:54

  - 0 +
• 
  @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)

  ErinD September 04, 2023 00:02

  - 0 +
• 
  Hi @ErinD
  
  Unfortunately, there's no current plans to support this, and I would advise having a look into Flyway

  DanC September 11, 2023 08:05

  - 0 +
• 
  I have the same problem with Octopus deploy.  Any suggestion?

  Nestea April 02, 2024 08:57

  - 0 +
• 
  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

  Nestea April 02, 2024 10:26

  - 0 +

Add comment

Please sign in to leave a comment.