Lesson Learned #446: Simplifying SQLPackage Log Debugging with PowerShell
Handling massive SQLPackage diagnostic logs, like those spanning over 4 million rows, can be an overwhelming task when troubleshooting support cases. This article introduces a PowerShell script designed to efficiently parse through SQLPackage diagnostic logs, extract error messages, and save them to a separate file, thus simplifying the review process and enhancing the debugging experience.
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 LearnLesson Learned #213: Cannot set the AccessToken property if the Integrated Security has been set
Today, I worked on a service request where using SQLPackage and Azure Active Directory user our customer got the following error message "Error exporting database:Could not connect to database server. Cannot set the AccessToken property if the 'Integrated Security' connection string keyword has been set to 'true' or 'SSPI'."
