Erorr exporting Azure sql database to blob storage with powershell

Question

I'm trying to use Azure management API to export a SQL database to a bacpac file in a blob storage. The process seems fairly simple: obtain a token, and then:

$sqlAzureBackupHeaders = @{
    Authorization = "Bearer $accessToken"
}

$sqlAzureBackupParams = @{
    storageKey =  "<key-goes-here>",
    storageUri =  "http://mystorageacct.blob.core.windows.net/my-blob-container/export-name.bacpac",
    storageKeyType =  "StorageAccessKey",
    administratorLogin =  "<sql-user>",
    administratorLoginPassword =  "<sql-password>",
    authenticationType =  "SQL"
}

$sqlAzureApiUri = "https://management.azure.com/subscriptions/<subscription-id>/resourceGroups/<resource-group>/providers/Microsoft.Sql/servers/<server-name>/databases/<database-name>/export?api-version=2014-04-01"
Invoke-RestMethod -Uri $sqlAzureApiUri -Method Post -Headers $sqlAzureBackupHeaders -Body $sqlAzureBackupParams

This results in an error:

Invoke-RestMethod : Receivera:InternalServiceFaultThe server was unable to process the request due to an internal error.  For more 
information about the error, either turn on IncludeExceptionDetailInFaults (either from ServiceBehaviorAttribute or from the 
<serviceDebug> configuration behavior) on the server in order to send the exception information back to the client, or turn on tracing 
as per the Microsoft .NET Framework SDK documentation and inspect the server trace logs.
At D:\Users\protec-admin\Desktop\run-backups.ps1:140 char:1
+ Invoke-RestMethod -uri $sqlAzureApiUri -Method Post -Headers $sqlAzur ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebException
    + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand

I tried using Invoke-WebRequest and converting body to json string - with the same result.

When I try the same call using Postman, it works fine, so there's something with making the call from powershell that's not working correctly.

How can I get this working from powershell?

Answer 1

According to my test, the rest API just accept the json body. Please use ConvertTo-Json to convert the body to json.

For example

$headers=@{"Authorization" = "Bearer "+$token}
$body=@{
  "storageKeyType"= "StorageAccessKey";
  "storageKey"= "<your storage account access key>";
  "storageUri"= "https://blobstorage0516.blob.core.windows.net/sample/testbacpac2.bacpac";
  "administratorLogin"= "<SQL admin>";
  "administratorLoginPassword"= "<SQL admin passsword>";
  "authenticationType"= "SQL"
}|ConvertTo-Json
$sqlAzureApiUri = "https://management.azure.com/subscriptions/<subscription-id>/resourceGroups/<resource-group>/providers/Microsoft.Sql/servers/<server-name>/databases/<database-name>/export?api-version=2014-04-01"
Invoke-RestMethod -Method Post -Uri $uri -Headers $headers -Body $body -UseBasicParsing -ContentType "application/json"