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

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 Server\110\DAC\bin\Microsoft.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

image

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: blackmarble\tfslab, \\store\drops.
Deploying the DB with the following settings
sqlserver:   dc
dacpac:
\\store\drops\Main.CI\Main.CI_20130314.2\Debug\Database.dacpac
dbname: Database1
Initializing deployment (Start)
Exception calling "Deploy" with "3" argument(s): "Could not deploy package."
Initializing deployment (Failed)
At
\\store\drops\Main.CI\Main.CI_20130314.2\Debug\SQLDeploy.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: blackmarble\tfslab, \\store\drops.

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.