Installing a DB from a DACPAC using Powershell as part of TFS Lab Management deployment

Mar 14, 2013 · 3 min read · Uncategorised ·

Share on:

I have been battling setting up a DB deployed via the SQL 2012 DAC tools and Powershell. My environment was a network isolated pair of machines

DC – the domain controller and SQL 2012 server
IIS – A web front end

As this is network isolated I could only run scripts on the IIS server, so my DB deploy needed to be remote. So the script I ended up with was

param(
    [string]$sqlserver = $( throw "Missing: parameter sqlserver"),
    [string]$dacpac = $( throw "Missing: parameter dacpac"),
    [string]$dbname = $( throw "Missing: parameter dbname") )

Write-Host "Deploying the DB with the following settings"
Write-Host "sqlserver: $sqlserver"
Write-Host "dacpac: $dacpac"
Write-Host "dbname: $dbname"

# load in DAC DLL (requires config file to support .NET 4.0)
# change file location for a 32-bit OS
add-type -path "C:Program Files (x86)Microsoft SQL Server110DACbinMicrosoft.SqlServer.Dac.dll"

# make DacServices object, needs a connection string
$d = new-object Microsoft.SqlServer.Dac.DacServices "server=$sqlserver"

# register events, if you want 'em
register-objectevent -in $d -eventname Message -source "msg" -action { out-host -in $Event.SourceArgs[1].Message.Message } | Out-Null

# Load dacpac from file & deploy to database named pubsnew
$dp = [Microsoft.SqlServer.Dac.DacPackage]::Load($dacpac)
$d.Deploy($dp, $dbname, $true) # the true is to allow an upgrade, could be parameterised, also can add further deploy params

# clean up event
unregister-event -source "msg"

Remember the SQL 2012 DAC tools only work with PowerShell 3.0 as they have a .NET 4 dependency.

This was called within the Lab Build using the command line

cmd /c powershell $(BuildLocation)SQLDeploy.ps1 dc $(BuildLocation)Database.dacpac sabs

All my scripts worked correctly locally when I ran it on the command line, they were also starting from within the build, but failing with errors along the lines of

Deployment Task Logs for Machine: IIS
Accessing the following location using the lab service account: blackmarbletfslab, \storedrops.
Deploying the DB with the following settings
sqlserver: dc
dacpac: \storedropsMain.CIMain.CI_20130314.2DebugDatabase.dacpac
dbname: Database1
Initializing deployment (Start)
Exception calling "Deploy" with "3" argument(s): "Could not deploy package."
Initializing deployment (Failed)
At \storedropsMain.CIMain.CI_20130314.2DebugSQLDeploy.ps1:26
char:2
+ $d.Deploy($dp, $dbname, $true) # the true is to allow an upgrade
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : DacServicesException
Stopped accessing the following location using the lab service account: blackmarbletfslab, \storedrops.

Though not obvious from the error message the issue was who the script was running as. The TFS agent runs as a machine account, this had no rights to access the SQL on the DC. Once I granted the computer account IIS$ suitable rights to the SQL box all was OK. The alternative would have been to enable mixed mode authentication and user a connection string in the form

“server=dc;User ID=sa;Password=mypassword”

So now I can deploy my DB on a new build.