Failing client connections with SQL OPENJSON Incorrect Syntax error after upgrading Identity Server 6 to 7

The Issue

Whilst updating a client’s Duende Identity Server from versions 6 to 7, we experienced a problem. We followed the upgrade steps and all was working fine against our development instance i.e. the Identity Server Db was upgraded to the current schema and we could login from our test MVC web client without issues.

The problem occured when we started to test using our UAT (a production replica) DB. On loading the Identity Server, it did the expected EF migrations and appeared to start, but when a client tried to connect we got an exception in the Identity Server logs in the form

OPENJSON - Incorrect syntax near the keyword '$'

The Solution

The issue it turns out was the SQL DB Compatibility Level as OPENJSON requires Compatibility Level 130. The UAT (and production) DB’s compatibility level had not been updated when the SQL server instance was upgraded, but it had not been an issue until now.

Once the DB compatibility level was updated, the Identity Server started working as expected. We did this via SQL Management Studio, right-clicking on the DB, selecting properties, and then setting the compatibility level to 150 (SQL 2019), but you could use the command

ALTER DATABASE IdentityServer SET COMPATIBILITY_LEVEL = 150

For the original version of this post see Richard Fennell's personal blog at Failing client connections with SQL OPENJSON Incorrect Syntax error after upgrading Identity Server 6 to 7