Identityserver 4.1.1 to: 6.0.0 db migration error: There is already an object named 'PersistedGrants' in the database.

[gunnars04]

Which version of Duende IdentityServer are you using? I'm updating from: Identityserver 4.1.1 to: 6.0.0 (I'm done updating all the nuget) We're using SQL Server.

Which version of .NET are you using? I've updated the projects to TargetFramework: net6.0

Describe the bug When running this migration: "IdentityServer4 v4.1 to Duende IdentityServer v6" https://docs.duendesoftware.com/identityserver/v6/upgrades/is4_v4_to_dis_v6/

when I run (I had to update the schema "Identity" to "dbo" since we're using the dbo schema for all our tables): dotnet ef database update -c PersistedGrantDbContext

I get:

Build started...
Build succeeded.
Failed executing DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE [dbo].[PersistedGrants] (
    [Key] nvarchar(200) NOT NULL,
    [Type] nvarchar(50) NOT NULL,
    [SubjectId] nvarchar(200) NULL,
    [ClientId] nvarchar(200) NOT NULL,
    [CreationTime] datetime2 NOT NULL,
    [Expiration] datetime2 NULL,
    [Data] nvarchar(max) NOT NULL,
    CONSTRAINT [PK_PersistedGrants] PRIMARY KEY ([Key])
);
Microsoft.Data.SqlClient.SqlException (0x80131904): There is already an object named 'PersistedGrants' in the database.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean isAsync, Int32 timeout, Boolean asyncWrite)
   at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ClientConnectionId:5914539b-b4a6-4165-9a5a-d4a34406b5e7
Error Number:2714,State:6,Class:16
There is already an object named 'PersistedGrants' in the database.

The table dbo.PersistedGrants already exists and has a bunch of data in it. What should I do?

PersistedGrant
Models persistence of authorization codes, reference and refresh tokens, and user consents.

https://docs.duendesoftware.com/identityserver/v5/reference/stores/persisted_grant_store/ I guess I can just drop the table in sql server and let the migration script re-create it, right?

My current table has these 3x columns that are not in the migration "create table" log above (see the bottom 3x columns below):

CREATE TABLE [dbo].[PersistedGrants](
    [Key] [nvarchar](200) NOT NULL,
    [Type] [nvarchar](50) NOT NULL,
    [SubjectId] [nvarchar](200) NULL,
    [ClientId] [nvarchar](200) NOT NULL,
    [CreationTime] [datetime2](7) NOT NULL,
    [Expiration] [datetime2](7) NULL,
    [Data] [nvarchar](max) NOT NULL,
    [ConsumedTime] [datetime2](7) NULL,
    [Description] [varchar](200) NULL,
    [SessionId] [varchar](200) NULL)

[gunnars04]

Update: I deleted [dbo].[PersistedGrants] and I let the migration create it again.

Now when I run: dotnet ef database update -c PersistedGrantDbContext

I get this error:

Build started... Build succeeded. Failed executing DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] DECLARE @defaultSchema sysname = SCHEMA_NAME(); EXEC(N'ALTER SCHEMA [' + @defaultSchema + N'] TRANSFER [Identity].[PersistedGrants];'); Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot find the object 'PersistedGrants', because it does not exist or you do not have permission. at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean isAsync, Int32 timeout, Boolean asyncWrite) at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName) at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject) at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues) at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection) at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration) at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType) at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType) at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0() at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action) ClientConnectionId:6d1682d9-6d92-469f-b49f-f955fddef6c2 Error Number:15151,State:1,Class:16 Cannot find the object 'PersistedGrants', because it does not exist or you do not have permission.

[gunnars04]

How come does your PersistedGrants table contain more columns in IdentityServer 7? https://github.com/DuendeSoftware/IdentityServer/blob/main/migrations/IdentityServerDb/Migrations/PersistedGrantDb.sql

After running the migration, it looks like this:

CREATE TABLE [dbo].[PersistedGrants]( [Key] nvarchar NOT NULL, [Type] nvarchar NOT NULL, [SubjectId] nvarchar NULL, [ClientId] nvarchar NOT NULL, [CreationTime] datetime2 NOT NULL, [Expiration] datetime2 NULL, [Data] nvarchar NOT NULL)

Is that correct?

[gunnars04]

When I run: dotnet ef database update -c ConfigurationDbContext

and update the generated file to use dbo schema instead of identity

I get:

Build started... Build succeeded. Failed executing DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] CREATE TABLE [dbo].[ApiResources] ( [Id] int NOT NULL IDENTITY, [Enabled] bit NOT NULL, [Name] nvarchar(200) NOT NULL, [DisplayName] nvarchar(200) NULL, [Description] nvarchar(1000) NULL, CONSTRAINT [PK_ApiResources] PRIMARY KEY ([Id]) ); Microsoft.Data.SqlClient.SqlException (0x80131904): There is already an object named 'ApiResources' in the database. at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean isAsync, Int32 timeout, Boolean asyncWrite) at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName) at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject) at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues) at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection) at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration) at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType) at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType) at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0() at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action) ClientConnectionId:5559ea1b-69ca-4840-a504-28022d30339d Error Number:2714,State:6,Class:16 There is already an object named 'ApiResources' in the database.

[gunnars04]

How come then I run: dotnet ef migrations add UpdateToDuende_v6_0 -c PersistedGrantDbContext -o Data/Migrations/IdentityServer/PersistedGrantDb

I get:

public partial class InitialIdentityServerMigration : Migration { protected override void Up(MigrationBuilder migrationBuilder) { migrationBuilder.EnsureSchema( name: "dbo");

        migrationBuilder.CreateTable(
            name: "PersistedGrants",
            schema: "dbo",
            columns: table => new
            {
                Key = table.Column<string>(maxLength: 200, nullable: false),
                Type = table.Column<string>(maxLength: 50, nullable: false),
                SubjectId = table.Column<string>(maxLength: 200, nullable: true),
                ClientId = table.Column<string>(maxLength: 200, nullable: false),
                CreationTime = table.Column<DateTime>(nullable: false),
                Expiration = table.Column<DateTime>(nullable: true),
                Data = table.Column<string>(maxLength: 50000, nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_PersistedGrants", x => x.Key);
            });

        migrationBuilder.CreateIndex(
            name: "IX_PersistedGrants_SubjectId_ClientId_Type",
            schema: "dbo",
            table: "PersistedGrants",
            columns: new[] { "SubjectId", "ClientId", "Type" });
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(
            name: "PersistedGrants",
            schema: "dbo");
    }
}

Without sessionid column, but here it's included?

CREATE TABLE [PersistedGrants] ( [Id] bigint NOT NULL IDENTITY, [Key] nvarchar(200) NULL, [Type] nvarchar(50) NOT NULL, [SubjectId] nvarchar(200) NULL, [SessionId] nvarchar(100) NULL, [ClientId] nvarchar(200) NOT NULL, [Description] nvarchar(200) NULL, [CreationTime] datetime2 NOT NULL, [Expiration] datetime2 NULL, [ConsumedTime] datetime2 NULL, [Data] nvarchar(max) NOT NULL, CONSTRAINT [PK_PersistedGrants] PRIMARY KEY ([Id]) ); GO

https://github.com/DuendeSoftware/IdentityServer/blob/main/migrations/IdentityServerDb/Migrations/PersistedGrantDb.sql

[AndersAbel]

It looks like the initial state of the Entity Framework migrations is incorrect as it tries to create the table instead of updating/altering it.

The persisted grants table might contain both short- and long-lived data. If you drop it and there is long-lived data (such as consents) it will have impacts on your users. It's impossible to give a generic answer of the impact, it all depends on the configuration you have.

This is not really a Duende.IdentityServer issue but concerns general database schema handling. I would recommend checking the documentation on Entity Framework Core migrations to get some more background information.

If you want/need hands on help to migrate, please get in touch for information about our remote consulting services.

[gunnars04]

@AndersAbel I think I know why this is happening.

There is no "__EFMigrationsHistory" table, it's only created now when I run the migration.

It seems that last time when identityserver was updated, a db script was generated (based on the create table migration script I assume).

So it seems I have to let the migration create all the tables etc. and then use diff to try to find out what changed.

Is there any other way I can use that you can think of?

[gunnars04]

@AndersAbel But this [dbo].[__EFMigrationsHistory] doesn't really contain anything useful. How can I make ef migrations detect my current tables and try to update it (insteaf ef re-creating them)?

Is is possible that it's behaving like this because my tables use the dbo schema (and not the default Identity schema)?

[AndersAbel]

You can use EF Migrations to create an initial reference database from the same version that your current database is.

Then

• Copy over the __EFMigrationsHistory table including the contents to your production database.
• Create the upgrade migration in the same project.

[gunnars04]

@AndersAbel First, I updated my old identityserver table schema to a tmp schema. F.ex. ALTER SCHEMA tmp TRANSFER dbo.ApiResources

Since there was no migration history table, I created it by running the duende migration. I ran the duende migrations that created all the tables/indexes/etc. https://docs.duendesoftware.com/identityserver/v6/upgrades/is4_v4_to_dis_v6/

Then I created a data migration script (for allt the tables), f.ex. : INSERT INTO dbo.ApiResourceClaims ([Id] ,[ApiResourceId] ,[Type]) SELECT [Id] ,[ApiResourceId] ,[Type] FROM tmp.ApiResourceClaims

New tables that didn't exist before (empty): dbo.IdentityProviders dbo.Keys

1x old table doesn't exist among the new ones (it has data): DataProtectionKeys Is the data not needed somewhere?

[AndersAbel]

The DataProtectionKeys table is not part of the IdentityServer product, but is created when Asp.Net Core Data Protection is configured to use a database through Entity Framework for the data protection keys. See https://learn.microsoft.com/en-us/aspnet/core/security/data-protection/implementation/key-storage-providers?view=aspnetcore-8.0&tabs=visual-studio#entity-framework-core for more information.

It is typically good to preserve the DataProtectionKeys on migrations.

[josephdecock]

Is anything further needed on this issue, or should we close it?

[josephdecock]

Closing, but feel free to reopen if necessary.