Performance issue for Managed identity for SQL authentication via azure function App

Question

Below code works fine but cause performance issue as for every request it took avg 3 seconds more. What could be another approach for this?

public QuoteContext(DbContextOptions options) : base(options)
{
    var conn = (Microsoft.Data.SqlClient.SqlConnection)Database.GetDbConnection();
    var credential = new DefaultAzureCredential();
    var token = credential
            .GetToken(new Azure.Core.TokenRequestContext(
                new[] { "https://database.windows.net/.default" }));
    conn.AccessToken = token.Token;
}

THe issue here is that `DefaultAzureCredential` doesn't cache the token so it is doing a call to AAD for each new connection. You probably wanna implement some caching around that. — Thomas, Oct 03 '21 at 22:46
Thanks .I tried it through storing generated token in static variable then checking validity of token if valid then pick from static variable else regenerate. But for every hour it has to again generate the token Is there any way we can make client static / singleton. — Santosh Jindal, Oct 04 '21 at 03:36
Token are only valid for an hour by default so every hour you'll have to get a new token. — Thomas, Oct 04 '21 at 05:13
which version of entity framework ar you using ? I think the appropriate approach with EF core is to use interceptor — Thomas, Oct 04 '21 at 05:15
you can check this answer: https://stackoverflow.com/a/63820411/4167200. It is using `AzureServiceTokenProvider` which is deprecated now but the logic is the same — Thomas, Oct 04 '21 at 08:11

score 0 · Answer 1 · answered Oct 13 '21 at 12:14

Thank you Thomas & J Weezy Posting your suggestion as an answer to help other community members.

"Note: you need to migrate the secret into the KeyVault. In this case, we named it AzureSqlSecret. This is in order to retrieve the database user's credentials.

The Entities class constructor that calls the AzureAuthenticationInterceptor is as follows:

public ProjectNameEntities() :
    base(new DbContextOptionsBuilder<ProjectNameEntities>()
        .UseSqlServer(ConfigurationManager.ConnectionStrings["ProjectNameEntities"].ConnectionString)
        .AddInterceptors(new AzureAuthenticationInterceptor())
        .Options)
{ }

AzureAuthenticationInterceptor:

#region NameSpaces
using Azure.Core;
using Azure.Identity;
using Azure.Security.KeyVault.Secrets;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore.Diagnostics;
using Microsoft.IdentityModel.Clients.ActiveDirectory;
using System;
using System.Configuration;
using System.Data.Common;
using System.Threading;
using System.Threading.Tasks;
#endregion

namespace <ProjectName>.DataAccess.Helpers
{
    public class AzureAuthenticationInterceptor : DbConnectionInterceptor
    {
        #region Constructor
        public AzureAuthenticationInterceptor()
        {
            SecretClientOptions objSecretClientOptions;
            string strAzureKeyVaultResourceIdentifier;
            string strAzureKeyVault;
            string strAzureKeyVaultUri;

            strAzureKeyVaultResourceIdentifier = ConfigurationManager.AppSettings["Azure:ResourceIdentifiers:KeyVault"];
            strAzureKeyVault = ConfigurationManager.AppSettings["Azure:KeyVaults:TaxPaymentSystem"];
            strAzureKeyVaultUri = strAzureKeyVaultResourceIdentifier.Replace("{0}", strAzureKeyVault);

            // Set the options on the SecretClient. These are default values that are recommended by Microsoft.
            objSecretClientOptions = new SecretClientOptions()
            {
                Retry =
                {
                    Delay= TimeSpan.FromSeconds(2),
                    MaxDelay = TimeSpan.FromSeconds(16),
                    MaxRetries = 5,
                    Mode = RetryMode.Exponential
                }
            };

            this.SecretClient = new SecretClient(
                vaultUri: new Uri(strAzureKeyVaultUri),
                credential: new DefaultAzureCredential(), 
                objSecretClientOptions
                );

            this.KeyVaultSecret = this.SecretClient.GetSecret("AzureSqlSecret");
            this.strKeyVaultSecret = this.KeyVaultSecret.Value;

            this.strAzureResourceIdentifierAuthentication = ConfigurationManager.AppSettings["Azure:ResourceIdentifiers:Authentication"];
            this.strAzureResourceIdentifierDatabase = ConfigurationManager.AppSettings["Azure:ResourceIdentifiers:DataBase"];
            this.strClientId = ConfigurationManager.AppSettings["Azure:DatabaseUsername:ClientId"];
            this.strTenantId = ConfigurationManager.AppSettings["Azure:TenantId"];                
        }
        #endregion

        #region Methods
        public override async ValueTask<InterceptionResult> ConnectionOpeningAsync(
            DbConnection objDbConnection,
            ConnectionEventData objEventData,
            InterceptionResult objReturn,
            CancellationToken objCancellationToken = default)
        {
            _ILogger.Debug("Reached the Async Interceptor method");

            if (objDbConnection is SqlConnection objSqlConnection)
            {
                objSqlConnection.AccessToken = GetAccessToken();
            }

            return objReturn;
        }

        public override InterceptionResult ConnectionOpening(
            DbConnection objDbConnection,
            ConnectionEventData objConnectionEventData,
            InterceptionResult objReturn)
        {
            _ILogger.Debug("Reached the non-Async Interceptor method");

            if (objDbConnection is SqlConnection objSqlConnection)
            {
                objSqlConnection.AccessToken = GetAccessToken();
            }

            return objReturn;
        }

        private string GetAccessToken()
        {
            AuthenticationContext objAuthenticationContext;
            AuthenticationResult objAuthenticationResult;
            ClientCredential objClientCredential;

            objAuthenticationContext = new AuthenticationContext(string.Format("{0}/{1}"
                                                                                , this.strAzureResourceIdentifierAuthentication
                                                                                , this.strTenantId));
            objClientCredential = new ClientCredential(this.strClientId, this.strKeyVaultSecret);
            objAuthenticationResult = objAuthenticationContext.AcquireTokenAsync(this.strAzureResourceIdentifierDatabase, objClientCredential).Result;
            return objAuthenticationResult.AccessToken;
        }
        #endregion

        #region Properties
        readonly <ProjectName>.Common.Logging.ILogger _ILogger = <ProjectName>.Common.Logging.LogWrapper.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
        private SecretClient SecretClient;
        private KeyVaultSecret KeyVaultSecret;
        private string strAzureResourceIdentifierDatabase;
        private string strAzureResourceIdentifierAuthentication;
        private string strKeyVaultSecret;
        private string strClientId;
        private string strTenantId;
        #endregion
    }
}

Performance issue for Managed identity for SQL authentication via azure function App

1 Answers1