Using REST API to pause Azure SQL Data Warehouse - “Bearer authorization error='invalid_token', error_description='The authentication scheme of not supported”

  • Article

 

A customer of mine recently approached me for assistance to help resolve an Authorization error he was receiving , when he was running a custom prepared automation script in C# that would pause and restart the SQL data warehouse.

To save costs, you can pause and resume compute resources on-demand.  He had tried the instructions provided here https://azure.microsoft.com/en-in/documentation/articles/sql-data-warehouse-manage-compute-rest-api/

The URL created looked like this:
https://management.azure.com/subscriptions/<subscription ID>/resourceGroups/<resource group name>/providers/Microsoft.Sql/servers/<servername>.database.windows.net/databases/<dbname>/pause?api-version=2014-04-01-preview+HTTP%2F1.1

It asked for an Authorization option and he provided the Authorization by following the link.
https://msdn.microsoft.com/en-IN/library/azure/dn798668.aspx

After getting the Authorization key, when he executed the REST API to pause the SQL database from REST client It gave the following error

“Bearer authorization_uri='https://login.windows.net/xxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxx', error='invalid_token', error_description='The authentication scheme of eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsIng1dCI6Ik1uQ19WWmNBVGZNNXBPWWlKSE1iYTlnb0VLWSIsImtpZCI6Ik1uQ19WWmNBVGZNNXBPWWlKSE1iYTlnb0VLWSJ9.eyJhdWQiOiJodHRwczovL21hbmFnZW1lbnQuYXp1cmUuY29tLyIsImlzcyI6Imh0dHBzOi8vc3RzLndpbmRvd3MubmV0LzY4MjE5NTc0LWZjN2EtNGY0Yy05ZjY5LWYyZDcwMjZkMmY0MC8iLCJpYXQiOjE0Njk3NjU2OTgsIm5iZiI6MTQ2OTc2NTY5OCwiZXhwIjoxNDY5NzY5NTk4LCJhcHBpZCI6IjEyZmQ0MTU1LWU4YTEtNGEyMi1iOTE3LWRiMWE3YzNmYjcyZSIsImFwcGlkYWNyIjoiMSIsImlkcCI6Imh0dHBzOi8vc3RzLndpbmRvd3MubmV0LzY4MjE5NTc0LWZjN2EtNGY0Yy05ZjY5LWYyZDcwMjZkMmY0MC8iLCJvaWQiOiI2YWU3ZjkyMy0xMTBjLTQ3ZDItODg0Yi05M2I5ZmM4OThiMGMiLCJzdWIiOiI2YWU3ZjkyMy0xMTBjLTQ3ZDItODg0Yi05M2I5ZmM4OThiMGMiLCJ0aWQiOiI2ODIxOTU3NC1mYzdhLTRmNGMtOWY2OS1mMmQ3MDI2ZDJmNDAiLCJ2ZXIiOiIxLjAifQ.BfIsC85k4HZTIOnSaM_MqnvJHUJG8eBUhSPxKTm4qgkPQ366DN4chQeNoLIz51Gmd4cxZqcUR7zf8ZCA8iJMe9Per-tTUoyHoYHUqVt07d0HKmsHELEsFuEAveAFZM6jL_3oYHPBOQvmvzrsHnjMxVU2JL7e1_V8j9qADpMi3G1GuimsyQU2ObojhV1FkVOoFJD7p2BaBDOfHRuyJ_r32SIdYc35D7yyrT6lJ7Kdb6ZZrtPFjMaxiecSTe-_nvMD2O6rJBoA_bo2gTaf8nlW6PJIfovGPyvCceqKo1wAMbrPbEpHKJYryfP42v-iz81yr_Y51Ye0teumsmwJdPJKvQ is not supported”

 

 

Steps to resolution:

+++++++++++++++++++++++++++++++++

A simple way of testing whether the REST API calls are working is by using POSTMAN client which is an add-on to Google chrome browser. It is an intuitive user interface that would make testing APIs effortless

My colleague Suhas recently blogged about using Testing Azure REST API using POSTMAN Client - The steps and the screenshots to setup the environment to test the Azure REST API using POSTMAN Client is described very well in his blog https://blogs.msdn.microsoft.com/suhaspv/2016/11/22/geo-replication-fail-over-testing-using-rest-api/

I decided to give this a shot to save time for testing!

 

 

I was able to test the scenario out using the client (Postman app) and was able to pause and unpause my Azure SQL DW successfully by altering the REST API to our scenario

I used the REST API from the following: https://azure.microsoft.com/en-in/documentation/articles/sql-data-warehouse-manage-compute-rest-api/

Rest API calls used:

POST https://management.azure.com/subscriptions/\<subscriptionid>/resourceGroups/<resourcegroupname>/providers/Microsoft.Sql/servers/<Dwservername>/databases/<dbname>/resume?api-version=2014-04-01-preview

 POST https://management.azure.com/subscriptions/\<subscriptionid>/resourceGroups/<resourcegroupname>/providers/Microsoft.Sql/servers/<Dwservename>/databases/<dbname>/pause?api-version=2014-04-01-preview

Note: I did not use the api-version=2014-04-01-preview HTTP/1.1 as mentioned in the above article as I was getting an error with it. So you may want to try with both to be sure.

 

 

However my customer was still receiving an error like the below when he tried attempt to generate a Request Token in Postman even though he provided a Manage Service API Access

Additional technical information:

Correlation ID: 6e4eda78-e520-496e-b897-0a397d46b325

Timestamp: 2016-08-01 14:03:44Z

TESTAPP90093: This application requires application permissions to another application. Consent for application permissions can only be performed by an administrator. Sign out and sign in as an administrator or contact one of your organization's administrators.

 

This error message here was more descriptive than the earlier authorization error and gave us a clear idea that we were missing some admin access.

 

++ We found that the customer was a Co-Admin on the subscription but was still giving us this error

We figured that - A subscription co-administrator does not automatically make you an Azure AD administrator and You'd need explicit role grant on the target Azure AD.

Once the customer was granted Global Administrator in the Azure AD domain he was able to generate the token without the error

 

After this we were able to successfully Pause/Resume the Azure SQL DW using the calls above.

More info on different AD roles: https://docs.microsoft.com/en-us/azure/active-directory/active-directory-assign-admin-roles

 

Now that we had it working with the Postman Client the customer was able to use the REST API calls above in his automation script in C#:

OAuth 2.0 is what we need which is explained in below article https://azure.microsoft.com/en-us/documentation/articles/resource-manager-api-authentication/#connect-subscription-to-application

 

Hope this helps! J