{"id":4277,"date":"2025-02-18T11:07:29","date_gmt":"2025-02-18T19:07:29","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=4277"},"modified":"2025-02-20T09:47:29","modified_gmt":"2025-02-20T17:47:29","slug":"go-passwordless-when-calling-azure-openai-from-azure-sql-using-managed-identities","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/go-passwordless-when-calling-azure-openai-from-azure-sql-using-managed-identities\/","title":{"rendered":"Go passwordless when calling Azure OpenAI from Azure SQL using Managed Identities"},"content":{"rendered":"

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.<\/p>\n

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.<\/p>\n

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.<\/p>\n

Assign a Managed Identity to your Azure SQL<\/h2>\n

To use a Managed Identity from Azure SQL, it must be assigned to the Azure SQL logical server: \u201cA 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.\u201d<\/p>\n

The SMI has the same name as the service. If your database server is named mydbserver<\/code> the assigned SMI will also be named mydbserver<\/code>.<\/p>\n

\"Image<\/a><\/p>\n

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

Create a Scoped Credential pointing to the Managed Identity<\/h2>\n

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

create database scoped credential [https:\/\/<my-azure-openai-endpoint>.openai.azure.com]\r\nwith identity = 'Managed Identity', secret = '{\"resourceid\":\"https:\/\/cognitiveservices.azure.com\"}';\r\ngo<\/code><\/pre>\n
Give the Managed Identity access to Azure OpenAI<\/h2>\n
The Managed Identity must possess appropriate permission to access the Azure OpenAI service. It is necessary to assign the “Cognitive Services OpenAI User<\/a>” role to the Managed Identity to ensure it has the required permissions.<\/p>\n
Call Azure OpenAI service from Azure SQL<\/h2>\n
Now it is possible to use sp_invoke_external_rest_endpoint<\/code> in the usual way. Behind the scenes Azure SQL and Entra ID will do\u00a0 the \u201cauthentication dance\u201d automatically so that you don\u2019t have to worry about using API key anymore:<\/p>\n
declare @response nvarchar(max)\r\ndeclare @payload nvarchar(max) = json_object('input': 'hello world');\r\n\r\nexec sp_invoke_external_rest_endpoint\r\n\u00a0\u00a0\u00a0 @url = 'https:\/\/<my-azure-openai-endpoint>.openai.azure.com\/openai\/deployments\/<model-deployment-name>\/embeddings?api-version=2024-08-01-preview',\r\n\u00a0\u00a0\u00a0 @method = 'POST',\r\n\u00a0\u00a0\u00a0 @credential = [https:\/\/<my-azure-openai-endpoint>.openai.azure.com],\r\n\u00a0\u00a0\u00a0 @payload = @payload,\r\n\u00a0\u00a0\u00a0 @response = @response output;<\/code><\/pre>\n
Conclusion<\/h2>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":24720,"featured_media":4283,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[601,1,648],"tags":[649],"class_list":["post-4277","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-ai","category-azure-sql","category-security","tag-passwordless"],"acf":[],"blog_post_summary":"
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 […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/4277","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/users\/24720"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/comments?post=4277"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/4277\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media\/4283"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media?parent=4277"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/categories?post=4277"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/tags?post=4277"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}