Unable to load assembly on Azure Sql that uses Azure.Identity library

Question

Hi Team,

We need to access the azure key vault from azure sql (managed instance) to retrieve the secret. We have developed an udf that will fetch the secret from azure key vault. Below is the code snippet:

using Microsoft.SqlServer.Server; 
using Azure.Identity;
using Azure.Security.KeyVault.Secrets;
 
public static string GetApiKeyFromVault(string secretName,
                                        string kvUri,
                                        bool useInteractive)
    {
        var secretVault = "";
        var client = new SecretClient(new Uri(kvUri), new DefaultAzureCredential(includeInteractiveCredentials:useInteractive));
        var secret = client.GetSecret(secretName);
        secretVault = secret.Value.Value;
        return secretVault;
    }

While deploying the assembly in azure sql. I am getting below error:

11:13:27 AMStarted executing query at Line 5 Msg 6503, Level 16, State 12, Line 1 Assembly 'azure.identity, version=1.12.0.0, culture=neutral, publickeytoken=92742159e12e44c8.' was not found in the SQL catalog. Total execution time: 00:00:01.361

I am seeking valuable advice on resolving the aforementioned error and any potential workarounds. Your insights would be greatly appreciated. Thanks

Answer

The error message indicates that the assembly azure.identity is not found in the SQL catalog, which means Azure SQL Managed Instance doesn't have access to the specified assembly for your User-Defined Function (UDF). Here are some steps and potential workarounds to resolve this issue:

Check Supported Assemblies: Ensure that the azure.identity assembly and any other required Azure SDK assemblies are supported and available in Azure SQL Managed Instance. Not all .NET assemblies are supported in SQL Managed Instance.

Use Built-in Features: Consider using built-in Azure SQL Managed Instance features for accessing Azure Key Vault. You can use Managed Identity for authentication, and then call the Azure Key Vault from your application or stored procedures rather than directly from a UDF.

Deploy Assemblies: If you have access to a different environment (like a local SQL Server), you can try deploying the assembly there to test if it works. If it does, you might consider rewriting your logic to avoid using unsupported assemblies.

Service Principal Authentication: If Managed Identity is not an option, consider using a service principal with client secret or certificate for authentication to the Key Vault. You may need to implement this logic in your application layer instead of within SQL.

Move Logic to Application Layer: Since UDFs in SQL are quite limited, it might be beneficial to handle the Key Vault interactions in your application layer (e.g., C#, .NET application) and pass the secrets to your SQL operations as parameters.

Azure Functions or Logic Apps: As an alternative, consider using Azure Functions or Logic Apps to fetch the secrets and then call these from your SQL Managed Instance.

By implementing one of these strategies, you should be able to work around the limitation and securely access your secrets from Azure Key Vault. If you have more details on your architecture, I might be able to provide more specific suggestions!

Share via

Unable to load assembly on Azure Sql that uses Azure.Identity library

1 answer

Your answer