new-azsqldatabaseexport error: "Unable to authenticate request" -AuthenticationType AdPassword

Question

Hello together,

i have written a powershell script to export azure SQL databases into a blob.
I use the parameter new-azsqldatabaseexport -AuthenticationType AdPassword ...
But I get an error: "Unable to authenticate request "
The Azure-AD user has the RBAC role "SQL Server Contributor" on the subscription.
Do I have to authorize the user additionally in the SQL Server?
And if so, what are the minimal permissions?

It works if I put the user in the SQL-Active Directory admin group, but I don't want to make a User used in a script an admin.

Thanks and best regards

Guido

Answer 1

Hi @Guido Jeuken , For Azure Active Directory (AAD) authentication (from the lowest possible set of permissions to the SQL AD Admin, must be an entity of the same tenant or added as a guest to the authorizing tenant. So yes, if you want to leverage Azure AD, the entity being used in the script must be added to an AD Group that has been granted a specific role or combination of RBAC roles.

In your specific case, the specific RBAC role should be SQL DB Contributor instead of SQL Server Contributor.

You should not have to add any additional permissions or configurations specifically to the database (i.e. run T-SQL against the specific database) but if you want to use an AD Group, this AD entity in the script must be part of the AD Group. You do not need to use the SQL Admin account. You can use the Service Principle approach, which plays well with PowerShell automation. The Service Principle can be assigned the applicable RBAC role instead of having to modify/limit permissions of an account that might be used for other tasks (like the AD Admin account).

Please see: Azure AD Service Principal authentication to SQL DB - Code Sample