The connection string is the application’s gateway to the database — its format, security, and performance settings affect every database call. The BlogApp needs different connection strings for LocalDB development, a full SQL Server developer instance, and production Azure SQL. Storing connection strings correctly (environment-specific config files and secrets management) is as important as the string format itself.
Connection String Formats
// ── appsettings.json — development defaults ────────────────────────────────
// {
// "ConnectionStrings": {
// "Default": "Server=(localdb)\\MSSQLLocalDB;Database=BlogApp;Trusted_Connection=True;TrustServerCertificate=True;Application Name=BlogApp"
// }
// }
// ── Connection string variants ─────────────────────────────────────────────
// LocalDB (Windows Authentication — no password needed):
// Server=(localdb)\MSSQLLocalDB;Database=BlogApp;Trusted_Connection=True;TrustServerCertificate=True
// Full SQL Server — Windows Authentication:
// Server=localhost;Database=BlogApp;Trusted_Connection=True;TrustServerCertificate=True
// Full SQL Server — SQL Server Authentication:
// Server=localhost;Database=BlogApp;User Id=blogapp_user;Password=BlogApp_Str0ng!Pass;TrustServerCertificate=True
// Azure SQL Database (always encrypted):
// Server=yourserver.database.windows.net;Database=BlogApp;User Id=admin@yourserver;Password=...;Encrypt=True;Connection Timeout=30
// Docker SQL Server container:
// Server=localhost,1433;Database=BlogApp;User Id=sa;Password=YourStr0ngPassw0rd!;TrustServerCertificate=True// ── Program.cs — register DbContext ───────────────────────────────────────
builder.Services.AddDbContext<AppDbContext>(opts =>
opts.UseSqlServer(
builder.Configuration.GetConnectionString("Default"),
sqlOpts =>
{
sqlOpts.EnableRetryOnFailure(
maxRetryCount: 3,
maxRetryDelay: TimeSpan.FromSeconds(30),
errorNumbersToAdd: null // retry on all transient errors
);
sqlOpts.CommandTimeout(30); // seconds before timeout
sqlOpts.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery);
}
)
);
// ── appsettings.Development.json — LocalDB ────────────────────────────────
// {
// "ConnectionStrings": {
// "Default": "Server=(localdb)\\MSSQLLocalDB;Database=BlogApp;Trusted_Connection=True;TrustServerCertificate=True;Application Name=BlogApp"
// }
// }
// ── Production — environment variable (never in appsettings.json) ─────────
// ConnectionStrings__Default=Server=prod.server;Database=BlogApp;User Id=app;Password=...;Encrypt=True
// Double underscore (__) maps to nested JSON in ASP.NET Core configuration
// ── Connection string components explained ────────────────────────────────
// Application Name=BlogApp → tags queries in SQL Server activity monitor
// TrustServerCertificate=True → bypass SSL cert validation (dev only — never prod)
// Encrypt=True → require encrypted connection (always for Azure SQL)
// Connect Timeout=30 → seconds to wait for connection
// Min Pool Size=5;Max Pool Size=100 → connection pool boundsNote:
TrustServerCertificate=True bypasses TLS certificate validation — the connection is still encrypted, but the certificate is not verified. This is necessary in development with self-signed certificates but must be False in production. In production, use a proper TLS certificate or set Encrypt=True;TrustServerCertificate=False to require a valid certificate. Azure SQL always requires Encrypt=True and validates the certificate automatically.Tip: Always include
Application Name=BlogApp in the connection string. This tag appears in SQL Server’s activity monitor, sys.dm_exec_sessions, and Extended Events — making it easy to identify which queries come from your application vs SSMS vs other tools. In production with multiple services connecting to the same database, distinguishing their connections by application name is invaluable for debugging performance issues and connection leaks.Warning: Never store production connection strings (with passwords) in
appsettings.json — this file is typically committed to source control. Use environment variables for production connection strings: ConnectionStrings__Default as an environment variable overrides the appsettings value. ASP.NET Core’s configuration system automatically reads environment variables in the format Section__Key (double underscore for nesting). In Azure, use App Service Connection Strings or Key Vault references.Connection Pooling Reference
// ── Connection pooling settings ────────────────────────────────────────────
// Pooling=True → enable pooling (default: True)
// Min Pool Size=5 → minimum connections kept open
// Max Pool Size=100 → maximum concurrent connections
// Connection Lifetime=0 → max age in seconds (0=indefinite)
// Connection Timeout=30 → wait time for a free pool connection
// ── Verify connection in code ─────────────────────────────────────────────
// app.MapGet("/health/db", async (AppDbContext db) => {
// await db.Database.ExecuteSqlRawAsync("SELECT 1");
// return Results.Ok("Database connection healthy");
// });Common Mistakes
Mistake 1 — TrustServerCertificate=True in production (bypasses security validation)
❌ Wrong — production connection string disables certificate validation; man-in-the-middle attacks possible.
✅ Correct — TrustServerCertificate=False (default) in production; use a valid TLS certificate on the SQL Server.
Mistake 2 — Storing passwords in appsettings.json (credentials in source control)
❌ Wrong — "Password=Prod!Pass" in appsettings.json committed to git; credentials exposed to everyone with repo access.
✅ Correct — use environment variables or Azure Key Vault references for production passwords; use dotnet user-secrets for local development secrets.