PENDING Configuration Manager MP SQL Server Access is denied

[rhughes]

Good morning,

I recently began the upgrade process of our Configuration Manager installation at work. I had installed and set this up many years ago and it's been trickling along, but the VM's running the roles are Server 2012 R2. As part of upgrading to Configuration Manager 2303, I have also began to replace the 2012 R2's with 2019.

I have ran into a few problems but one problem that I can't seem to resolve is where the Management Points (we have 3), are randomly reporting that they've lost contact with the database... but they appear to still function? The database server is one of the servers that I have re-built, this was previously on a Server 2012 R2 VM with MSSQL 2017. I went through the process of backing up the databases that were hosted on this installation, along with taking a export of the security accounts etc.

I then that someone from Infrastructure to build a new VM with 2019, but with the same hostname as the 2012 R2 VM that was at this point, shut down. All seemed to go well at this point, where I installed MSSQL 2022, restored the databases and re-imported the security accounts etc. Did all the usual steps of adding the SQL AD service account, adding SCCM Servers AD group to local admins etc. I then went through the restore site process via the Configuration Manager Setup.

Everything seemed to work from this point, Configuration Manager is connecting to the database, nothing strange happening, apart from the MP's regularly complaining with the following:

Management Point encountered an error when connecting to the database CM_XXX on SQL Server XXX-01-scsql-01.cogc.ac.uk. The OLEDB error code was 0x80040E09. The operating system reported error 5: Access is denied. Possible cause: The computer running SQL Server is turned off, not connected to the network, or not functioning properly. Solution: Verify that the SQL Server is turned on, connected to the network, and functioning properly. Possible cause: Management point does not have sufficient access rights to the database. Solution: Verify that management point computer account or the Management Point Database Connection Account is a member of Management Point Role (smsdbrole_MP) in the SQL Server database. Possible cause: Network problems are preventing the management point from properly accessing the SQL Server. Solution: Investigate and correct any problems with your network. Possible cause: The management point having problems is at a secondary site, and SQL access account being used was recently reset by the parent site. Solution: This can automatically repair itself after one hour , as long as the account in use is not locked out. Check that the account in use is not locked out. Instead of waiting an hour, you can stop and start the SMS_SITE_COMPONENT_MANAGER service to immediately apply the new configuration. Possible cause: The SQL Server Service Principal Names (SPNs) are not registered correctly in Active Directory Solution: Ensure SQL Server SPNs are correctly registered. Review Q829868.

I can confirm that the management point computer names are listed under security users for the Configuration Manager database and they have the smsdbrole_MP role. I have also checked the SPN's and they appear to be set correctly, I even deleted them and re-added encase it was holding onto something from the previous install.

I have a feeling that something strange is happening somewhere because I used the same hostname from the Server 2012 R2 VM, for the new database server. The old server is turned off, so there is only one machine with this hostname. I also carried across the same IP address too, so this won't be a DNS resolution problem.

Any advice on this would be greatly appreciated. I also tried re-installing the management points but the same warning is repeatedly appearing in the log.

Cheers,
Richard

 

[Vignezh]

Is the above issue resolved ? I have exactly same issue

 

[sangie15]

Did anyone ever fix this issue? We are having the exact same issue.

 

[MarkusSacramento]

Anyone with status regarding the issue?We are having same issue after upgrading to 2303 on Server 2016 so the issue seems to be related to SCCM upgrade.

 

[Orion2672]

We just upgraded last night to 2309, reviewing the logs I have the same issue but its been there for a couple months at least and we were on 2207. Will have to open an incident with Microsoft to see if they can give me any insight.

 

[mastayoda77]

We just upgraded last night to 2309, reviewing the logs I have the same issue but its been there for a couple months at least and we were on 2207. Will have to open an incident with Microsoft to see if they can give me any insight.

We are also seeing this behavior for at least the past several months. It doesn't just seem to be the MPs either, at about the same time, I have seen other servers complain about access to the sql server including operations on the site server itself like WSUS, REST, and others. Seems to always occur overnight, not really seeing it during the day. I have a request in to our sql admins for them to investigate, but I am awaiting word back from them.

Has anyone else found a solution or source?

One concern I have is that we were recently forced to encrypt our sql connections and I have found little to no mentions of what possible impacts this would have on the MS side of things. Not like their documentation is the best, but it would be nice to find SOMETHING one way or the other. For those of you seeing this, are your sql connections also encrypted?

 

[daveg]

We are also seeing this behavior for at least the past several months. It doesn't just seem to be the MPs either, at about the same time, I have seen other servers complain about access to the sql server including operations on the site server itself like WSUS, REST, and others. Seems to always occur overnight, not really seeing it during the day. I have a request in to our sql admins for them to investigate, but I am awaiting word back from them.

Has anyone else found a solution or source?

One concern I have is that we were recently forced to encrypt our sql connections and I have found little to no mentions of what possible impacts this would have on the MS side of things. Not like their documentation is the best, but it would be nice to find SOMETHING one way or the other. For those of you seeing this, are your sql connections also encrypted?

I just ran into this issue myself upgrading to 2303. I grabbed the sql server's certificate and tried importing it and letting the wizard try to figure it out. When that didn't work, I stuck in the trusted root container and restarted the SMS_EXECUTIVE service on the affected MP and they are talking to sql again. Hope this helps!