Otcopus deploy - SQL - Deploy DACPAC "Could not connect to database server."

Question

Trying out Octopus deploy for the first time. Trying to deploy a dacpac to a machine and it keeps on failing. I keep on getting the following error:

Exception calling "Extract" with "4" argument(s): "Could not connect to database server." 
At C:\Octopus\Work\20191023152506-102-81\Script.ps1:394 char:13 
+             $dacServices.Extract($dbDacPacFilepath, $TargetDatabase,  ... 
+             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
    + CategoryInfo          : NotSpecified: (:) [], ParentContainsErrorRecordException 
    + FullyQualifiedErrorId : DacServicesException 

The remote script failed with exit code 1 

The action SQL - Deploy DACPAC on Staging failed

I am currently using SQL server 2017 and have the dacframework installed for SQL server 2016. for the connection string i tried using ., localhost, and the name of the server given in sql management studio. I am not passing any credentials, I am using integrated security. I am also passing the database name as well. I followed this youtube video also, just without using the project variables.

Answer 1

In my previous experience I just used the SqlPackage.exe to deploy a dacpac. Helps for manually testing and polishing out permissions, or other issues.

For example:

#example usage:Generate-DBUpdate-Script -server $dbServer -db $dbName -user $dbUser -psw $dbPassword -dacpacFilePath $dacpacFile -publishProfilePath ".\Publish\$dbPublishProfile" -outputFile $SqlUpgradeArtifactName
function Generate-DBUpdate-Script($server, $db, $user, $psw, $dacpacFilePath, $publishProfilePath, $outputFile)
{        
    #generate an update script
    & 'C:\Program Files (x86)\Microsoft SQL Server\110WorkingDAC\DAC\bin\SqlPackage.exe' /Action:Script /OutputPath:$outputFile /SourceFile:$dacpacFilePath /Profile:$publishProfilePath /TargetServerName:$server /TargetDatabaseName:$db /TargetUser:$user /TargetPassword:$psw

    #save generated script as deployment artifact
    New-OctopusArtifact $outputFile
}

Can change the action to publish to avoid generating the script and just deploy straight away.

Hope that helps.