IdentityServer4 Sample with ASP Identity with real SQL Server

asp.net-core asp.net-identity entity-framework-core identityserver4 sql-server

Question

I have been struggling to get the final SAMPLE (ASP.Net, EF Core, SQL) to work against a real SQL Server. Every sample I can find does not use real SQL they always opt for in-memory data store

I changed the connection string

"Data Source=.;Initial Catalog=IS4;Integrated Security=True;"

and ran

dotnet ef database update -c ApplicationDbContext

This created me a SQL database with 25 tables.

I tweaked Startup.cs to change

        services.AddDbContext<ApplicationDbContext>(options =>
            options.UseSqlServer(connectionString));

and b.UseSqlite to b.UseSqlServer

            .AddConfigurationStore(options =>
            {
                options.ConfigureDbContext = b =>
                    b.UseSqlServer(connectionString,
                        sql => sql.MigrationsAssembly(migrationsAssembly));
            })
            // this adds the operational data from DB (codes, tokens, consents)
            .AddOperationalStore(options =>
            {
                options.ConfigureDbContext = b =>
                    b.UseSqlServer(connectionString,
                        sql => sql.MigrationsAssembly(migrationsAssembly));

                // this enables automatic token cleanup. this is optional.
                options.EnableTokenCleanup = true;
                // options.TokenCleanupInterval = 15;
            });

I ran the server with "/seed" on the command line but the Seed functionality doesn't work

First it complains CLIENT can't have a NULL ID when it calls SaveChanges(). If I change the code to add the ID

        if (!context.Clients.Any())
        {
            Console.WriteLine("Clients being populated");
            int i = 1;
            foreach (var client in Config.GetClients().ToList())
            {
                var x = client.ToEntity();
                x.Id = i++;
                context.Clients.Add(x);
            }
            context.SaveChanges();
        }
        else
        {
            Console.WriteLine("Clients already populated");
        }

I then get

"Cannot insert the value NULL into column 'Id', table 'IS4.dbo.ClientGrantTypes".

When I watch the video's it says it can be migrated from SQLite to full SQL simply by changing the connection string which is obviously not true, given all the other changes I have done, so I must be doing (or missing) something else.

Any thoughts?

1
0
5/25/2018 12:11:08 AM

Popular Answer

Could it be that all the tables with an "Id INT" column should all be IDENTITY columns and they are not!

I checked the migrations code and it has

protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "ApiResources",
                columns: table => new
                {
                    Id = table.Column<int>(nullable: false)
                        .Annotation("Sqlite:Autoincrement", true),
                    Description = table.Column<string>(maxLength: 1000, nullable: true),
                    DisplayName = table.Column<string>(maxLength: 200, nullable: true),

I am guessing

.Annotation("Sqlite:Autoincrement", true),

doesn't work with full SQL and therefore all the tables need identity properties setting.

Interestingly if you run the other template to add the AdminUI

dotnet new is4admin

It seems to add a couple of SQL scripts

CREATE TABLE "Clients" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_Clients" PRIMARY KEY AUTOINCREMENT,
    "AbsoluteRefreshTokenLifetime" INTEGER NOT NULL,
    "AccessTokenLifetime" INTEGER NOT NULL,

which does make them identity columns.

1
5/25/2018 9:41:19 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow