Identity Server 4 loggs (Invalid object name 'DeviceCodes') exception

Question

We are using Identity Server 4 for authentication. After upgrading our solution from .Net Core 2.0 to .Net Core 3.1, we started to notice a new exception be logged when a client redirects to our authentication URL, which did not effect the process and everything seems OK for the end user. The exception is:

An exception occurred while iterating over the results of a query for context type 'IdentityServer4.EntityFramework.DbContexts.PersistedGrantDbContext'.
Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid object name 'DeviceCodes'.
   at Microsoft.Data.SqlClient.SqlCommand.<>c.b__164_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
ClientConnectionId:2b07bb46-18fe-4553-a79f-89b0186556d4
Error Number:208,State:1,Class:16

the query that causes this exception :

Failed executing DbCommand (2ms) [Parameters=[@__p_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT [d].[UserCode], [d].[ClientId], [d].[CreationTime], [d].[Data], [d].[DeviceCode], [d].[Expiration], [d].[SubjectId]
FROM [DeviceCodes] AS [d]
WHERE [d].[Expiration] < GETUTCDATE()
ORDER BY [d].[DeviceCode]

We do not have this table 'DeviceCodes' in our context, Any advice please!

user_8435293 · Answer

You're missing the DeviceCodes table which you will get (now that you have upgraded) by adding a migration from the Package Manager Console window like so:

add-migration Core3Upgrade -Context PersistedGrantDbContext

(Remember to set the correct Startup project in VS and the appropriate Default project in the Package Manager Console according to your setup, else this will fail.)

and then...

update-database -Context PersistedGrantDbContext

(or run whatever initialise code you may use to seed/apply migrations for your auth database)

If successful, the following SQL should get executed:

  Executed DbCommand (27ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
  CREATE TABLE [DeviceCodes] (
      [UserCode] nvarchar(200) NOT NULL,
      [DeviceCode] nvarchar(200) NOT NULL,
      [SubjectId] nvarchar(200) NULL,
      [ClientId] nvarchar(200) NOT NULL,
      [CreationTime] datetime2 NOT NULL,
      [Expiration] datetime2 NOT NULL,
      [Data] nvarchar(max) NOT NULL,
      CONSTRAINT [PK_DeviceCodes] PRIMARY KEY ([UserCode])
  );

Identity Server 4 loggs (Invalid object name 'DeviceCodes') exception

1 Answers