Azure SQL Database sp_invoke_external_rest_endpoint Failing (401 Unauthorized) with Managed Identity for OpenAI

Question

Environment:

• Azure SQL Database
• Azure OpenAI Service
• System Assigned Managed Identity (SAMI)

The Issue: I am attempting to call an Azure OpenAI endpoint directly from an Azure SQL Database using the sp_invoke_external_rest_endpoint system stored procedure. Despite configuring the Managed Identity, the execution is failing with a 401 Unauthorized error.

Steps Taken:

1. Enabled the System Assigned Managed Identity for the Azure SQL logical server.
2. Granted the SQL Server's Managed Identity the Cognitive Services OpenAI User role on the Azure OpenAI resource via Azure RBAC.
3. Waited 15+ minutes for RBAC propagation.

T-SQL Executed:

DECLARE @url NVARCHAR(4000) = 'https://<my-resource>.openai.azure.com/openai/deployments/<my-deployment>/completions?api-version=2023-05-15';
DECLARE @payload NVARCHAR(MAX) = N'{"prompt": "Generate a test response.", "max_tokens": 50}';
EXEC sp_invoke_external_rest_endpoint
    @url = @url,
    @method = 'POST',
    @credential = [https://<my-resource>.openai.azure.com],
    @payload = @payload; 

Error Received:

Msg 31921, Level 16, State 1, Procedure sp_invoke_external_rest_endpoint, Line 5 The REST endpoint returned an error. Status code: 401. Description: Unauthorized.

Has anyone successfully configured this integration using Managed Identities without passing explicit API keys in the database credentials? Are there additional database-scoped credentials required even when SAMI is enabled at the server level?

Answer 1

Thanks Ronn, so glad that got you unblocked!

To answer your follow-up: nope, you actually don't need to touch that SECRET payload at all.

You can't pass a client_id in the JSON because the database engine will literally just ignore it. Instead, Azure SQL handles the routing magically on the server side:

If you attach a User-Assigned Managed Identity (UAMI) to the server, it automatically prioritizes it over the System-Assigned one.

If you happen to have a bunch of UAMIs attached, the system relies strictly on whichever one you designated as the Primary identity at the logical server level.

So, your T-SQL setup stays exactly the same. You just manage the switch on the infrastructure side.

The Configuration

When you are ready to make the switch, just pop open Cloud Shell and run a quick update to ensure your new UAMI is set as the primary.

# Assign the UAMI to the SQL logical server and set it as the primary identity
az sql server update \
  --resource-group "YourResourceGroup" \
  --name "YourSqlServerName" \
  --assign-identity "YourUAMIResourceId" \
  --primary-user-assigned-identity-id "YourUAMIResourceId" 

Once that is set, your existing DATABASE SCOPED CREDENTIAL will just automatically piggyback on that primary UAMI to fetch the token. No database code changes required!

Answer 2

Hey Ronn! This is a super common gotcha when setting up this integration.

The 401 error is happening because the Entra ID token your Azure SQL Database is requesting doesn't have the correct "audience" claim for Cognitive Services.

When you create the DATABASE SCOPED CREDENTIAL using a System Assigned Managed Identity, you have to explicitly tell it which resource ID you are targeting. You do this by passing a JSON payload in the SECRET parameter.

The Fix

Just drop your current credential and recreate it with the explicit Cognitive Services resource ID like this:

-- Drop the existing incomplete credential
DROP DATABASE SCOPED CREDENTIAL [https://<my-resource>.openai.azure.com];
GO
-- Recreate it with the required Cognitive Services audience
CREATE DATABASE SCOPED CREDENTIAL [https://<my-resource>.openai.azure.com]
WITH IDENTITY = 'Managed Identity',
SECRET = '{"resourceid": "https://cognitiveservices.azure.com"}'; 
GO

Since you've already handled the Cognitive Services OpenAI User RBAC assignment, running this quick update will get your sp_invoke_external_rest_endpoint call authenticating perfectly!