Chromex.Authentication.SqlServer 1.0.2

Chromex.Authentication.SqlServer

Entity Framework Core interceptor for automatic Azure CLI credential injection into SQL Server connections. Part of the Chromex.Authentication ecosystem.

Overview

Chromex.Authentication.SqlServer provides seamless integration between Chromex.Authentication and Entity Framework Core, automatically injecting Azure CLI credentials into every SQL Server database connection.

Problem This Package Solves

The VDI SQL Authentication Issue

When using SqlAuthenticationMethod.ActiveDirectoryDefault in VDI environments:

DefaultAzureCredential tries credentials in this order:
  1. Environment variables
  2. Workload identity
  3. Visual Studio cached credentials
  4. Visual Studio Code cached credentials
  5. Azure CLI credentials ← Your account (has DB permission)
  6. **MANAGED IDENTITY** ← VDI's identity (NO DB permission)
  7. Interactive browser login

Problem: VDI managed identity lacks permissions to development databases, causing connection failures before Azure CLI credentials are ever tried.

Solution: This package intercepts database connections and injects Azure CLI tokens directly, bypassing the entire chain and using only your Azure AD account credentials.

Installation

# Make sure the main package is installed first
dotnet add package Chromex.Authentication

# Then add the SQL Server package
dotnet add package Chromex.Authentication.SqlServer

⚠️ CRITICAL: Environment Configuration Required

This package ONLY works in the AzureDev environment.

You MUST set ASPNETCORE_ENVIRONMENT=AzureDev in your Properties/launchSettings.json:

{
  "profiles": {
    "http": {
      "commandName": "Project",
      "environmentVariables": {
        "ASPNETCORE_ENVIRONMENT": "AzureDev"
      },
      "applicationUrl": "http://localhost:5053"
    },
    "https": {
      "commandName": "Project",
      "environmentVariables": {
        "ASPNETCORE_ENVIRONMENT": "AzureDev"
      },
      "applicationUrl": "https://localhost:7020;http://localhost:5053"
    }
  }
}

Without this, database connections will fail with authentication errors.

See QUICKSTART_GUIDE.md for detailed environment setup.

Quick Start

See QUICKSTART_GUIDE.md for 5-minute setup with examples.

How It Works

Interceptor Architecture

┌────────────────────────────────────────────────┐
│  Entity Framework DbContext.Database.Open()   │
├────────────────────────────────────────────────┤
│         ↓                                       │
│  SqlServerAuthInterceptor.ConnectionOpening() │
│         ↓                                       │
│  Request token from IAzureDevAuthenticationService
│         ↓                                       │
│  Set SqlConnection.AccessToken                │
│         ↓                                       │
│  Connection opens with Azure CLI credentials  │
└────────────────────────────────────────────────┘

What Gets Bypassed

❌ Skipped:
   - DefaultAzureCredential chain
   - Environment variable lookup
   - Workload identity checks
   - VDI managed identity (the problematic step)
   - Interactive authentication prompts

✅ Used:
   - Azure CLI credentials only ('az login')
   - Token caching from Chromex.Authentication
   - Automatic token refresh before expiration

Registration

Step 1: Register Main AzureDev Authentication Package

using Chromex.Authentication.Extensions;

var builder = WebApplication.CreateBuilder(args);

if (builder.Environment.IsEnvironment("AzureDev"))
{
    builder.Services.AddAzureDevAuthentication(builder.Configuration, builder.Environment);
}

// Add your DbContext AFTER registering authentication
builder.Services.AddDbContext<MyAppDbContext>(options =>
    options.UseSqlServer("Server=myserver.database.windows.net;Database=mydb;Encrypt=true;TrustServerCertificate=false;")
);

Step 2: Register SQL Server Authentication Interceptor

using Chromex.Authentication.SqlServer.Extensions;

if (builder.Environment.IsEnvironment("AzureDev"))
{
    builder.Services.AddAzureDevAuthentication(builder.Configuration, builder.Environment);
    
    builder.Services.AddDbContext<MyAppDbContext>(options =>
        options.UseSqlServer("Server=myserver.database.windows.net;Database=mydb;Encrypt=true;TrustServerCertificate=false;")
    );
    
    // Register the SQL Server authentication interceptor
    builder.Services.AddAzureDevSqlServerAuthentication();
}

var app = builder.Build();

// Initialize authentication during startup
if (builder.Environment.IsEnvironment("AzureDev"))
{
    await app.Services.InitializeAzureDevAuthenticationAsync();
}

app.Run();

Step 3: Configure DbContext

Update your DbContext to use the interceptor:

using Chromex.Authentication.SqlServer.Infrastructure.Interceptors;
using Microsoft.EntityFrameworkCore;

public class MyAppDbContext : DbContext
{
    private readonly IServiceProvider _serviceProvider;

    public MyAppDbContext(DbContextOptions<MyAppDbContext> options, IServiceProvider serviceProvider) 
        : base(options)
    {
        _serviceProvider = serviceProvider;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        base.OnConfiguring(optionsBuilder);

        // Inject the SQL Server authentication interceptor if available
        var interceptor = _serviceProvider.GetService<SqlServerAuthInterceptor>();
        if (interceptor != null)
        {
            optionsBuilder.AddInterceptors(interceptor);
        }
    }

    public DbSet<User> Users { get; set; }
    // ... other DbSets ...
}

Using with Your Application

Once configured, no changes needed to your application code:

public class UserService
{
    private readonly MyAppDbContext _dbContext;

    public UserService(MyAppDbContext dbContext)
    {
        _dbContext = dbContext;
    }

    // This automatically uses Azure CLI credentials
    public async Task<List<User>> GetAllUsersAsync()
    {
        return await _dbContext.Users.ToListAsync();
    }

    // All EF Core operations work transparently
    public async Task CreateUserAsync(User user)
    {
        _dbContext.Users.Add(user);
        await _dbContext.SaveChangesAsync();
    }
}

Connection String Format

For Azure SQL Database

Server=myserver.database.windows.net;Database=mydb;Encrypt=true;TrustServerCertificate=false;

Important: Do NOT include User Id or Password in the connection string when using this interceptor. The token handles authentication.

For SQL Server on Azure VMs

Server=tcp:myvm.eastus.cloudapp.azure.com,1433;Database=mydb;Encrypt=true;TrustServerCertificate=false;

Configuration

Store connection strings in configuration, not code:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=myserver.database.windows.net;Database=mydb;Encrypt=true;TrustServerCertificate=false;"
  }
}

Then in Program.cs:

var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");
builder.Services.AddDbContext<MyAppDbContext>(options =>
    options.UseSqlServer(connectionString)
);

Architecture Details

SqlServerAuthInterceptor

Handles both synchronous and asynchronous connection operations:

public class SqlServerAuthInterceptor : DbConnectionInterceptor
{
    private readonly IAzureDevAuthenticationService _authenticationService;
    private readonly ILogger<SqlServerAuthInterceptor> _logger;

    // Intercepts async connection opens
    public override async ValueTask<InterceptionResult> ConnectionOpeningAsync(
        DbConnection connection,
        ConnectionEventData eventData,
        InterceptionResult result,
        CancellationToken cancellationToken = default)
    {
        if (connection is SqlConnection sqlConnection)
        {
            var token = await _authenticationService.GetAccessTokenAsync();
            sqlConnection.AccessToken = token;
        }
        return result;
    }

    // Intercepts sync connection opens
    public override InterceptionResult ConnectionOpening(
        DbConnection connection,
        ConnectionEventData eventData,
        InterceptionResult result)
    {
        if (connection is SqlConnection sqlConnection)
        {
            var token = _authenticationService.GetAccessTokenAsync().Result;
            sqlConnection.AccessToken = token;
        }
        return result;
    }
}

Token Lifecycle

┌──────────────────────────────────────────────┐
│  First Database Query                        │
├──────────────────────────────────────────────┤
│ 1. SqlServerAuthInterceptor.ConnectionOpening
│ 2. Request token from cache/Azure            │
│ 3. Set SqlConnection.AccessToken             │
│ 4. Connection opens with token               │
│ 5. Query executes                            │
└──────────────────────────────────────────────┘
         ↓
┌──────────────────────────────────────────────┐
│  Subsequent Queries (same token)             │
├──────────────────────────────────────────────┤
│ 1. SqlServerAuthInterceptor.ConnectionOpening
│ 2. Request token from CACHE (fast)           │
│ 3. Set SqlConnection.AccessToken             │
│ 4. Connection opens instantly                │
│ 5. Query executes                            │
└──────────────────────────────────────────────┘
         ↓
┌──────────────────────────────────────────────┐
│  Token Near Expiration (< 5 min left)        │
├──────────────────────────────────────────────┤
│ 1. SqlServerAuthInterceptor.ConnectionOpening
│ 2. Request NEW token from Azure              │
│ 3. Update cache                              │
│ 4. Set SqlConnection.AccessToken             │
│ 5. Connection opens with new token           │
│ 6. Query executes                            │
└──────────────────────────────────────────────┘

Common Use Cases

1. Entity Framework Core Migrations

# Add migration
dotnet ef migrations add AddUsersTable

# Update database (uses interceptor automatically)
dotnet ef database update

# Script migration (no interceptor needed - manual authentication)
dotnet ef migrations script

2. Bulk Operations

public async Task ImportUsersAsync(List<User> users)
{
    _dbContext.Users.AddRange(users);
    // Interceptor automatically handles token for SaveChangesAsync
    await _dbContext.SaveChangesAsync();
}

3. Transactions

public async Task TransferDataAsync()
{
    using var transaction = await _dbContext.Database.BeginTransactionAsync();
    try
    {
        // All operations within transaction use interceptor
        _dbContext.Users.Add(newUser);
        _dbContext.Teams.Remove(oldTeam);
        await _dbContext.SaveChangesAsync();
        
        await transaction.CommitAsync();
    }
    catch
    {
        await transaction.RollbackAsync();
        throw;
    }
}

4. Raw SQL Queries

public async Task<List<dynamic>> ExecuteStoredProcAsync(string procName)
{
    var connection = _dbContext.Database.GetDbConnection();
    // Interceptor handles token injection
    await connection.OpenAsync();
    
    using var command = connection.CreateCommand();
    command.CommandText = $"EXEC {procName}";
    using var reader = await command.ExecuteReaderAsync();
    // Process results...
}

Error Handling

TokenAcquisitionException

Thrown if token cannot be acquired:

try
{
    var users = await _dbContext.Users.ToListAsync();
}
catch (InvalidOperationException ex) when (ex.Message.Contains("token"))
{
    logger.LogError("Failed to acquire database token. Ensure 'az login' is valid: {Message}", ex.Message);
    // Retry or handle gracefully
}

Connection Timeout

If token acquisition takes too long:

// Increase connection timeout in connection string
"Server=myserver.database.windows.net;Database=mydb;Connection Timeout=120;..."

Expired Token

Handled automatically - interceptor requests new token:

// No code needed - interceptor detects expiration and refreshes
var users = await _dbContext.Users.ToListAsync();

Logging

The interceptor logs all token operations:

[Debug] [SqlServerAuthInterceptor] Setting access token on SQL connection
[Debug] [SqlServerAuthInterceptor] Access token set successfully on SQL connection
[Error] [SqlServerAuthInterceptor] Failed to set access token on SQL connection

Configure Serilog in Program.cs:

var loggerConfig = new LoggerConfiguration()
    .MinimumLevel.Debug()
    .WriteTo.Console()
    .WriteTo.File("logs/myapp-.log", rollingInterval: RollingInterval.Day)
    .Enrich.FromLogContext()
    .CreateLogger();

Log.Logger = loggerConfig;
builder.Host.UseSerilog();

Troubleshooting

Error: "IAzureDevAuthenticationService is not registered"

InvalidOperationException: IAzureDevAuthenticationService is not registered.
Call services.AddAzureDevAuthentication() before AddAzureDevSqlServerAuthentication().

Solution: Register main authentication first:

// CORRECT ORDER:
services.AddAzureDevAuthentication(configuration, environment);  // First
services.AddAzureDevSqlServerAuthentication();                   // Then

Error: "Failed to set access token on SQL connection"

[Error] Failed to set access token on SQL connection
Reason: Azure CLI token invalid for this database

Solution: Your Azure AD account lacks database permissions:

# Ask Azure admin to grant you one of:
# - SQL Server Contributor
# - SQL DB Contributor
# - SQL Server Security Manager (with database-specific role)

Error: "Connection timeout"

SqlException: Timeout expired. The timeout period elapsed prior to...

Solution: Token acquisition might be slow. Increase timeout:

var connectionString = "Server=...;Connection Timeout=120;...";

Or check if az login has expired:

az account show
az login  # Re-login if needed

Connection String Errors

Wrong (with User Id/Password):

"Server=myserver.database.windows.net;User Id=user@domain;Password=xyz;..."

Correct (no credentials):

"Server=myserver.database.windows.net;Database=mydb;Encrypt=true;..."

Limitations

Only Works with SQL Server

This interceptor only handles SqlConnection. Other database types won't use it:

// Works - uses interceptor
options.UseSqlServer(connectionString);

// Doesn't work - no interceptor for other databases
options.UseNpgsql(connectionString);
options.UseMySql(connectionString);

Requires Chromex.Authentication

Must have main package registered first.

Token Validation

Token is acquired but not validated against the specific database. The database validates it independently.

Best Practices

✅ Do:

• Keep connection strings in configuration (appsettings.json)
• Use database-specific Azure AD roles (minimal permissions)
• Enable logging to diagnose token issues
• Test connections during development
• Use az account show to verify login status

❌ Don't:

• Store connection strings in code
• Include username/password in connection strings
• Use service principal credentials for development (use user account)
• Leave az login expired during testing
• Hard-code server names (use configuration)

Performance Considerations

Token Caching Impact

First query: ~50-100ms (token request) Subsequent queries: <1ms (cached token)

Connection Pooling

SQL Server connection pooling works normally. Tokens are cached separately:

Connection Pool:
  ├─ Connection 1 ─→ Reuse ─→ Token cached
  ├─ Connection 2 ─→ Reuse ─→ Token cached
  └─ Connection 3 ─→ Reuse ─→ Token cached

All use the same cached token until refresh needed.

Next Steps

1. Main Package: See Chromex.Authentication README for JWT and other features
2. Migrations: Use EF Core migrations for schema management
3. Monitoring: Configure Application Insights for production diagnostics
4. Testing: Use SQL Server containers for integration tests

Support

• Quick Start: See QUICKSTART_GUIDE.md
• Main Package: See Chromex.Authentication README
• EF Core: Entity Framework Core Documentation
• Azure SQL: Azure SQL Authentication

License

Copyright © 2025 Timothy Jones. All rights reserved. License