Create Bearer Token with Permissions for Azure Data Sync Api Trigger

Question

I have a configured and functioning Azure Data Sync, that I would like to trigger on demand by an API call.

I used the learn.microsoft.com tryit functionality at https://learn.microsoft.com/en-us/rest/api/sql/2021-11-01/sync-groups/trigger-sync?tabs=HTTP#code-try-0 to build my API call.

That functionality returns a Bearer Token for access and API calls function until the token expires.

I set up a second API to get a new bearer token, by defining an application in my account with a shared secret. That API returns a bearer token, but when I use it in the API to trigger the Data Sync, I receive permission error messages.

In the token request I initally tried my application id in scope to get a token, that resulted in

The access token has been obtained for wrong audience or resource '622....330'. It should exactly match with one of the allowed audiences 'https://management.core.windows.net/','https://management.core.windows.net','https://management.azure.com/','https://management.azure.com

I then changed the scope to be https://management.azure.com

ClientID does not have authorization to perform action 'Microsoft.Sql/servers/databases/syncGroups/triggerSync/action' over scope '/subscriptions/...fd2e,,,/resourceGroups/Default-SQL-WestUS/providers/Microsoft.Sql/servers/iv...f/databases/dbname/syncGroups/syncname'

I have tried adding permissions for

Access Azure Service Management Azure SQL Database Microsoft Graph

with no change in results.

I don't know if this is an issue of selecting the correct scope when requesting the Bearer token or assigning additional / correct permissions to the Application with the shared secret. Or am I approaching getting the Bearer token the wrong way.

Thanks,

Jim

Answer 1

I tried to reproduce the same in my environment and got below results:

I created one Azure AD application and granted API permission like below:

I generated an access token via postman with below parameters

POST
https://login.microsoftonline.com/cdf429fe-37a2-4a79-8e40-XXXXXX/oauth2/v2.0/token
  
client_id:abbc8b66-7bb9-4901-b04c-xxxxx  
scope:https://management.azure.com/.default 
client_secret: OzE8QXXXXX
grant_type:client_credentials

Response:

When I try to run the query, I got same error as you like below:

POST https://management.azure.com/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Sql/servers/{serverName}/databases/{databaseName}/syncGroups/{syncGroupName}/triggerSync?api-version=2021-11-01

To resolve this issue, you need to assign SQL DB contributor role to your application like below.

Go to Azure Portal -> Your subscription -> Access control (IAM) -> Add role assignment

You can assign SQL DB Contributor role role to your application by selecting it as below:

After assigning the role, I generated the token again and got response successfully when I ran the same query.

You can try the same in your environment by assigning SQL DB Contributor role to your application.