Entity Framework and Securing Connection Strings
# Security of Connection Strings
The connection strings should not be hard-coded or checked in to any version control system.
# Case A: A Windows WPF Application
## Problem
* My data access classes, including the `DbContext` are in a platform-agnostic class library.
* For the Windows client application, the connection string is saved within the registry in an encrypted format:
* In production, the installer will add the necessary encrypted string to registry.
* In development, the encrypted string is added as an environment variable
* All the mechanisms above are for windows, but the `DbContext` is in a platform-agnostic library. Therefore it uses dependency injection (specifically *constructor injection*) to acquire the connection string:
* The `ServiceLocator` class that will work on the Windows app first acquire the encrypted connection string either from environment variables or registry
* Then it will decrypt it, and pass it to the `DbContext` constructor
* By this way, the string is protected.
* The major issue arises on using Entity Framework CLI commands such as `dotnet ef migration add`. These commands need to be able to instantiate a `DbContext`, but they don’t know the connection string so they will fail.
## Solution
`EntityFrameworkCore.Design` has an interface named `IDesignTimeDbContextFactory` which is a factory for creating derived `DbContext` instances. Implementing this interface enables design-time services for context types that do not have a public **default** constructor. At design-time, derived context instances can be created in order to enable specific design-time experiences such as Migrations.
The following NuGet packages are required:
* Microsoft.Extensions.Configuration
* Microsoft.Extensions.Configuration.Json
* Microsoft.EntityFrameworkCore.Design
We create a factory class implementing this interface:
```csharp
public class BISDbContextFactory : IDesignTimeDbContextFactory
{
public MyDbContext CreateDbContext(string[] args)
{
var configuration = new ConfigurationBuilder()
.AddJsonFile("appsettings.json", optional: false)
.Build();
var connectionString = configuration.GetConnectionString("DefaultConnection") ?? throw new InvalidOperationException("Could not find connection string 'DefaultConnection' in appsettings.json");
return new MyDbContext(connectionString);
}
}
```
Of course, the derived context class should have appropriate constructors:
```csharp
public partial class BISDbContext : DbContext
{
private readonly string connectionString;
public BISDbContext(string connectionString)
{
this.connectionString = connectionString;
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(
connectionString,
options =>
{
options.EnableRetryOnFailure();
options.CommandTimeout(60);
}
);
#if DEBUG
// optionsBuilder.AddInterceptors(new EFCommandInterceptor());
#endif
}
// dbsets
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
// use default inheritance mapping - TPH
// custom mappings
}
}
```
The _appsettings.json_ is a file that contains the connection details:
```json
{
"ConnectionStrings": {
"DefaultConnection": "Server=connectionserver;...;"
}
}
```
**This file should be added to the .gitignore**
## Usage
Once the above configuration is completed, the CLI tools can be used as usual. If `--verbose` flag is added, we can see the process flow:
> Finding DbContext classes...
Finding IDesignTimeDbContextFactory implementations...
Found IDesignTimeDbContextFactory implementation 'MyDbContextFactory'.
Found DbContext 'MyDbContext'.
Finding application service provider in assembly 'Core'...
Finding Microsoft.Extensions.Hosting service provider...
No static method 'CreateHostBuilder(string\[\])' was found on class 'Program'.
No application service provider was found.
Finding DbContext classes in the project...
Using DbContext factory 'MyDbContextFactory'.
Using context 'MyDbContext'.
Finding design-time services referenced by assembly 'Core'...
Finding design-time services referenced by assembly 'Core'...
No referenced design-time services were found.
Finding design-time services for provider 'Microsoft.EntityFrameworkCore.SqlServer'...
Using design-time services from provider 'Microsoft.EntityFrameworkCore.SqlServer'.
Finding IDesignTimeServices implementations in assembly 'Core'...
No design-time services were found.
Writing migration to '....cs'.
Writing model snapshot to '....cs'.
'MyDbContext' disposed.
# Case B: ASP.NET Application
## Problem
The issue is identical with the WPF application case; the codebase is divided into many projects with different responsibilities:
1. Core: Models, interfaces, DTOs, etc.
2. Infrastructure: DAOs, repositories, primary key objects (like sequential GUID definitions), etc.
3. WebApp: The actual ASP.NET application.
The connection string for the database is secured differently for development and production:
* In development, the connection string is saved in the *secrets.json* file. This file is on the configuration build chain, and allows to overwrite config details of production during development.
* In production, the secret is fetched from an Azure Vault.
## Solution and Usage
**The codes below change along the explanations. For the final version, check the end of this section.**
The `Infrastructure` project contains the `DbContext` implementation, but it requires the connection string. Instead, we supply the whole database connection configuration:
```c#
public class BwEdsDbContext : DbContext
{
private readonly string? _connectionString;
public BwEdsDbContext(DbContextOptions options) : base(options)
{
}
...
```
This allows to add a `DbContext` to WebApp service builder without the `Infrastructure` knowing anything about the database configuration:
```c#
if (!builder.Environment.IsDevelopment())
{
builder.Configuration.AddAzureKeyVault(
new Uri($"https://{builder.Configuration\["KeyVault:Name"\]}.vault.azure.net/"),
new DefaultAzureCredential());
}
builder.Services.AddDbContext(options =>
{
var connectionString = builder.Configuration.GetConnectionString("bw-eds-mssql-connection-string");
options.UseSqlServer(connectionString, sqlServerDbContextOptionsBuilder =>
{
sqlServerDbContextOptionsBuilder.EnableRetryOnFailure();
sqlServerDbContextOptionsBuilder.CommandTimeout(60);
sqlServerDbContextOptionsBuilder.UseNetTopologySuite();
});});
...
```
Since all the config parameters, including even the database type, are configured within WebApp, the `Infrastructure` can remain agnostic to the underlying database technology and configuration.
The issue arises on database migrations and updates. The EF migration and update commands should be run in the project `Infrastructure`, however, now it does not know anything about the database. Therefore we need to instruct EF to how to create a `DbContext` instance via an implementation of `IDesignTimeDbContextFactory`:
```c#
public sealed class BwEdsDbContextFactory : IDesignTimeDbContextFactory
{
public BwEdsDbContext CreateDbContext(string[] args)
{
var configuration = new ConfigurationBuilder()
.AddUserSecrets() // just any class to associate the secrets with the project
.Build();
var connectionString = configuration.GetConnectionString("bw-eds-mssql-connection-string")
?? throw new InvalidOperationException("Could not find connection string 'bw-eds-mssql-connection-string' in user secrets");
return new BwEdsDbContext(connectionString);
}
}
```
Yet the factory still needs the connection string, and it is defined within the `WebApp`. We definitely do not want any cyclic dependencies, nor mixing up the responsibilities of different projects, so we add the secret to the `Infrastructure` project, too:
```bash
# Run these commands in the Infrastructure root directory
dotnet user-secrets init
dotnet user-secrets set "ConnectionStrings:bw-eds-mssql-connection-string" "your-connection-string"
```
The next issue to solve is the database connection configuration. When the `WebApp` initializes the `DbContext`, it supplies the complete configuration. However, our factory is designed to serve only the connection string. The two possible options are either modifying the factory to also give the full configuration, or adding a new constructor and overriding `OnConfiguring` of the `DbContext` implementation. The second approach can be implemented as follows:
```c#
public class BwEdsDbContext : DbContext
{
private readonly string? _connectionString;
public BwEdsDbContext(string connectionString)
{
_connectionString = connectionString;
}
public BwEdsDbContext(DbContextOptions options) : base(options)
{
}
// DbSet here
...
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
if (!optionsBuilder.IsConfigured && _connectionString != null)
{
optionsBuilder.UseSqlServer(_connectionString, x => x.UseNetTopologySuite());
}
}
}
```
Implementing the first approach, however, has more advantages. We can even centralize the database connection configuration generation, having a single point of truth:
```c#
///
/// The single point of truth for configuring the database context.
///
public static class DbContextConfigurationGenerator
{
///
/// Configures the with the specified connection string and options.
///
///
///
/// The configured .
///
/// The connection string is required to be passed in because the database context requires it to be passed in.
/// The configuration options are retrying on failure for resilience, setting a command timeout of 60 seconds, and
/// using NetTopologySuite for spatial data.
///
public static void ConfigureBwEdsDbContext(
DbContextOptionsBuilder optionsBuilder,
string connectionString)
{
optionsBuilder.UseSqlServer(connectionString, sqlServerDbContextOptionsBuilder =>
{
sqlServerDbContextOptionsBuilder.EnableRetryOnFailure();
sqlServerDbContextOptionsBuilder.CommandTimeout(60);
sqlServerDbContextOptionsBuilder.UseNetTopologySuite();
});
}
}
```
This requires to revise the factory:
```c#
public sealed class BwEdsDbContextFactory : IDesignTimeDbContextFactory
{
public BwEdsDbContext CreateDbContext(string[] args)
{
var configuration = new ConfigurationBuilder()
.AddUserSecrets() // just any class to associate the secrets with the project
.Build();
var connectionString = configuration.GetConnectionString("bw-eds-mssql-connection-string")
?? throw new InvalidOperationException("Could not find connection string 'bw-eds-mssql-connection-string' in user secrets");
var optionsBuilder = new DbContextOptionsBuilder();
DbContextConfigurationGenerator.ConfigureBwEdsDbContext(optionsBuilder, connectionString);
return new BwEdsDbContext(optionsBuilder.Options);
}
}
```
And the web app:
```c#
builder.Services.AddDbContext(options =>
{
var connectionString = builder.Configuration.GetConnectionString("bw-eds-mssql-connection-string")
?? throw new InvalidOperationException("Could not find connection string 'bw-eds-mssql-connection-string' in user secrets");
DbContextConfigurationGenerator.ConfigureBwEdsDbContext(options, connectionString);
});
```
We can now simplify the `DbContext` implementation:
```c#
public class BwEdsDbContext(DbContextOptions options) : DbContext(options)
{
public DbSet Sensors { get; set; }
...
}
```
Now we can use EF commands such as `migration add` and `database update` **from the Infrastructure** root directory.