In the realm of Azure integration, robust security is paramount. When it comes to accessing your Azure SQL Server, opting for Azure Identity or token-based authentication is the obvious choice over basic forms of authentication. This approach significantly enhances security and streamlines access management, offering numerous advantages over traditional methods.
For more information on Azure Managed Identity, please read this article Azure Managed Identities Introduction.
Obtaining a Token
The first step in connecting to SQL Server is to obtain a token.
The following code will use (in order) a Managed Identity Credential (for use on Azure Resources), and if unavailable fall back to an Azure Cli Credential (for use on devices with Azure Cli installed) to obtain a token for the scope of Azure SQL Servers https://database.windows.net/.default
var tokenCredential = new ChainedTokenCredential(new ManagedIdentityCredential(), new AzureCliCredential());
var tokenRequestContext = new TokenRequestContext(["https://database.windows.net/.default"]);
var token = await _credential.GetTokenAsync(tokenRequestContext, CancellationToken.None);
The token variable above is an Azure.Core.AccessToken more info here, which contains two properties of note:
- ExpiresOn : a
DateTimeOffsetthat represents when the token is going to expire - Token : a
stringwith the JSON Web Token (JWT), this is the actual token
Checking the ExpiresOn is useful, a common issue that occurs when attempting to use an expired token. The code below is an example of the core of a Singleton that will allow a token to shared between connections and refreshed when its validity is less than 5 minutes.
private static AccessToken _token;
private static SemaphoreSlim _semaphoreToken = new SemaphoreSlim(1, 1);
private static TokenRequestContext _tokenRequestContext = new TokenRequestContext(["https://database.windows.net/.default"]);
private TokenCredential _tokenCredential = new ChainedTokenCredential(new ManagedIdentityCredential(), new AzureCliCredential());
public async Task<string> GetAccessTokenAsync()
{
await _semaphoreToken.WaitAsync();
try
{
//If the Token has more than 5 minutes Validity
if (DateTime.UtcNow.AddMinutes(5) <= _token.ExpiresOn.UtcDateTime) return _token.Token;
var tokenRequestContext = new TokenRequestContext(_azureSqlScopes);
var token = await _tokenCredential.GetTokenAsync(_tokenRequestContext, CancellationToken.None);
_token = token;
return token.Token;
}
finally
{
_semaphoreToken.Release();
}
}
Granting access in Azure SQL
When using Azure SQL with Azure Identities Entra ID (previously Azure AD) must be enabled on the server
Note: There is the option to only allow Entra ID as the method of authentication for this database (This will disable SQL Authentication)
Once Entra Id is enabled on the SQL Server a Database User must be created and permissions assigned.
The code below will create a user for the EntraId account with the User Principal Name (UPN) of Paul.Reardon@AwesomeCompany.com and assign them to the DB Owner role.
CREATE USER [Paul.Reardon@AwesomeCompany.com] FROM EXTERNAL PROVIDER;
go
ALTER ROLE [db_owner]
ADD MEMBER [Paul.Reardon@AwesomeCompany.com]
Note:
Users must exist in Entra ID before running this or it will fail.
DB Owner is a high level of permissions, always consider the least privilege principals when assigning permissions.
Connecting with SQL Client
The most straight forward way to connect to SQL Server in c# is to use a SQL Client
The following code will use an implementation of the code above to get an access token and add it to the connection, as the code above will only refresh the token if there is less than 5 minutes life on it this will mean that excess latency is avoided as token requests are required infrequently.
var token = await GetAccessTokenAsync(); // using an implimentation of the class above
var connection = new SqlConnection("Server=myAwesomeServer.database.windows.net;Database=AwesomeDb;");
connection.AccessToken = token;
// Use the connection to run a query
var command = connection.CreateCommand();
command.CommandText = "Select * from Products";
var reader = command.ExecuteReader();
...
Connecting with Entity Framework Core
Entity Framework (EF) Core is Microsoft’s Object-Relational Mapper (ORM) framework for more information Click Here. When using EF the SQL Connection is abstracted away as EF supports many data sources, as such one of the best ways to ensure that the token is valid and present in the connection is to use an interceptor. EF Interceptors give the ability to interfere with low level database operations as well as high level EF operations, for more information of EF Interceptors Click Here
An example of an Interceptor is a follows:
public class AzureIdentityAuthenticationDbConnectionInterceptor : DbConnectionInterceptor
{
private static readonly string[] _azureSqlScopes = new[] { "https://database.windows.net/.default" };
private const int _cacheLifeTime = 5;
private readonly TokenCredential _credential = new ChainedTokenCredential(
new ManagedIdentityCredential(),
new AzureCliCredential());
private static AccessToken _token;
private static SemaphoreSlim _semaphoreToken = new SemaphoreSlim(1, 1);
public override InterceptionResult ConnectionOpening(
DbConnection connection,
ConnectionEventData eventData,
InterceptionResult result)
{
var sqlConnection = (SqlConnection)connection;
if (IsAzureSqlConnection(sqlConnection)) sqlConnection.AccessToken = GetAccessToken();
return base.ConnectionOpening(connection, eventData, result);
}
public override async ValueTask<InterceptionResult> ConnectionOpeningAsync(
DbConnection connection,
ConnectionEventData eventData,
InterceptionResult result,
CancellationToken cancellationToken = default)
{
var sqlConnection = (SqlConnection)connection;
if (IsAzureSqlConnection(sqlConnection)) sqlConnection.AccessToken = await GetAccessTokenAsync();
return await base.ConnectionOpeningAsync(connection, eventData, result, cancellationToken);
}
private static bool IsAzureSqlConnection(SqlConnection connection)
{
//
// Only try to get a token from AAD if
// - We connect to an Azure SQL instance; and
// - The connection doesn't specify a username.
//
var connectionStringBuilder = new SqlConnectionStringBuilder(connection.ConnectionString);
return connectionStringBuilder.DataSource.Contains("database.windows.net", StringComparison.OrdinalIgnoreCase) && string.IsNullOrEmpty(connectionStringBuilder.UserID);
}
private string GetAccessToken()
{
_semaphoreToken.Wait();
try
{
//If the Token has more than 5 minutes Validity
if (DateTime.UtcNow.AddMinutes(_cacheLifeTime) <= _token.ExpiresOn.UtcDateTime) return _token.Token;
var tokenRequestContext = new TokenRequestContext(_azureSqlScopes);
var token = _credential.GetToken(tokenRequestContext, CancellationToken.None);
_token = token;
return token.Token;
}
finally
{
_semaphoreToken.Release();
}
}
private async Task<string> GetAccessTokenAsync()
{
await _semaphoreToken.WaitAsync();
try
{
//If the Token has more than 5 minutes Validity
if (DateTime.UtcNow.AddMinutes(_cacheLifeTime) <= _token.ExpiresOn.UtcDateTime) return _token.Token;
var tokenRequestContext = new TokenRequestContext(_azureSqlScopes);
var token = await _credential.GetTokenAsync(tokenRequestContext, CancellationToken.None);
_token = token;
return token.Token;
}
finally
{
_semaphoreToken.Release();
}
}
}
To register the interceptor modify add the interceptor to the Dependency Injection Setup
builder.Services.AddDbContextPool<AwesomeDbContext>(o =>
{
o.UseSqlServer(connectionString);
o.AddInterceptors(new AzureIdentityAuthenticationDbConnectionInterceptor());
});
Once configured every time that EF attempts to open a database connection the interception will ensure that a valid token is present.