Create SQL XmlRepository for storing Data Protection keys

[aspnet-hello]

From @janpieterz on Saturday, October 7, 2017 8:02:51 AM

Would make sense as it's one of the most common shared pieces of infrastructure between multiple servers.

It's mentioned in aspnet/DataProtection#145 but doesn't seem to have been built.

Copied from original issue: aspnet/DataProtection#280

[aspnet-hello]

From @blowdart on Friday, October 13, 2017 11:58:42 AM

@muratg It's worth doing, but I think trying to make it generic, or indeed EF model based might be more bother than its worth. A straightforward SQL, with parameterised queries would do it.

[aspnet-hello]

From @urbanhusky on Monday, November 27, 2017 12:46:54 AM

I'm trying to implement such a repository and I use EF. I struggle with figuring out how to properly resolve the corresponding DbContext. The context would be registered as scoped in the DI container, but I don't know how the repository is being registered - or if I would have to register it myself.

I do the following:

// Register db context
services.AddDbContext<DataProtectionDbContext>(
    opts =>
    {
        var dpapiMigrationsAssembly = typeof(DataProtectionDbContext).GetTypeInfo().Assembly.GetName().Name;
        opts.UseSqlServer(dpapiConnectionString, b => b.MigrationsAssembly(dpapiMigrationsAssembly));
    });

// configure dpapi
services.AddDataProtection()
    .ProtectKeysWithCertificate(GetCertificate()) // no support for rollover or revocation when using certificate?
    .AddKeyManagementOptions(options => options.XmlRepository = new SqlDatabaseXmlRepository(/* TODO: how to resolve? Repository needs to be an instance and any DI would be for *right now* */))

[dansward]

I have an EntityFrameworkCore implementation of this along with tests and a sample ready for review. Please let me know if it is ok to submit a pull request.

[natemcmaster]

@dansward thanks for your PR to kick off the conversation on this subject.

We would be interested in taking a well-written PR to create this feature. At minimum, we would hope to see an implementation that satisfies the following:

• There is a way for users to create the expected database schema.
• There is a way for users to configure the repository by providing a connection string
• There is a way for users to change between database providers, such as SQL Server or Postgres.

These requirements are pretty high-level, so let's have a conversation about the options for implementing these. One option includes using Entity Framework Core, but that is not the only alternative.

[natemcmaster]

Done in aspnet/DataProtection#303 and aspnet/DataProtection#323. Thanks @dansward!