Skip to content

create-external-model-transact-sql.md

Latest commit

 

History

History
250 lines (177 loc) · 9.42 KB

create-external-model-transact-sql.md

File metadata and controls

250 lines (177 loc) · 9.42 KB
title CREATE EXTERNAL MODEL (Transact-SQL)
description CREATE EXTERNAL MODEL (Transact-SQL) for creating an external model object that contains the location, authentication method, and purpose of an AI model inference endpoint.
author jettermctedder
ms.author bspendolini
ms.reviewer randolphwest
ms.date 04/21/2025
ms.service sql
ms.topic reference
ms.custom
sql-ai
f1_keywords
CREATE_EXTERNAL_MODEL
EXTERNAL MODEL
ai_generate_embeddings
helpviewer_keywords
External
External model
ai_generate_embeddings, external model
dev_langs
TSQL
monikerRange =azuresqldb-current || >=sql-server-ver17 || >=sql-server-linux-ver17

CREATE EXTERNAL MODEL (Transact-SQL)

[!INCLUDE sqlserver2025]

Creates an external model object that contains the location, authentication method, and purpose of an AI model inference endpoint.

Syntax

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

CREATE EXTERNAL MODEL external_model_object_name
[ AUTHORIZATION owner_name ]
WITH
  (   LOCATION = '<prefix>://<path>[:<port>]'
    , API_FORMAT = '<OpenAI, Azure OpenAI, etc>'
    , MODEL_TYPE = EMBEDDINGS
    , MODEL = 'text-embedding-ada-002'
    [ , CREDENTIAL = <credential_name> ]
    [ , PARAMETERS = '{"valid":"JSON"}' ]
  );

Arguments

external_model_object_name

Specifies the user-defined name for the external model. The name must be unique within the database.

owner_name

Specifies the name of the user or role that owns the external model. If not specified, ownership is given to the current user. Depending on permissions and roles, explicit permission needs to be granted to users to use specific external models.

LOCATION

Provides the connectivity protocol and path to the AI model inference endpoint.

API_FORMAT

The API message format for the AI model inference endpoint provider.

Accepted values are:

  • Azure OpenAI
  • OpenAI
  • Ollama

MODEL_TYPE

The type of model being accessed from the AI model inference endpoint location.

Accepted values are:

  • EMBEDDINGS

MODEL

The specific model hosted by the AI provider. For example, text-embedding-ada-002, text-embedding-3-large, or o3-mini.

CREDENTIAL

Indicate which DATABASE SCOPED CREDENTIAL object is used with the AI model inference endpoint. More on accepted credential types and naming rules can be found in sp_invoke_external_rest_endpoint, or in the Remarks section of this article.

PARAMETERS

A valid JSON string that contains parameters to be appended to the AI model inference endpoint request message. For example:

'{"Dimensions" : 1536}'

Permissions

External model creation and altering

Requires ALTER ANY EXTERNAL MODEL or CREATE EXTERNAL MODEL database permission.

For example:

GRANT CREATE EXTERNAL MODEL TO [<PRINCIPAL>];

or

GRANT ALTER ANY EXTERNAL MODEL TO [<PRINCIPAL>];

External model grants

To use an external model in an AI function, a principal must be granted the ability to EXECUTE it.

For example:

GRANT EXECUTE ON EXTERNAL MODEL::MODEL_NAME TO [<PRINCIPAL>];
GO

Remarks

HTTPS and TLS

Only AI model inference endpoints that are configured to use HTTPS with TLS encryption protocol are supported for the LOCATION parameter.

Accepted API formats and model types

The following sections outline accepted API Formats for each MODEL_TYPE.

API_FORMAT for EMBEDDINGS

This table outlines the API Formats and URL endpoint structures for the EMBEDDINGS model type. To view specific payload structures, use the link in the API Format column.

| API format | Location path format | | --- | --- | --- | | Azure OpenAI | https://{endpoint}/openai/deployments/{deployment-id}/embeddings?api-version={date} | | OpenAI | https://{server_name}/v1/embeddings | | Ollama | https://localhost:{port}/api/embed |

Create embedding endpoints

For more information on creating embedding endpoints, use these links for the appropriate AI model inference endpoint provider:

Credential name rules for EXTERNAL MODEL

The created DATABASE SCOPED CREDENTIAL used by an EXTERNAL MODEL must adhere to specific following rules:

  • Must be a valid URL
  • The URL domain must be one of those domains included in the allowlist
  • The URL must not contain a query string
  • Protocol + Fully Qualified Domain Name (FQDN) of the called URL must match Protocol + FQDN of the credential name
  • Each part of the called URL path must match completely with the respective part of URL path in the credential name
  • The credential must point to a path that is more generic than the request URL. For example, a credential created for path https://northwind.azurewebsite.net/customers can't be used for the URL https://northwind.azurewebsite.net

Collation and credential name rules

RFC 3986 Section 6.2.2.1 states that "When a URI uses components of the generic syntax, the component syntax equivalence rules always apply; namely, that the scheme and host are case-insensitive," and RFC 7230 Section 2.7.3 mentions that "all other are compared in a case-sensitive manner."

As there's a collation rule set at the database level, the following logic is applied, to be coherent with the database collation rule, and the RFC mentioned previously. (The described rule could potentially be more restrictive than the RFC rules, for example if database is set to use a case-sensitive collation):

  1. Check if the URL and credential match using the RFC, which means:
    • Check the scheme and host using a case-insensitive collation (Latin1_General_100_CI_AS_KS_WS_SC)
    • Check all other segments of the URL are compared in a case-sensitive collation (Latin1_General_100_BIN2)
  2. Check that the URL and credential match using the database collation rules (and without doing any URL encoding).

Examples

Create an EXTERNAL MODEL with Azure OpenAI

This example creates an EXTERNAL MODEL of the EMBEDDINGS type using Azure OpenAI and uses Managed Identity for authentication.

-- Create access credentials to Azure OpenAI using a key:
CREATE DATABASE SCOPED CREDENTIAL [https://my-azure-openai-endpoint.openai.azure.com/]
    WITH IDENTITY = 'HTTPEndpointHeaders', secret = '{"api-key":"YOUR_AZURE_OPENAI_KEY"}';
GO

-- Create the EXTERNAL MODEL
CREATE EXTERNAL MODEL MyAzureOpenAiModel
AUTHORIZATION CRM_User
WITH (
      LOCATION = 'https://azureopenaiserver.openai.azure.com/openai/deployments/text-embedding-ada-002/embeddings?api-version=2024-02-01',
      API_FORMAT = 'Azure OpenAI',
      MODEL_TYPE = EMBEDDINGS,
      MODEL = 'text-embedding-ada-002',
      CREDENTIAL = [https://my-azure-openai-endpoint.openai.azure.com]
);

Verify the new external model is created, by querying the sys.external_models catalog view.

SELECT * FROM sys.external_models;

Create an EXTERNAL MODEL with Ollama, default parameters, and an explicit owner

This example creates an EXTERNAL MODEL of the EMBEDDINGS type using Ollama hosted locally for development purposes. The example also uses PARAMETERS to set the Dimensions parameter at the endpoint to 725.

CREATE EXTERNAL MODEL MyOllamaModel
AUTHORIZATION AI_User
WITH (
      LOCATION = 'https://localhost:11435/api/embed',
      API_FORMAT = 'Ollama',
      MODEL_TYPE = EMBEDDINGS,
      MODEL = 'all-minilm',
      PARAMETERS = '{"Dimensions":"725"}'
);

Create an EXTERNAL MODEL with OpenAI

This example creates an EXTERNAL MODEL of the EMBEDDINGS type using the OpenAI API_FORMAT and HTTP header based credentials for authentication.

-- Create access credentials
CREATE DATABASE SCOPED CREDENTIAL [https://openai.com]
WITH IDENTITY = 'HTTPEndpointHeaders', secret = '{"Bearer":"YOUR_OPENAI_KEY"}';
GO

-- Create the external model
CREATE EXTERNAL MODEL MyAzureOpenAiModel
AUTHORIZATION CRM_User
WITH (
      LOCATION = 'https://api.openai.com/v1/embeddings',
      API_FORMAT = 'OpenAI',
      MODEL_TYPE = EMBEDDINGS,
      MODEL = 'text-embedding-ada-002',
      CREDENTIAL = [https://openai.com]
);

Related content