Azure SQL sp_invoke_external_rest_endpoint to APIM 401 error

Question

I have set up a published API in APIM to query the Azure Maps API from Azure SQL.

The Maps API returns a 200 response in test in APIM

In Azure SQL, I have

1. created the sql user from entra,
2. granted reference on data scoped credential to the user
3. created the database scoped credential to the url/function WITH IDENTITY = 'HTTPEndpointHeaders', secret = '{"Ocp-Apim-Subscription-Key":"**********"}'; running below:

   declare @ret int, @response nvarchar(max);
   exec @ret = sp_invoke_external_rest_endpoint 
       @url = N'https://*******-api.azure-api.net/search/search/address/json?api-version=1.0&query=15127%20NE%2024th%20Street,%20Redmond,%20WA%2098052',
       @method = 'GET', 
       @credential = [https://******-api.azure-api.net/search],
       @response = @response OUTPUT  
   
   SELECT @ret AS ReturnCode, @response AS Response;
   
   
   

i get a 401 error but the subscription key is copied and pasted from APIM / API / Test / HHTP Request pane

Suggestions on how to resolve?

Thanks

Shaun

Answer 1

figured it out, which is contrary to the documentation - you need to pass both the subscription key and the APIM subscription key from sql to APIM not just the APIM subscription key