Security is a significant topic today, and the ability to access a service requiring authentication without using an API key, password, or secret is a common request from those concerned about the security of a solution, which includes all of us.

In today’s digital landscape, cybersecurity threats are increasingly sophisticated and frequent, making it imperative to protect sensitive information. Traditional methods of authentication, such as passwords and API keys, are often susceptible to breaches and misuse. As a result, there is growing interest in alternative, more secure authentication methods.

Azure SQL DB, as you have learned in the past months, is well integrated with Azure OpenAI, and thanks to such integration calling a model exposed by Azure OpenAI without the need to resort to an API-key is very easy.

Assign a Managed Identity to your Azure SQL

To use a Managed Identity from Azure SQL, it must be assigned to the Azure SQL logical server: “A System Managed Identity (SMI) is automatically assigned to Azure SQL Managed Instance when it’s created. When you’re using Microsoft Entra authentication with Azure SQL Database, you must assign an SMI when Azure service principals are used to create Microsoft Entra users in SQL Database.”

The SMI has the same name as the service. If your database server is named mydbserver the assigned SMI will also be named mydbserver.

You can also assign as User Managed Identity (UMI), as explained here: as explained in Managed identities in Microsoft Entra for Azure SQL. To better understand the difference between System and User assigned managed identities, take a look at the “Managed identity types” article.

Create a Scoped Credential pointing to the Managed Identity

Once the Managed Identity is set, a Scoped Database Credential must be created so that it can be used to connect to a specific URL. As explained in sp_invoke_external_rest_endpoint documentation, the secret must be set to the correct resource for OAuth2 authentication which, for Azure OpenAI, is cognitiveservices.azure.com (as per documentation):

create database scoped credential [https://<my-azure-openai-endpoint>.openai.azure.com]
with identity = 'Managed Identity', secret = '{"resourceid":"https://cognitiveservices.azure.com"}';
go

Give the Managed Identity access to Azure OpenAI

The Managed Identity must possess appropriate permission to access the Azure OpenAI service. It is necessary to assign the “Cognitive Services OpenAI User” role to the Managed Identity to ensure it has the required permissions.

Call Azure OpenAI service from Azure SQL

Now it is possible to use sp_invoke_external_rest_endpoint in the usual way. Behind the scenes Azure SQL and Entra ID will do  the “authentication dance” automatically so that you don’t have to worry about using API key anymore:

declare @response nvarchar(max)
declare @payload nvarchar(max) = json_object('input': 'hello world');

exec sp_invoke_external_rest_endpoint
    @url = 'https://<my-azure-openai-endpoint>.openai.azure.com/openai/deployments/<model-deployment-name>/embeddings?api-version=2024-08-01-preview',
    @method = 'POST',
    @credential = [https://<my-azure-openai-endpoint>.openai.azure.com],
    @payload = @payload,
    @response = @response output;

Conclusion

Adopting a passwordless approach enhances security while simplifying the process, as it eliminates the need to manage security keys and rotate them periodically. This makes the overall solution easier to maintain and more secure.