We're moving a legacy service to use Dapper, and I'm unsure where to keep the SQL Server connection string. I know not to hardcode it, but I'm torn between appsettings.json, Azure Key Vault, and environment variables.
Local development
For my machine, I usually just drop it in appsettings.json under a ConnectionStrings section. It's quick, but I worry about accidentally committing it if I use a shared repo.
Production
In Azure App Service, I've seen teams inject it via Application Settings. That works, yet it feels less structured than a dedicated secrets manager. If we go with Key Vault, how much extra plumbing does Dapper actually require? Do I need a custom IDbConnectionFactory, or can I resolve SqlConnection directly from DI?
Quick example
Here's how I currently register it:
// Register SqlConnection in Program.cs
builder.Services.AddScoped<IDbConnection>(() =>
new SqlConnection(builder.Configuration.GetConnectionString("Default")));
Is this safe enough, or should I be wrapping it in a factory pattern? Also, any gotchas with connection pooling when the lifetime is Scoped?
With Dapper, connection-string management is mostly about keeping credentials out of code and making sure each database operation gets a connection with a sensible lifetime. A small factory keeps connection creation consistent without holding one open connection for the life of the API.
Keep credentials in configuration, not source control
Use ASP.NET Core’s configuration system to read the connection string by name. For local development, use Secret Manager; in deployed environments, supply it through a managed secret store or protected environment configuration. An environment variable can use the key
ConnectionStrings__AppDb. Avoid committing production credentials toappsettings.json, and never log the full connection string.Fail during startup if the required setting is missing. This is easier to diagnose than discovering the problem on the first database request.
Create a connection per unit of work
Register a factory, not a shared
SqlConnection. The factory can be a singleton because it only holds the connection string; each repository call creates and disposes its own connection. Disposing a connection returns it to ADO.NET’s connection pool, so opening a new one for an operation usually does not mean a new physical database connection.A few details worth checking
If the API uses a database provider other than SQL Server, keep the same configuration and lifetime approach but use that provider’s connection type and review its encryption and pooling options.