Blog

How to use Sqlpackage with Managed Identity

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:

Tutorial: Use a managed identity to access Azure SQL Database – Windows – Azure AD – Microsoft Entra | Microsoft Learn

How managed identities for Azure resources work with Azure virtual machines – Microsoft Entra | Microsoft Learn

SqlPackage Export – SQL Server | Microsoft Learn

No Comments

Leave a Reply

Knulle mamma eldre porno gratis sprute orgasme naturlige pupperComo melhorar a performance de uma Azure Function usando SQL binding

Contact Us

We are always here to help. Please reach out to us and we'll get back to you as soon as possible.

Address:

Maruti Court, Ground Floor
Mvuli Road, Nairobi, Kenya

Contacts:

+254716973110

office@digitalexperts.co.ke

Hours:

Mon-Fri 8am – 5pm
Sat 9am – 1am
Sun & Holidays Closed