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.

is your octopus tentacle process which is running the dacpac deployment running under the user which has a write access to the DB? — Alex M
For the demo, I just had it running under NT AUTHORITY\SYSTEM. That user did not have access to the database. — Franco Pettigrosso
At first, I did not know how it will work. In a sense, I still do not and I do not have a plan on how to use it. I did give NT AUTHORITY\SYSTEM access to the DB and it did work. If you have any knowledge of how it should be used - It will greatly be appreciated. — Franco Pettigrosso
see example in an answer for PS code that will generate an update script for the DB from dacpac. Manually run it for easy testing . — Alex M

Alex M Alex M · Accepted Answer · 2019-10-24T15:53:15

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.

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

1 Answers