Lesson Learned #457:Optimizing SQL Connection Management with Single Token Authentication

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.

This week, I addressed a service request highlighting a key issue in client applications with varying workloads. We noted that the volume of Azure Managed Identity requests, which fluctuates based on the client's workload and the overall rate of requests received per second, can escalate significantly. This surge in requests often leads to a delay, making the application wait a few seconds before it can successfully request a new token. To mitigate this challenge, we have developed a basic example that proactively manages token renewal, synchronizing it with the token's expiration time.

In the realm of modern applications interfacing with SQL databases, the efficient management of database connections is of paramount importance. A frequent obstacle encountered is the inefficiency and resource-intensiveness of authenticating each connection individually. This article introduces a streamlined solution employing C# and Microsoft.Data.SqlClient, designed to secure a singular token for all connections. This approach substantially reduces authentication overhead, thereby enhancing overall application performance.

The Problem

Traditional connection management strategies often involve requesting a new authentication token for each connection to the database. This approach, while straightforward, can lead to increased latency and resource consumption, especially in high-throughput scenarios.

The Solution

The provided C# script showcases an efficient way to manage SQL connections by obtaining a single token that is reused for all connections. This method is particularly useful in environments like Azure SQL Database where authentication tokens are used instead of traditional connection strings.

Implementation Overview

The core of this solution is the ProvideToken and GetToken methods. ProvideToken obtains an access token from Azure using ManagedIdentityCredential, which is then used by all SQL connections. GetToken ensures that the token is refreshed only when necessary, specifically when it's either not obtained yet or close to expiration.

Code Walkthrough

Initialization: The script initializes variables for the access token, its expiration time, and the connection string.
Main Loop: In the Main method, the loop continuously fetches the token, connects to the database, executes a query, and then closes the connection.
ConnectToDatabase: This method creates a SqlConnection using the shared token. It includes retry logic for robustness.
ExecuteQuery: A simple query execution is demonstrated, with retry logic similar to the connection method.
Logging and Timing: The script includes methods for logging and measuring the execution time, which are essential for monitoring and debugging.

Benefits

Reduced Authentication Overhead: By reusing a token, the need to authenticate each connection separately is eliminated, reducing the time and resources spent on connection setup.
Scalability: This approach scales better in high-demand scenarios where numerous connections are made to the database.
Simplified Code: Centralizing token management simplifies the codebase, making it easier to maintain and understand.

Conclusion

Implementing a single token authentication for SQL connections in C# applications can significantly improve performance and scalability. The provided script is a starting point that can be integrated and adapted to various application needs.

Example code

using System; using Microsoft.Data.SqlClient; using System.Threading; using System.Diagnostics; using Azure.Identity; namespace DockerHealth { class Program { private static string _accessToken; private static DateTimeOffset _accessTokenExpiration; private static string _connectionString = "Server=tcp:servername.database.windows.net,1433;Initial Catalog=DBName;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Pooling=true;Max Pool size=100;Min Pool Size=0;ConnectRetryCount=3;ConnectRetryInterval=10;Connection Lifetime=0;Application Name=ConnTest Check"; static void Main(string[] args) { SqlConnection oConnection = new SqlConnection(); Stopwatch stopWatch = new Stopwatch(); while (true) { GetToken(); stopWatch.Start(); oConnection =ConnectToDatabase(); LogExecutionTime(stopWatch, "Connected - " + oConnection.ClientConnectionId.ToString() ); stopWatch.Start(); ExecuteQuery(oConnection); LogExecutionTime(stopWatch, "Executed"); oConnection.Close(); Log("Waiting for...."); Thread.Sleep(1000); } } static SqlConnection ConnectToDatabase() { SqlConnection Connection = new SqlConnection(_connectionString); Connection.AccessToken = _accessToken; int retries = 0; while (true) { try { Connection.Open(); return Connection; } catch (Exception ex) { retries++; if (retries >= 5) { Log($"Maximum number of retries reached. Error: " + ex.Message); break; } Log($"Error connecting to the database. Retrying in " + retries + " seconds... Error: " + ex.Message); Thread.Sleep(retries * 1000); } } return Connection; } static void Log(string message="") { try { Console.WriteLine($"{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")}: {message}"); } catch { } } static void ExecuteQuery(SqlConnection connection) { int retries = 0; while (true) { try { using (SqlCommand command = new SqlCommand("SELECT 1", connection)) { command.CommandTimeout = 5; object result = command.ExecuteScalar(); } break; } catch (Exception ex) { retries++; if (retries >= 5) { Log($"Maximum number of retries reached. Error: " + ex.Message); break; } Log($"Error executing the query. Retrying in " + retries + " seconds...Error:" + ex.Message); Thread.Sleep(retries * 1000); } } } static void LogExecutionTime(Stopwatch stopWatch, string action) { try { stopWatch.Stop(); TimeSpan ts = stopWatch.Elapsed; Log($"{action} - {String.Format("{0:00}:{1:00}:{2:00}.{3:000}", ts.Hours, ts.Minutes, ts.Seconds, ts.Milliseconds)}"); stopWatch.Reset(); } catch { } } static (string, DateTimeOffset) ProvideToken() { try { var clientId = "c242c7df-XXX-XXXX-XXXXX"; var resource = "https://database.windows.net/"; var managedIdentityCredential = new ManagedIdentityCredential(clientId); var accessToken = managedIdentityCredential.GetTokenAsync(new Azure.Core.TokenRequestContext(new[] { resource })); return (accessToken.Result.Token, accessToken.Result.ExpiresOn.UtcDateTime); } catch (Exception ex) { Log($"Was not possible to obtain the token. Error: " + ex.Message); } return ("", DateTimeOffset.UtcNow); } static void GetToken() { if (string.IsNullOrEmpty(_accessToken) || DateTimeOffset.UtcNow.AddMinutes(5) >= _accessTokenExpiration) { Stopwatch stopWatch = new Stopwatch(); var result = ProvideToken(); _accessToken = result.Item1; _accessTokenExpiration = result.Item2; LogExecutionTime(stopWatch, "Token Obtained"); Log($"Access Token Expiration:{_accessTokenExpiration}"); } else { Log($"Same Token"); } } } }

Enjoy!