How to insert Identity Server 4 persisted ApiSecret values using T-SQL

Question

I have been through the Identity Server 4 QuickStart for using Entity Framework for persistent storage of configuration and operational data. In the QuickStart, the ApiResources are loaded into the database in code. The Api secret is set with

        new ApiResource("api1", "My API")
        {
            ApiSecrets = { new Secret("secret".Sha256())}
        }

in the ApiResource constructor. When, in Startup.InitializeDatabase, that ApiResource is added to the ConfigurationDbContext.ApiResources DbSet,

        foreach(var resource in Config.GetApiResources())
        {
            context.ApiResources.Add(resource.ToEntity());
        }
        context.SaveChanges();

the record in the child ApiSecrets table contains a readable text value in the ApiSecrets.Value field.

    K7gNU3sdo+OL0wNhqoVWhr3g6s1xYv72ol/pe/Unols=

I would like to manage my configuration data through SQL scripts, but I can't figure out how to set the ApiSecrets.Value correctly. I've tried using T-SQL HASHBYTES('SHA2_256', 'secret'), but that produces an unreadable (I think binary) value in ApiSecrets.Value. Is there a way to set the hashed secret correctly through T-SQL?

Answer 1

You were on the right track to use HASHBYTES, just need to get the Base64 hash out of the BinaryHash:

DECLARE @HASHBYTES VARBINARY(128) = hashbytes('sha2_256', 'secret')
SELECT cast(N'' as xml).value('xs:base64Binary(sql:variable("@HASHBYTES"))', 'varchar(128)');