With the introducing of sp_invoke_external_rest_endpoint in Azure SQL DB, it’s possible to directly calling the Azure OpenAI service within the database. Azure OpenAI is in the safe-listed Azure services. For other API service (like common OpenAI) that’s not in the safe-list, you will need to create a wrapper API in the Azure API Management Instance.
To invoke external rest endpoint in Azure SQL DB, a few setup steps are required:
-
Create database master key, if not already.
IF NOT EXISTS(SELECT * FROM sys.symmetric_keys WHERE [name] = '##MS_DatabaseMasterKey##') CREATE MASTER KEY; -
Create database scoped credential for the API service
CREATE DATABASE SCOPED CREDENTIAL [<your endpoint>] WITH IDENTITY = 'HTTPEndpointHeaders' ,SECRET = '{"api-key":"<your api key>"}';If you need to reference the above credentials in many places, you can reate a user-friendly named function that other SP/function can use to minimise change impact if credential needs to be changed.
CREATE OR ALTER FUNCTION dbo.TheAPICredentials() RETURNS SYSNAME AS BEGIN RETURN N'<your endpoint>' --this is the db scoped credential created above END;You can do the same with the API URL.
CREATE OR ALTER FUNCTION dbo.TheAPIURL() RETURN NVARCHAR(255) AS BEGIN RETURN N'your api url'; END;
An example of Azure SQL DB directly calling ChatGPT can be found here:
Query your Data in Azure SQL using Natural Language and ChatGPT