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

Ayush Shrivastava 60

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.

Nandan Hegde 32,026 Reputation points MVP

2024-08-26T15:31:45.4366667+00:00

Can you please provide the database scoped cred SQL statement?
Ayush Shrivastava 60 Reputation points

2024-08-27T06:19:44.59+00:00

CREATE DATABASE SCOPED CREDENTIAL auth

with identity = 'Managed Identity', secret = '{"resourceid":"{app id of the auth app that I have created under authentication section}"}'
Ayush Shrivastava 60 Reputation points

2024-08-28T06:29:33.89+00:00

@Nandan Hegde Any update here !!
Ayush Shrivastava 60 Reputation points

2024-08-28T06:30:23.6066667+00:00

@Nandan Hegde Any update on the question.
Sai Raghunadh M 150 Reputation points Microsoft Vendor

2024-09-03T07:01:53.2+00:00

Hi @ Ayush Shrivastava,

Just checking in to see if the below answer provided by @Nandan Hegde helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Accepted answer

Nandan Hegde 32,026 Reputation points MVP

2024-08-30T04:23:29.97+00:00

Refer the below aspect :

Creating a database scope cred with random name :

Failure :

Update the cred as per proper URL name :
Please sign in to rate this answer.
Sai Raghunadh M 150 Reputation points Microsoft Vendor

2024-09-04T08:28:22.5566667+00:00

Hi @ Ayush Shrivastava,

Just checking in to see if the above answer provided by @Nandan Hegde helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Ayush Shrivastava 60 Reputation points

2024-09-04T16:17:21.6233333+00:00

Thanks @Nandan Hegde

This works. Do you know what permission my server's managed identity need to trigger the function. It's throwing 403 right now.

It would be helpful if you can share screenshots.

Ayush Shrivastava 60 Reputation points

2024-09-04T16:52:29.1666667+00:00

Although I have configured it to throw 401

it's throwing

"http": {

"code": 403,

"description": "Forbidden"

}

Ayush Shrivastava 60 Reputation points

2024-09-04T21:16:17.43+00:00

@Nandan Hegde following up on my question above

Nandan Hegde 32,026 Reputation points MVP

2024-09-05T03:13:50.8666667+00:00

Can you please specify what permission you have provided the Azure SQL server on the Azure function?

Ayush Shrivastava 60 Reputation points

2024-09-05T06:47:55.55+00:00

Yes Sure,

I used IAM section in the function App and provided contributor since I didn't see any role related to Azure functions.

If I am not mistaken I need to provide Azure function's access to my SQL Server's Managed Identity right ?

Ayush Shrivastava 60 Reputation points

2024-09-05T06:55:11.6133333+00:00

This is the role that I am using.

Ayush Shrivastava 60 Reputation points

2024-09-05T06:56:04.6033333+00:00

Also Nanda do you think we can connect over a meeting to discuss and resolve this one.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

2 additional answers

Sai Raghunadh M 150 Reputation points Microsoft Vendor

2024-08-29T06:16:20.9766667+00:00

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.
Please sign in to rate this answer.
Ayush Shrivastava 60 Reputation points

2024-08-29T07:26:48.86+00:00

I understand this and this is not the issue. Even if I add @credentials it errors out and throws this error.

Sai Raghunadh M 150 Reputation points Microsoft Vendor

2024-08-29T08:26:35.1233333+00:00

Have you granted the necessary permissions to the Managed Identity of your Azure SQL Database to call Azure Function?

Ayush Shrivastava 60 Reputation points

2024-09-04T19:00:10.44+00:00

That is where I am struggling.

@Sai Raghunadh M It's not very clear what permission is needed to call the function
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Nandan Hegde 32,026 Reputation points MVP

2024-08-29T15:59:12.3633333+00:00

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}]
Please sign in to rate this answer.
Ayush Shrivastava 60 Reputation points

2024-08-30T10:48:21.2366667+00:00

Will try this and update here.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

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

2 additional answers

Your answer