How to use Managed Identity to call a azure function from Azure SQL Database using sp_invoke_external_endpoint

Question

How to use Managed Identity to call a azure function from Azure SQL Database using sp_invoke_external_endpoint.

When I try to create a credential using this query

CREATE

And then run my rest endpoint to call the Azure function

EXEC sp_invoke_external_rest_endpoint
  @url = N'https://{appName}.azurewebsites.net/api/{functionName}',
  @method = 'POST',
  @credential = {name},
   @payload =  @json_body, 
  @response = @response OUTPUT;

I shows me this error

SQL Error [31630] [S0001]: The database scoped credential '{name}' cannot be used to invoke an external rest endpoint.

Answer 1

Refer the below aspect :

Creating a database scope cred with random name :

Failure :

Update the cred as per proper URL name :

Answer 2

Hi @ Ayush Shrivastava,

Thanks for the question and using MS Q&A platform.

You created a special username and password called a "database scoped credential" that allows your Azure SQL Database to access your Azure Function.

To use this username and password in your call to sp_invoke_external_rest_endpoint, you need to specify the name of the credential in the @credential parameter of the stored procedure. In your case, you named the credential "auth".

So, to use the credential in your call to sp_invoke_external_rest_endpoint, you can replace {name} in your query with the name of the database scoped credential you created, which is "auth". (@credential = {auth} This will allow your Azure SQL Database to authenticate with your Azure Function and call it successfully.

For your better understanding please go through this Document: sp_invoke_external_rest_endpoint (Transact-SQL)

Hope this helps. Do let us know if you have any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful.

Answer 3

Hello Ayush

Apologies to have missed out on this thread.

assuming that you have followed all the steps w.r.t app creation to access provision as stated below :

https://github.com/Azure-Samples/azure-sql-db-invoke-external-rest-endpoints/blob/main/azure-functions.ipynb

The above error comes in case if the database scoped credential name is other then the URL.

Try having the databasecope cred name as below :

[https://{appName}.azurewebsites.net/api/{functionName}]