First, you need to add firewall rule in order to connect to Azure SQL Server.
- Edit your build definition
- Select Option tab and check Allow Scripts to Access OAuth Token
- Add Azure PowerShell step (arguments: -RestAddress https://[account].vsdtl.visualstudio.com/DefaultCollection/_apis/vslabs/ipaddress -Token $(System.AccessToken) -RG [resource group] -Server [server name] -ruleName $(Build.BuildNumber)
Code:
param (
[string]$RestAddress,
[string]$Token,
[string]$RG,
[string]$Server
)
$basicAuth = ("{0}:{1}" -f 'test',$Token)
$basicAuth = [System.Text.Encoding]::UTF8.GetBytes($basicAuth)
$basicAuth = [System.Convert]::ToBase64String($basicAuth)
$headers = @{Authorization=("Basic {0}" -f $basicAuth)}
$result = Invoke-RestMethod -Uri $RestAddress -headers $headers -Method Get
Write-Host $result.value
New-AzureRmSqlServerFirewallRule -ResourceGroupName $RG -ServerName $Server -FirewallRuleName "UnitTestRule" -StartIpAddress "$($result.value)" -EndIpAddress "$($result.value)"
Secondly, I recommend you use the assembly in this package:
Microsoft.SqlServer.Dac.
Thirdly, to get the detail error, you can use this code instead:
Catch
{
Write-Host "$fileName deployment has been failed" -foregroundcolor "red"
$Error | format-list -force
Write-Host $Error[0].Exception.ParentContainsErrorRecordException;
Break
}
On the other hand, I recommend you can deploy SQL package through SqlPackage.exe.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…