Using the Service Management REST API To generate SQL Database in SQL Server

Question

I am trying to create and delete database for dev/test/prod with the use of the Service Management REST API when needed.

I made an application and followed the steps provided in: https://learn.microsoft.com/en-us/rest/api/azure/. I managed to get it working and could create and delete databases. In my Synapse notebook I added a condition does_database_exist that would use the REST API to acquire list of databases in a specific server and checks whether the new/old database_name is in this list.

I was testing the notebook until I got the error below. After reading the error it looks like my application suddenly doesn't have the appropriate rights to use the API. The application has the following roles SQL DB Contributor/SQL Server Contributor. It used to work with these specific rights. I have also tried to give these rights at the resource group/ subscription level, but with no success.

Also, the scope seems valid as I haven't changed it since it work and the credentials are valid until next year (I also tried regenerating the client credentials).

{"error":{"code":"AuthorizationFailed","message":"The client '{application_object_id}' with object id '{application_object_id'} does not have authorization to perform action 'Microsoft.Sql/servers/{sql_server}/read' over scope '/subscriptions/{subscription}/resourceGroups/{resource_group}/providers/Microsoft.Sql/servers/{sql_server}/databases' or the scope is invalid. If access was recently granted, please refresh your credentials."}}

I have been stuck for the past 3 days on this and don't know how to proceed. Does anyone know any direction for me to troubleshoot?

One of my thoughts: Based on the does_database_exist condition I check whether my database is created and once created it will start inserting data into the database. I started with a sleep() between every does_database_exist check and was curious if the removal of the sleep() might have caused the application to be flagged by sending to much requests.       

import time

check = False
while check == False:
check = database.does_database_exist

time.sleep(5)
if database.does_database_exist == True:   

check = True

print('Database is made!')           

I am sorry for the formatting, the code block is only showing part of the code when I save the question.

Answer 1

@Kevin van der Gugten - Thanks for the question and using MS Q&A platform.

Based on the error message you provided, it looks like your application does not have the appropriate rights to use the API. You mentioned that you have already given your application the SQL DB Contributor/SQL Server Contributor roles, but it still doesn't work.

One thing you can try is to check if the scope of your application is correct. The error message indicates that the scope might be invalid. Make sure that the scope of your application is set to '/subscriptions/{subscription}/resourceGroups/{resource_group}/providers/Microsoft.Sql/servers/{sql_server}/databases'.

Another thing you can try is to refresh your credentials. The error message suggests that if access was recently granted, you should refresh your credentials. You can regenerate the client credentials and try again.

Regarding your thought about the sleep() function causing the issue, it is possible that sending too many requests in a short amount of time could cause your application to be flagged. However, it is difficult to say for sure without more information.

I hope this helps! Let me know if you have any other questions.