Calling AI models from T-SQL

Embedding models are only one kind of model. To write an answer, classify a product review or extract fields from a supplier's text, a database needs to call a chat model, and there is no built-in AI function for that. The general tool is sp_invoke_external_rest_endpoint, a system stored procedure that sends an HTTPS request to an allowed endpoint and returns the response. Hana used it for every model call that was not an embedding, and learned that most of the work is not the call itself but the credential, the permissions and the error handling around it.

Credentials that live in the database

Both external models and REST calls authenticate with a database scoped credential. The credential's name must be a URL: it must use HTTPS, contain no query string, match the protocol and host of the endpoint being called, and be the same as or more general than the request path. The secret is protected by the database master key, so a master key must exist before the first credential is created. There are four kinds of identity for these credentials.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password from Key Vault>';   -- once per database

CREATE DATABASE SCOPED CREDENTIAL [https://hf-aoai-cat.openai.azure.com/]
WITH IDENTITY = 'Managed Identity',
     SECRET   = '{"resourceid":"https://cognitiveservices.azure.com"}';

With Managed Identity, the database server's identity in Microsoft Entra ID must be granted a role on the model resource. For calls that only use a deployed model, the Cognitive Services OpenAI User role is enough; the documentation for external models also mentions the broader Contributor role. Sunil Rao, the security manager, insisted on the narrower User role and on managed identity everywhere: no API key sits in the database, nothing needs rotating, and turning off the role assignment stops every call at once. On SQL Server 2025, managed identity works through Azure Arc, and an administrator must also enable the allow server scoped db credentials option.

Which endpoints are allowed

In Azure SQL Database and Managed Instance, sp_invoke_external_rest_endpoint can only call endpoints on an allowlist of Azure service domains, including Azure OpenAI at *.openai.azure.com, Azure Functions and App Service at *.azurewebsites.net, API Management at *.azure-api.net, Azure Storage, Event Hubs and Microsoft Graph. To reach anything else, the documentation recommends putting it behind Azure API Management, which also adds rate limiting, logging and a single place to change the back end. Outbound firewall rules on the logical server can narrow the allowed destinations further, which Sunil set so the catalog server can reach only the Harrow & Finch model resource. SQL Server 2025 has no built-in allowlist but has the feature off by default.

Permissions

Calling the procedure needs the database permission EXECUTE ANY EXTERNAL ENDPOINT, and using a credential needs REFERENCES on that credential. Hana granted both to a role used only by the Ask Finch stored procedures, not to the application login, so the application can run the procedures but cannot call arbitrary endpoints with its own SQL. On SQL Server 2025 and Managed Instance, the server option external rest endpoint enabled must be turned on first, which needs ALTER SETTINGS; Azure SQL Database and Fabric have it on by default.

CREATE ROLE role_ai_caller;
GRANT EXECUTE ANY EXTERNAL ENDPOINT TO role_ai_caller;
GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::[https://hf-aoai-cat.openai.azure.com/] TO role_ai_caller;

-- The procedure owner holds the role; the app only gets EXECUTE on the procedure
ALTER ROLE role_ai_caller ADD MEMBER ai_proc_owner;

Because the procedure runs as its caller by default, Hana created the Ask Finch procedures WITH EXECUTE AS a dedicated user, ai_proc_owner, that holds the AI caller role. The application gets EXECUTE on the procedures and nothing else. Lesson 10 returns to this pattern.

Making a call

The procedure takes a URL, a method that defaults to POST, optional headers as flat JSON, a payload, a credential name, a timeout in seconds and an output parameter for the response. The default timeout is 30 seconds and the range is 1 to 230. A retry count from 0 to 10 retries automatically on HTTP 408, 429, 500, 502, 503 and 504, honoring a Retry-After header when the service sends one; with retries, the timeout becomes a total for all attempts.

DECLARE @payload nvarchar(max) = JSON_OBJECT(
    'messages': JSON_ARRAY(
        JSON_OBJECT('role':'system', 'content':'Classify the review as positive, negative or mixed. Reply with one word.'),
        JSON_OBJECT('role':'user',   'content':@reviewText)),
    'max_tokens': 5,
    'temperature': 0);

DECLARE @response nvarchar(max), @rc int;
EXEC @rc = sp_invoke_external_rest_endpoint
     @url        = N'https://hf-aoai-cat.openai.azure.com/openai/deployments/chat-small/chat/completions?api-version=2024-10-21',
     @method     = 'POST',
     @credential = [https://hf-aoai-cat.openai.azure.com/],
     @payload    = @payload,
     @timeout    = 20,
     @retry_count = 2,
     @response   = @response OUTPUT;

SELECT @rc AS return_code,
       JSON_VALUE(@response, '$.response.status.http.code') AS http_code,
       JSON_VALUE(@response, '$.result.choices[0].message.content') AS label;

The return value is 0 for any 2xx response; for other HTTP statuses it is the status code, and if the call cannot be made at all, an error is raised. The response document has two parts: response, holding the HTTP status code, description and headers, and result, holding whatever the service returned. A chat completion's text is in result.choices[0].message.content; token counts are in result.usage, which Hana logged for every call, because they are what the company pays for.

Reading the response safely

A model's answer is text, and text can be anything. Asking for one word does not guarantee one word. Hana's procedures treat the model's output as untrusted input: they check the HTTP code first, read only the fields they expect with JSON_VALUE or OPENJSON, compare the answer with a list of allowed values, and record anything unexpected for review rather than storing it. Where the model supports structured output, which constrains replies to a supplied JSON schema, she used it, and the DP-800 outline mentions structured output as one of the capabilities to evaluate when choosing a model.

import json

response = json.loads("""{
  "response": {"status": {"http": {"code": 200, "description": "OK"}}},
  "result": {"choices": [{"message": {"role": "assistant", "content": " Mixed. "}}],
             "usage": {"prompt_tokens": 96, "completion_tokens": 2, "total_tokens": 98}}
}""")

allowed = {"positive", "negative", "mixed"}
code = response["response"]["status"]["http"]["code"]
raw = response["result"]["choices"][0]["message"]["content"]
label = raw.strip().strip(".").lower()

print("http code:", code)
print("raw answer:", repr(raw))
print("cleaned label:", label, "| allowed:", label in allowed)
print("tokens billed:", response["result"]["usage"]["total_tokens"])

http code: 200
raw answer: ' Mixed. '
cleaned label: mixed | allowed: True
tokens billed: 98

The same checks in T-SQL use TRIM, LOWER and a lookup table of allowed labels. The cleaning step matters: during the prototype the model replied Mixed, mixed., MIXED and once Mixed, leaning negative. Without validation, the last one would have created a fourth category in the review report.

Limits and throttling

Calls to external endpoints share the database's resources, so the engine limits them. The payload, sent and received, can be up to 100 MB; the URL up to 8 KB; request and response headers up to 8 KB in total. Concurrent calls are capped at 15 in 100 of the worker threads, up to 175; beyond that, a call fails with error 10928, or 10936 when elastic pool limits are hit. While a call is waiting, its session shows the HTTP_EXTERNAL_CONNECTION wait type, which is how Elena spotted, in the wait statistics, that a batch job was spending most of its time waiting on the model rather than on the database.

Model services have their own limits, usually tokens per minute and requests per minute for each deployment. Exceeding them returns HTTP 429. A retry count of two or three, with the service's Retry-After header honored, handles short bursts; a sustained overload needs fewer calls, a larger quota or a queue. Lesson 11 shows how Hana estimated the load.

Errors the procedure must expect

Hana listed the failures she saw during the prototype and how the procedures handle each. A 401 or 403 meant the managed identity had lost its role, once because a colleague tidied role assignments in the test subscription; the procedure logs it and returns search results only, and an alert fires on the first occurrence. A 429 meant the deployment's quota was exhausted; one retry usually succeeded. A 400 meant the request was malformed, almost always because a question contained a character that broke hand-built JSON, which is why every payload is built with JSON_OBJECT and JSON_ARRAY rather than string concatenation. A timeout meant the model was slow; the fallback applies. And error 10928 meant too many concurrent outbound calls from a load test, which the pilot's traffic will never approach.

Each case writes a row to the request log with the HTTP code, the elapsed time and the step that failed. Elena reviews the log weekly with the same eye she brings to SQL Agent job history: a failure that happens once is noise, a failure that repeats is a problem with an owner.

One more habit came from the review. Every procedure that calls a model records the deployment name and the API version it used in the request log, because both change over time. When Microsoft retires an API version or the team moves a deployment to a newer model, the log shows exactly which procedures and which share of traffic are affected, and the change can be tested on the procedures that use it before it reaches the rest. Hana keeps the endpoint URLs in a small configuration table read by the procedures, rather than in each procedure's code, so a version change is one update instead of a code release.

External models or REST calls

Embeddings can be generated either way: through AI_GENERATE_EMBEDDINGS with an external model, or by calling the embeddings endpoint with sp_invoke_external_rest_endpoint and reading result.data[0].embedding. The external model is simpler, can be granted with EXECUTE like any object, works inline in SELECT and UPDATE, and can be swapped by altering one object. The REST procedure gives full control over the request and is the only option for chat models, classification and anything that is not an embedding. Hana used external models for embeddings and the REST procedure for everything else.