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?

score 5 · Accepted Answer · answered Jan 15 '19 at 22:25

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)');

score 2 · Answer 2 · answered Jul 24 '20 at 17:17

Hope this would help.

CREATE FUNCTION [dbo].[ConvertToSha256ForIdentityServer](@secret varchar(128))
RETURNS varchar(128) AS
BEGIN
    DECLARE @HASHBYTES VARBINARY(128) = hashbytes('sha2_256', @secret)
    RETURN (SELECT top 1 cast(N'' as xml).value('xs:base64Binary(sql:variable("@HASHBYTES"))', 'varchar(128)'));
END

to insert

MERGE INTO dbo.[ClientSecrets] AS TARGET
USING (
        VALUES
         (dbo.ConvertToSha256ForIdentityServer('mysecret'),'SharedSecret',getdate(),1)
      )
      AS Source ([Value],[Type],[Created],[ClientId])
ON Target.[ClientId] = source.[ClientId]
WHEN matched THEN
  UPDATE SET
      [Value] = Source.[Value],
      [Type] = Source.[Type]

WHEN NOT matched BY target THEN
  INSERT ([Value],[Type],[Created],[ClientId])
  VALUES ([Value],[Type],[Created],[ClientId]);

Notice that this and [the accepted answer](https://stackoverflow.com/a/54207758/23118) produces different result! This `ConvertToSha256ForIdentityServer` function generated values that matched the existing entries in the `ApiSecrets` table, so from my perspective this is the correct answer. — hlovdal, May 25 '22 at 14:22

Derviş Kayımbaşıoğlu · Answer 3 · 2019-01-15T21:55:45.423

You can use the algorithm below to create secret, then you can store it in SQL:

using System.Security.Cryptography;

static class Extentions
{

    public static string Sha256(this string input)
    {
        using (SHA256 shA256 = SHA256.Create())
        {
            byte[] bytes = Encoding.UTF8.GetBytes(input);
            return Convert.ToBase64String(((HashAlgorithm)shA256).ComputeHash(bytes));
        }
    }
}


void Main()
{
    Console.WriteLine( "secret-as-guid".Sha256());
}

you can also use passwordgenerator.net

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

3 Answers3

Linked