To export Azure SQL database using Sqlpackage and Managed Identity:
Step1
Enable system assigned managed identity on an Azure VM
Step2
- Enable AAD auth on Azure SQL server
- Conn to Azure SQL database via AAD admin
- Create contained user for the managed identity (using Azure VM name as contained username)
create user <vmname> from external provider;
alter role db_owner add member <vmname>;
Step3
On the Azure VM where we enabled System assigned Managed Identity, execute below to test getting access token:
# Using PowerShell’s Invoke-WebRequest, make a request to the local managed identity's endpoint to get an access token for Azure SQL:
$response = Invoke-WebRequest -Uri 'http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=https%3A%2F%2Fdatabase.windows.net%2F' -Method GET -Headers @{Metadata="true"}
# Convert the response from a JSON object to a PowerShell object:
$content = $response.Content | ConvertFrom-Json
# Extract the access token from the response:
$AccessToken = $content.access_token
Step4
Run sqlpackage + managed identity to export database
./sqlpackage.exe /at:$AccessToken /Action:Export /TargetFile:"C:\AdventureWorksLT.bacpac" \ /SourceConnectionString:"Server=tcp:{yourserver}.database.windows.net,1433;Initial Catalog=AdventureWorksLT;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
# OR
./sqlpackage.exe /at:$($AccessToken_Object.Token) /Action:Export /TargetFile:"C:\AdventureWorksLT.bacpac" \ /SourceConnectionString:"Server=tcp:{yourserver}.database.windows.net,1433;Initial Catalog=AdventureWorksLT;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
Reference:
Microsoft
