Exporting backup of Azure SQL database via Azure automation through Managed identity Auth

Question

Hi All,

I am referring the below MSFT doc : https://learn.microsoft.com/en-us/powershell/module/az.sql/new-azsqldatabaseexport?view=azps-9.5.0 to export a bacpac into Azure blob storage via SQL auth method.

Our organziation plans to disable SQL auth, so we were planning to use managed identity auth but it seems that is not possible as per the Doc.

Can someone confirm on this?

Answer 1

@Priya Jha

No, .bacpac Import/Export does not support authentication types beyond those exposed in the Powershell you linked. You can use SQL auth or ADPassword auths, for the object which is assigned as the AD admin of your SQL Server. If you are only removing SQL auth, you could investigate whether the ADPassword option meets your needs.

 Managed Service Identity does not really make sense for Import/Export. Which identity do you want to use?

 MSIs are used to allow some Azure resources to authenticate to another Azure resource. For instance, if you create a VM, on which you deploy your code, which then authenticates to (for example) your Azure Storage, to do whatever the permissions that identity is assigned. 

In contrast, Import/Export is not a resource to which an identity could be assigned, it is an operation you can take on your server. Behind the scenes, we need to authenticate to your server, but since this is running in our infrastructure there is no logical identity it could use.

I hope this information is helpful

Regards

Geetha

Answer 2

@GeethaThatipatri-MSFT

Hi,

In our company we also plan keep only AD accounts to authenticate user or apps in SQL, and provide Azure Policy to check that state, but that import/export is blocker for us.
Using MSI will be useful, because we have own process for backups into dedicated storage account in different points of time (like deployment with schema migration). We run that process via runbook, where MSI is suggested and soon only possible authentication. Of course we could also pass user/password (from AD) to runbook but it is less secured (we cannot use MFA for that in real service user) and we need do that as exception for our access policy.

Its strange that every direction is to use MSI as authentication method but not for that import/export (looking for WorkloadIdentity for AKS, MSI in runbooks, Active Directory Default as auth type into SQL Server direct from connection string and base client libraries...).