Theoretically, Azure Data Studio is all you need to manage Azure SQL databases on Linux, but it doesn't seem to work that way. I frequently end up with "login failed for token-identified-principal" after being connected for only a few minutes. This makes using Azure Data Studio an exercise in frustration, wondering what combination of operations will result in ADS just conking out on you until your token expires.
Azure SQL doesn't support the standard T-SQL BACKUP and RESTORE commands, so getting a copy of your database for local development is somewhat challenging. The Azure Portal requires the AAD admin credentials, not something I want to give out to developers. Enter SqlPackage, which I have started preferring to both the Azure portal and ADS. SqlPackage for Linux is available at https://aka.ms/sqlpackage-linux.
A first attempt at copying the database from Azure might look like this:
sqlpackage /a:export /scs:"Data Source=...Initial Catalog=..." /ua:true /tid:"mytenant.onmicrosoft.com"
Indeed, that's what I attempted. But UniversalAuthentication (required to authenticate with MFA) doesn't seem to work as expected on Linux. You would expect that a browser would be launched to login to the application (since MFA won't work without a browser). However, the command above doesn't work.
Looking through the SqlPackage help, I see that there is a /AccessToken option. We can manually obtain an access token and use it to authenticate. Access tokens can be obtained using the Azure CLI, using the command az account get-access token --resource https://database.windows.net/. This will return a JSON body containing (among other things) the access token to use. Copy and paste this into an environment variable ACCESS_TOKEN, and then the full sqlpackage invocation becomes:
sqlpackage /a:export /at:"$ACCESS_TOKEN" /scs:"Data Source=..." /ua:True ...
Top comments (0)