IdentityServer4 Sample with ASP Identity with real 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?

Are you saying you added all the ConfigurationDbContext tables and PersistedGrantDbContext tables to your own ApplicationDbContext? — Brad, May 25 '18 at 00:53
I followed the sample instructions, the migrations created the tables. There was only one default database connection and the migrations only have "ApplicationDbContextModelSnapshots". — user2600177, May 25 '18 at 09:10

user2600177 · Answer 1 · 2018-05-25T09:41:19.007

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.

You may have marked this down, but it seemed to fix things – user2600177 May 25 '18 at 09:10 — user2600177, May 25 '18 at 09:10

score 1 · Answer 2 · answered Aug 17 '20 at 23:30

I was faced with this issue today and did a couple of searches online and stumbled upon this https://entityframeworkcore.com/knowledge-base/46587067/ef-core---do-sqlserver-migrations-apply-to-sqlite-

The link pointed out to switch the annotation portion in the migration class UP method after

Id = table.Column(nullable: false)

from

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

to

.Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn)

And you will need to import

using Microsoft.EntityFrameworkCore.Metadata;

Then you build, and the migration will be successful.

Create . the above solution worked for me. thanks – Ismail Saifo Dec 25 '20 at 15:38 — Ismail Saifo, Dec 25 '20 at 15:38

score 0 · Answer 3 · answered Jul 20 '18 at 14:05

To resolve this particular issue I used SSMS.

right click on table
select script to drop and create
add IDENTITY after the NOT NULL
Execute

However you are correct, it is using sqlite annotations in the sql file and in the migrations.

To fully resolve this issue, you need to create an implementation of all 3 necessary database contexts: identity, persisted grant, and configuration.

That requires an implementation of design time factories for each of those contexts as well.

Then you can run add-migration in the package manager console for each of those contexts, and then run update database, or run the application with the migrate function when seeding.

So to recap:

Create implementations for the 3 db contexts
Create Design time factory implementations for those db contexts
Add the migrations
Update the database with those migrations

IdentityServer4 Sample with ASP Identity with real SQL Server

3 Answers3