SCCM console connectivity issues with SQL Server 2019 – Connection may have been terminated by the server

I had set up a new lab a few days ago using the configuration manager current branch baseline version 2002 with SQL Server 2019.

Installation went fine and I was able to play with it.

By the way, I have used the Johan hydration kit to set up this lab to save some time. Though the current hydration kit that is available is with SQL 2017, I tweaked it and added SQL 2019 for my lab setup.

After a couple of days, when I tried to launch the configuration manager console, I ended up seeing the following screen.

image

Since the above error talks about the connectivity to SMS provider, I started looking at the SMSPROV.log

image

Error from the log:

[HY000][0][Microsoft][SQL Server Native Client 11.0]Unspecified error occurred on SQL Server. Connection may have been terminated by the server.

[HY000][0][Microsoft][SQL Server Native Client 11.0]Unspecified error occurred on SQL Server. Connection may have been terminated by the server.

[HY000][596][Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot continue the execution because the session is in the kill state.   [HY000][0][Microsoft][SQL Server Native Client 11.0]Unspecified error occurred on SQL Server. Connection may have been terminated by the server.  ThreadID : 4844 , DbError: 596 , Sev: 21~*~*

SQL Error: [HY000][596][Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot continue the execution because the session is in the kill state.

If you use SQL Server 2019 for configuration manager 1910 and above (supported versions) , there's a known issue with the new scalar UDF inlining feature in SQL 2019 and the workaround to fix this issue is to disable the UDF lining using following command.

Open the SQL server management studio and run the following syntax:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF

image

After you run the syntax, you need to restart the SQL server service.

without restarting the SQL Service, you will not be able to launch the console.

Go to services.msc and restart SQL Server

image

After the SQL Service started, launch the console, you should be able to connect to SMS Provider.

image

Though SQL server 2019 is supported for configuration manager build 1910 onwards, this is know issue and there is also workaround for this provided by Microsoft for now.

Reference:

https://docs.microsoft.com/en-us/mem/configmgr/core/plan-design/configs/support-for-sql-server-versions#known-issue-with-sql-server-2019

Post Comment