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.