question

EdgarWaltherITsmart-2739 avatar image
0 Votes"
EdgarWaltherITsmart-2739 asked EdgarWaltherITsmart-2739 answered

What permissions are required to scale a database?

Hi all,

I try to find what permissions are required to scale a database. But I cannot find that anywhere.

In my particular case, I run a powershell command from azure data factory and that fails, with the following error:

Failure type: User configuration issue
Details: Operation on target Get current database properties failed: {"error":{"code":"AuthorizationFailed","message": "The client 'xxxx-xxxx-xxxx' with object id 'xxxx-xxxx-xxxx' does not have authorization to perform action 'Microsoft.Sql/servers/databases/read' over scope '/subscriptions/xxxx-xxxx-xxxx/resourceGroups/XXXX/providers/Microsoft.Sql/servers/xxxx.database.windows.net/databases/DBNAME' or the scope is invalid. If access was recently granted, please refresh your credentials."}}

My ADF currently has the role 'SQL Server Contributor' on the azure sql server. But it looks like that is not enough?

Can anyone help me out here?

Many thanks,
Edgar Walther

azure-data-factoryazure-sql-database
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered PRADEEPCHEEKATLA-MSFT converted comment to answer

You need to add the Contributor role as you already did. Here you will find another way of doing the same using a Web Activity.

Another way to try the same is using a pipeline as explained here.



· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for your answer.

ADF is now 'Contributor' on the azure sql server, not just 'SQL Server Contributor'.
I also read that the the adf service principal needs to have the db_owner database role inside the database, to be able to execute the related aklter database statement. But that is also done.
But it still does not work.

Any other suggestions are most welcome!

0 Votes 0 ·
PRADEEPCHEEKATLA-MSFT avatar image PRADEEPCHEEKATLA-MSFT EdgarWaltherITsmart-2739 ·

Hello @EdgarWaltherITsmart-2739,

After granting 'Contributor' role to ADF, are you still experiencing the same error message as shown above or different error message?

If it's different, please do share the error message?

0 Votes 0 ·

I still get the same error message.

0 Votes 0 ·
Show more comments

After more investigation I found that the process is in fact using a web activity calling the “Azure Resource Manager REST API”.
It gets info about sql server and database and puts settings to change the scale level.

Maybe there are specific permisions that need to be set to be able to use the “Azure Resource Manager REST API”?

Thanks.

0 Votes 0 ·
PRADEEPCHEEKATLA-MSFT avatar image PRADEEPCHEEKATLA-MSFT EdgarWaltherITsmart-2739 ·

Hello @EdgarWaltherITsmart-2739,

In order to repro from our end, could you please helps with the script which you are trying to run in order to scale the DB?

0 Votes 0 ·
EdgarWaltherITsmart-2739 avatar image
0 Votes"
EdgarWaltherITsmart-2739 answered

This is the relevant part of the json definition of the ADF pipeline:


 {
                             "name": "Scale up database to requested service objective",
                             "type": "WebActivity",
                             "dependsOn": [],
                             "policy": {
                                 "timeout": "0.02:00:00",
                                 "retry": 1,
                                 "retryIntervalInSeconds": 30,
                                 "secureOutput": false
                             },
                             "userProperties": [],
                             "typeProperties": {
                                 "url": "https://management.azure.com/subscriptions/<subscription guid>/resourceGroups/<rg name>/providers/Microsoft.Sql/servers/<sqlserver name>/databases/<database name>?api-version=2021-02-01-preview",
                                 "method": "PUT",
                                 "headers": {
                                     "Content-Type": "application/json"
                                 },
                                 "body": {
                                     "sku": {
                                         "name": "GP_S_Gen5_20"
                                     },
                                     "location": "UK South"
                                 },
                                 "authentication": {
                                     "type": "MSI",
                                     "resource": "https://management.azure.com"
                                 }
                             }
                         }
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EdgarWaltherITsmart-2739 avatar image
1 Vote"
EdgarWaltherITsmart-2739 answered

The issue on our end is solved. The subscription guid appeared to be wrong in the url.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.