REST API call does not list SQL resources on Azure subscription

Published 11-18-2020 07:13 AM 871 Views
Microsoft

The Symptoms

Recently I came to a support issue where we tried to use REST API to list SQL resources on an Azure subscription. The output returned results, but it did not show any of the SQL resources that we expected to see. Filtering the result with a GREP only brought up a storage account that had "SQL" in its name, but none of the servers or databases.

 

These are the commands that were used:

 

az rest -m get -u 'https://management.azure.com/subscriptions/11111111-2222-3333-4444-555555555555/resources?api-version=2020-06-01'
GET https://management.azure.com/subscriptions/11111111-2222-3333-4444-555555555555/resources?api-version=2020-06-01

 

Though the issue was noticed first for SQL resources, it might actually occur for any other resource type within an Azure subscription. Please see the Cause section below to see why.

 

The Troubleshooting

The Azure portal showed the full list of Azure SQL servers and databases, for either drilling down through the subscription or going directly to the "SQL servers" or "SQL databases" blades. Commands like az sql db list or az sql server list also returned all SQL resources. Permission issues were excluded by using an owner account for subscription and resources. And it turned out that only one specific subscription was affected, whereas it worked fine for all other subscription.

 

The Cause

Some list operations divide the result into separate pages when too much data is returned and the results are too large to return in one response. A typical size limit is when the list operation returns more than 1,000 items.

In this specific case, the subscription contained so many resources that the SQL resources didn't make it onto the first result page. It required using the URL provided by the nextLink property to switch to the second page of the resultset.

 

Note though that this issue is not specific to SQL resources. It might occur to any resource that happen to appear on the second or following page of the resultset. 

 

The Solution

When using list operations, a best practice is to check the nextLink property on the response. When nextLink isn't present in the results, the returned results are complete. When nextLink contains a URL, the returned results are just part of the total result set. You need to skip through the pages until you either find the resource you are looking for, or have reached the last page.

 

The response with a nextLink field looks like this:

 

{
  "value": [
    <returned-items>
  ],
  "nextLink": "https://management.azure.com:24582/subscriptions/11111111-2222-3333-4444-555555555555/resources?%24expand=createdTime%2cchangedTime%2cprovisioningState&%24skiptoken=eyJuZXh0UG...<token details>...MUJGIn0%3d"
}

 

 

This URL can be used in the "-u" parameter (or --uri/--url) of the REST client, e.g. in the az rest command.

 

Further Information

 

 

%3CLINGO-SUB%20id%3D%22lingo-sub-1901127%22%20slang%3D%22en-US%22%3EREST%20API%20call%20does%20not%20list%20SQL%20resources%20on%20Azure%20subscription%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1901127%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSTRONG%3EThe%20Symptoms%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3ERecently%20I%20came%20to%20a%20support%20issue%20where%20we%20tried%20to%20use%20REST%20API%20to%20list%20SQL%20resources%20on%20an%20Azure%20subscription.%20The%20output%20returned%20results%2C%20but%20it%20did%20not%20show%20any%20of%20the%20SQL%20resources%20that%20we%20expected%20to%20see.%20Filtering%20the%20result%20with%20a%20GREP%20only%20brought%20up%20a%20storage%20account%20that%20had%20%22SQL%22%20in%20its%20name%2C%20but%20none%20of%20the%20servers%20or%20databases.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThese%20are%20the%20commands%20that%20were%20used%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-html%22%3E%3CCODE%3Eaz%20rest%20-m%20get%20-u%20'https%3A%2F%2Fmanagement.azure.com%2Fsubscriptions%2F11111111-2222-3333-4444-555555555555%2Fresources%3Fapi-version%3D2020-06-01'%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-json%22%3E%3CCODE%3EGET%20https%3A%2F%2Fmanagement.azure.com%2Fsubscriptions%2F11111111-2222-3333-4444-555555555555%2Fresources%3Fapi-version%3D2020-06-01%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%3CSTRONG%3EThe%20Troubleshooting%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EThe%20Azure%20portal%20showed%20the%20full%20list%20of%20Azure%20SQL%20servers%20and%20databases%2C%20for%20either%20drilling%20down%20through%20the%20subscription%20or%20going%20directly%20to%20the%20%22SQL%20servers%22%20or%20%22SQL%20databases%22%20blades.%20Commands%20like%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fcli%2Fazure%2Fsql%2Fdb%3Fview%3Dazure-cli-latest%23az_sql_db_list%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Eaz%20sql%20db%20list%3C%2FA%3E%20or%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fcli%2Fazure%2Fsql%2Fserver%3Fview%3Dazure-cli-latest%23az_sql_server_list%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Eaz%20sql%20server%20list%3C%2FA%3E%20also%20returned%20all%20SQL%20resources.%20Permission%20issues%20were%20excluded%20by%20using%20an%20owner%20account%20for%20subscription%20and%20resources.%20And%20it%20turned%20out%20that%20only%20one%20specific%20subscription%20was%20affected%2C%20whereas%20it%20worked%20fine%20for%20all%20other%20subscription.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EThe%20Cause%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3ESome%20list%20operations%20divide%20the%20result%20into%20separate%20pages%20when%20too%20much%20data%20is%20returned%20and%20the%20results%20are%20too%20large%20to%20return%20in%20one%20response.%20A%20typical%20size%20limit%20is%20when%20the%20list%20operation%20returns%20more%20than%201%2C000%20items.%3C%2FP%3E%0A%3CP%3EIn%20this%20specific%20case%2C%20the%20subscription%20contained%20so%20many%20resources%20that%20the%20SQL%20resources%20didn't%20make%20it%20onto%20the%20first%20result%20page.%20It%20required%20using%20the%20URL%20provided%20by%20the%20nextLink%20property%20to%20switch%20to%20the%20second%20page%20of%20the%20resultset.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EThe%20Solution%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EWhen%20using%20list%20operations%2C%20a%20best%20practice%20is%20to%20check%20the%20nextLink%20property%20on%20the%20response.%20When%20nextLink%20isn't%20present%20in%20the%20results%2C%20the%20returned%20results%20are%20complete.%20When%20nextLink%20contains%20a%20URL%2C%20the%20returned%20results%20are%20just%20part%20of%20the%20total%20result%20set.%20You%20need%20to%20skip%20through%20the%20pages%20until%20you%20either%20find%20the%20resource%20you%20are%20looking%20for%2C%20or%20have%20reached%20the%20last%20page.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20response%20with%20a%20nextLink%20field%20looks%20like%20this%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-json%22%3E%3CCODE%3E%7B%0A%26nbsp%3B%20%22value%22%3A%20%5B%0A%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3CRETURNED-ITEMS%3E%0A%26nbsp%3B%20%5D%2C%0A%26nbsp%3B%20%22nextLink%22%3A%20%22https%3A%2F%2Fmanagement.azure.com%3A24582%2Fsubscriptions%2F11111111-2222-3333-4444-555555555555%2Fresources%3F%2524expand%3DcreatedTime%252cchangedTime%252cprovisioningState%26amp%3B%2524skiptoken%3DeyJuZXh0UG...%3CTOKEN%3E...MUJGIn0%253d%22%0A%7D%3C%2FTOKEN%3E%3C%2FRETURNED-ITEMS%3E%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20URL%20can%20be%20used%20in%20the%20%22-u%22%20parameter%20(or%20--uri%2F--url)%20of%20the%20REST%20client%2C%20e.g.%20in%20the%20az%20rest%20command.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EFurther%20Information%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fcli%2Fazure%2Freference-index%3Fview%3Dazure-cli-latest%23az_rest%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Eaz%20rest%3C%2FA%3E%20syntax%3C%2FLI%3E%0A%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Frest%2Fapi%2Fazure%2F%23async-operations-throttling-and-paging%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3EAsync%20operations%2C%20throttling%2C%20and%20paging%3C%2FA%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Version history
Last update:
‎Nov 24 2020 08:20 AM
Updated by: