Skip to content

[Doc]: New-AzSqlDatabaseExport Doesn't Contain Guidance for Managed Identity #27631

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Open
jeremy-beeman-smyrnareadymix opened this issue Apr 23, 2025 · 6 comments

Comments

@jeremy-beeman-smyrnareadymix

Type of issue

Missing information

Feedback

Hello,

I have been trying to use the updated New-AzSqlDatabaseExport cmdlet with Managed Identity, but I keep running into:
| Cannot process command because of one or more missing mandatory | parameters: AdministratorLogin AdministratorLoginPassword.

What should these fields be for utilizing ManagedIdentity for the Database Export capability?

Looks like this is the PR that introduced the verbiage about managed identity: #26884

Thank you

Page URL

No response

Content source URL

No response

Author

No response

Document Id

No response

Platform Id

No response

@jeremy-beeman-smyrnareadymix
Copy link
Author

@matthetherington
Copy link

matthetherington commented May 15, 2025

That PR #26884 added ManagedIdentity as allowed Enum values.

Does anyone know why if they can't be used?

The Azure REST API docs for the endpoint this calls have a specific error which makes me hopeful this could actually work:

400 SQLPasswordSpecifiedWithManagedIdentity - administratorLoginPassword should not be specified if authenticationType is ManagedIdentity.

https://learn.microsoft.com/en-us/rest/api/sql/databases/export?view=rest-sql-2023-08-01&tabs=HTTP

@debalinaroy ?

@matthetherington
Copy link

matthetherington commented May 15, 2025

It does work! What a headache - there's zero documentation around this...

It wants a user-assigned MI which is assigned to the SQL server, like this with Bicep:

resource sqlManagedIdentity 'Microsoft.ManagedIdentity/userAssignedIdentities@2025-01-31-preview' = {
  name: 'my-mi-name'
  location: location
  tags: tags
}

resource sqlServer 'Microsoft.Sql/servers@2024-05-01-preview' = {
  name: 'myservername'
  location: location
  tags: tags
  identity: {
    type: 'SystemAssigned,UserAssigned'
    userAssignedIdentities: {
      '${sqlManagedIdentity.id}': {}
    }
  }
  properties: {
    //...
  }

The MI also needs to be an Entra admin for the server.

Grant the MI a Storage Blob Data Contributor role on the storage account, and add a login for it in SQL server:

IF NOT EXISTS(SELECT principal_id FROM sys.database_principals WHERE name = 'my-mi-name') BEGIN
    CREATE USER [my-mi-name] FROM EXTERNAL PROVIDER;
END

ALTER ROLE db_owner ADD MEMBER [my-mi-name];

Then the powershell wants -AuthenticationType ManagedIdentity and -StorageKeyType ManagedIdentity, with AdministratorLogin and StorageKey as the full resource ID to the MI. You need to provide AdministratorPassword so I'm supplying any old value as a SecureString.

$pw = ConvertTo-SecureString "AdministratorPassword" -AsPlainText -Force
$exportRequest = New-AzSqlDatabaseExport `
    -ResourceGroupName $serverResourceGroup `
    -ServerName $serverName `
    -DatabaseName $databaseName `
    -StorageKeyType ManagedIdentity `
    -StorageUri "https://$storageAccountName.blob.core.windows.net/$storageContainer/$fileName.bacpac" `
    -AuthenticationType ManagedIdentity `
    -AdministratorLogin "/subscriptions/f5f0032a-80f8-4564-8235-209695ff5e09/resourceGroups/my-rg/providers/Microsoft.ManagedIdentity/userAssignedIdentities/my-mi-name" `
    -AdministratorLoginPassword $pw `
    -StorageKey "subscriptions/f5f0032a-80f8-4564-8235-209695ff5e09/resourceGroups/my-rg/providers/Microsoft.ManagedIdentity/userAssignedIdentities/my-mi-name"

How are we meant to guess you should pass a resource ID into StorageKey and AdministratorLogin?

I may have spoken too soon, but I can see it's created an entry under Data Management -> Import/Export history in the Azure portal for the Azure SQL instance.

It can take a while according to https://learn.microsoft.com/en-us/azure/azure-sql/database/database-import-export-hang?view=azuresql - I'll update this comment if that doesn't work.

@debalinaroy debalinaroy self-assigned this May 16, 2025
@debalinaroy
Copy link
Contributor

@matthetherington your analysis is correct. We are really sorry for the inconvenience caused to everyone. This is a new feature and we are already working on removing the password requirement for SQL login as well as update public documentation with description on how to use Managed identity with import export.

@matthetherington
Copy link

matthetherington commented May 27, 2025

No worries @debalinaroy - I'm just happy it's working! If I can find the time, I'll PR a change to the docs. I think it would make sense to also change the parameter names, it's quite confusing that parameters named StorageKey and AdministratorLogin expect a resource ID as their value.

Also, the command still validates that a SQL admin password is provided, despite it being unused.

For posterity, both the storage account and SQL server I am interacting with have public access off. I had to make the user-assigned MI an administrator of the SQL server, and the final command I'm using is:

$pw = ConvertTo-SecureString "AdministratorPassword" -AsPlainText -Force
$exportRequest = New-AzSqlDatabaseExport `
    -ResourceGroupName $serverResourceGroup `
    -ServerName $serverName `
    -DatabaseName $copiedDatabaseName `
    -StorageKeyType ManagedIdentity `
    -StorageKey "/subscriptions/$mySubId/resourceGroups/$myRg/providers/Microsoft.ManagedIdentity/userAssignedIdentities/$myMiName" `
    -StorageUri "https://$storageAccountName.blob.core.windows.net/$storageContainer/$serverName/$databaseName/$year/$month/$day/$nowTimestamp.bacpac" `
    -AuthenticationType ManagedIdentity `
    -AdministratorLogin "/subscriptions/$mySubId/resourceGroups/$myRg/providers/Microsoft.ManagedIdentity/userAssignedIdentities/$myMiName" `
    -AdministratorLoginPassword $pw `
    -UseNetworkIsolation $true `
    -SqlServerResourceIdForPrivateLink "/subscriptions/$mySubId/resourceGroups/$myRg/providers/Microsoft.Sql/servers/$myServerName" `
    -StorageAccountResourceIdForPrivateLink "/subscriptions/$mySubId/resourceGroups/$myRg/providers/Microsoft.Storage/storageAccounts/$myStorageAccountName"

As per the docs, I then had to approve the private endpoint connections in the Azure Portal, after which the copy was successful.

@matthetherington
Copy link

matthetherington commented May 27, 2025

I have created a PR to update the powershell docs here: #27852

Edit: remade the PR under #27881

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

4 participants