PENDING SCCM 2016 Errors - Database Issues?

[surfrock66]

I have a weird situation and am completely stumped as to a resolution. To start with, my organization had their root CA cert expire on Monday and we had to recover from that over the week. It appears we are back in order, a new CA was stood up and all is well, all existing certs have been reissued.

This is a fairly new SCCM 2016 environment on a Server 2016 box.

In SCCM, I went to enter the new PKI client certificate, but when I go to save I get an error. It is titled "The SMS Provider reported an error" and the details are as follows:

ConfigMgr Error Object:
instance of SMS_ExtendedStatus
{
Description = "Failed to update item([\"Display=\\\\SEIU-SCCM-02.AD.SEIU1000.org\\\"]MSWNET:[\"SMS_SITE=S1K\"]\\\\SEIU-SCCM-02.AD.SEIU1000.org\\,SMS Distribution Point)";
ErrorCode = 2278686723;
File = "e:\\cm1702_rtm\\sms\\siteserver\\sdk_provider\\smsprov\\sspsitesettingitem.cpp";
Line = 296;
Operation = "PutInstance";
ParameterInfo = "";
ProviderName = "ExtnProv";
SQLMessage = "[42000][8181][Microsoft][SQL Server Native Client 11.0][SQL Server]Text for 'SMSDBMON_SiteControl_SiteControl_AddUpd_HMAN_upd' is missing from the system catalog. The object must be dropped and re-created before it can be used. : SMSDBMON_SiteControl_SiteControl_AddUpd_HMAN_upd";
SQLSeverity = 16;
SQLStatus = 8181;
StatusCode = 2147749889;
};

-------------------------------
Microsoft.ConfigurationManagement.ManagementProvider.WqlQueryEngine.WqlQueryException
The SMS Provider reported an error.


Stack Trace:
at Microsoft.ConfigurationManagement.ManagementProvider.WqlQueryEngine.WqlResultObject.Put(ReportProgress progressReport)
at Microsoft.ConfigurationManagement.ManagementProvider.WqlQueryEngine.WqlResultObject.Put()
at Microsoft.ConfigurationManagement.AdminConsole.NotifyingResultObject.Put()
at Microsoft.ConfigurationManagement.AdminConsole.SmsDialogData.Put(IResultObject resultObject, List`1 resultObjectsPut, Boolean retainLock)
at Microsoft.ConfigurationManagement.AdminConsole.SmsDialogData.Put(Boolean retainLock)
at Microsoft.ConfigurationManagement.AdminConsole.DialogFramework.Forms.SmsPropertySheet.Put(ActionTrigger trigger)

-------------------------------

System.Management.ManagementException
Generic failure


Stack Trace:
at Microsoft.ConfigurationManagement.ManagementProvider.WqlQueryEngine.WqlResultObject.Put(ReportProgress progressReport)
at Microsoft.ConfigurationManagement.ManagementProvider.WqlQueryEngine.WqlResultObject.Put()
at Microsoft.ConfigurationManagement.AdminConsole.NotifyingResultObject.Put()
at Microsoft.ConfigurationManagement.AdminConsole.SmsDialogData.Put(IResultObject resultObject, List`1 resultObjectsPut, Boolean retainLock)
at Microsoft.ConfigurationManagement.AdminConsole.SmsDialogData.Put(Boolean retainLock)
at Microsoft.ConfigurationManagement.AdminConsole.DialogFramework.Forms.SmsPropertySheet.Put(ActionTrigger trigger)

-------------------------------

I get this error when making ANY changes to the site or distribution point (they're the same machine). I can do things like edit task sequences and collections without issue. I'm also noticing that since the root CA debacle, none of my clients are checking in...I have a new SCCM Client Certificate issued through GPO from the new CA, but I want to troubleshoot the issues with my SCCM server first...I'm worried I have an issue with writing to my database.

Here's a link to the relevant smsprov.log entry, I'll summarize the important bits below:

https://pastebin.com/6F1j06rc

• *** [42000][8181][Microsoft][SQL Server Native Client 11.0][SQL Server]Text for 'SMSDBMON_SiteControl_SiteControl_AddUpd_HMAN_upd' is missing from the system catalog. The object must be dropped and re-created before it can be used. : SMSDBMON_SiteControl_SiteControl_AddUpd_HMAN_upd $$<SMS Provider><06-01-2017 21:33:29.474+420><thread=12292 (0x3004)>
• ~*~*~[42000][8181][Microsoft][SQL Server Native Client 11.0][SQL Server]Text for 'SMSDBMON_SiteControl_SiteControl_AddUpd_HMAN_upd' is missing from the system catalog. The object must be dropped and re-created before it can be used. : SMSDBMON_SiteControl_SiteControl_AddUpd_HMAN_upd ThreadID : 12292 , DbError: 8181 , Sev: 16~*~*~ $$<SMS Provider><06-01-2017 21:33:29.475+420><thread=12292 (0x3004)>
• CSCItem_Base_Source::UpdateItem: Failed to save item (["Display=\\SEIU-SCCM-02.AD.SEIU1000.org\"]MSWNET:["SMS_SITE=S1K"]\\SEIU-SCCM-02.AD.SEIU1000.org\,SMS Distribution Point) properties $$<SMS Provider><06-01-2017 21:33:29.475+420><thread=12292 (0x3004)>
• SspGenericSiteSettingItem::UpdateReal: Failed to update item (["Display=\\SEIU-SCCM-02.AD.SEIU1000.org\"]MSWNET:["SMS_SITE=S1K"]\\SEIU-SCCM-02.AD.SEIU1000.org\,SMS Distribution Point) to database $$<SMS Provider><06-01-2017 21:33:29.475+420><thread=12292 (0x3004)>
• ~*~*~e:\cm1702_rtm\sms\siteserver\sdk_provider\smsprov\sspsitesettingitem.cpp(296) : Failed to update item(["Display=\\SEIU-SCCM-02.AD.SEIU1000.org\"]MSWNET:["SMS_SITE=S1K"]\\SEIU-SCCM-02.AD.SEIU1000.org\,SMS Distribution Point)~ SQL Error: [42000][8181][Microsoft][SQL Server Native Client 11.0][SQL Server]Text for 'SMSDBMON_SiteControl_SiteControl_AddUpd_HMAN_upd' is missing from the system catalog. The object must be dropped and re-created before it can be used. : SMSDBMON_SiteControl_SiteControl_AddUpd_HMAN_upd~*~*~ $$<SMS Provider><06-01-2017 21:33:29.475+420><thread=12292 (0x3004)>
• ~*~*~Failed to update item(["Display=\\SEIU-SCCM-02.AD.SEIU1000.org\"]MSWNET:["SMS_SITE=S1K"]\\SEIU-SCCM-02.AD.SEIU1000.org\,SMS Distribution Point) [42000][8181][Microsoft][SQL Server Native Client 11.0][SQL Server]Text for 'SMSDBMON_SiteControl_SiteControl_AddUpd_HMAN_upd' is missing from the system catalog. The object must be dropped and re-created before it can be used. : SMSDBMON_SiteControl_SiteControl_AddUpd_HMAN_upd~*~*~ $$<SMS Provider><06-01-2017 21:33:29.476+420><thread=12292 (0x3004)>

 

[Prajwal Desai]

Is it just SQL 2016 or SQL 2016 with SP1 ?.

 

[surfrock66]

It shows SQL 2016 in P&F, but it's fully patched with all available updates. SQL Server version 13.0.4202.2.

 

[surfrock66]

Some more details:

I have backups, but they're before the 5/29 cert thing, so I assume if I restore I'll just be in the same boat.

All certs on the box re active with valid expiration dates. All have been configured with the exception of the workgroup client certificate, which I was trying to install during this mess.

I am starting to see some error log events in the MSSQLSERVER source with the following text: Attempt to fetch logical page (1:558793) in database 5 failed. It belongs to allocation unit 72057594060865536 not to 72057594252296192.

I am increasingly worried about database corruption, but the only correlating event is the cert expiration.

 

[surfrock66]

Additional data. I'm getting the following in the error log:

On 6/2/2017 8:07:42 PM, component SMS_SRS_REPORTING_POINT on computer SEIU-SCCM-02.AD.SEIU1000.ORG reported: The report server service is not running on Reporting Service Point server "SEIU-SCCM-02.AD.SEIU1000.org"; start the service to enable reporting.

When I go into srsrp.log, I get the following error over and over:

Reporting Services URL from Registry [http://seiu-sccm-02/ReportServer/ReportService2005.asmx]~ $$<SMS_SRS_REPORTING_POINT><06-02-2017 21:51:45.451+420><thread=7184 (0x1C10)>
System.Web.Services.Protocols.SoapException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. ---> Microsoft.ReportingServices.Library.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing.~ at Microsoft.ReportingServices.Library.Global.EnsureModeFromCatalog()~ at Microsoft.ReportingServices.Library.Global.EnsureRSNativeMode()~ at Microsoft.ReportingServices.WebServer.ReportingService2005..ctor() $$<SMS_SRS_REPORTING_POINT><06-02-2017 21:51:55.471+420><thread=7184 (0x1C10)>
(!) SRS not detected as running $$<SMS_SRS_REPORTING_POINT><06-02-2017 21:51:55.471+420><thread=7184 (0x1C10)>
Failures reported during periodic health check by the SRS Server SEIU-SCCM-02.AD.SEIU1000.org.~ $$<SMS_SRS_REPORTING_POINT><06-02-2017 21:51:55.471+420><thread=7184 (0x1C10)>
Registry change~ $$<SMS_SRS_REPORTING_POINT><06-02-2017 21:51:55.471+420><thread=7184 (0x1C10)>
~Waiting for changes for 1 minutes $$<SMS_SRS_REPORTING_POINT><06-02-2017 21:51:55.472+420><thread=7184 (0x1C10)>
Timed Out...~ $$<SMS_SRS_REPORTING_POINT><06-02-2017 21:52:55.473+420><thread=7184 (0x1C10)>
Set configuration~ $$<SMS_SRS_REPORTING_POINT><06-02-2017 21:52:55.473+420><thread=7184 (0x1C10)>
Check state~ $$<SMS_SRS_REPORTING_POINT><06-02-2017 21:52:55.473+420><thread=7184 (0x1C10)>
Check server health.~ $$<SMS_SRS_REPORTING_POINT><06-02-2017 21:52:55.473+420><thread=7184 (0x1C10)>
Successfully created srsserver~ $$<SMS_SRS_REPORTING_POINT><06-02-2017 21:52:55.473+420><thread=7184 (0x1C10)>

I think the cert thing screwed up DB communication, and I'm not sure how to fix it.

 

[Prajwal Desai]

If you ask me I wouldn't worry about reporting services point role as it can be configured later. This issue is really weird and I’ve never seen this before.


You could try the below steps but make sure you have a good backup and then try :-

Navigate to database\Tables\SiteControl
Expand triggers
Right click on that trigger and select drop and recreate to new query window
Execute the query

 

[surfrock66]

I actually went back and restored a BackupExec backup from 2 days before the cert issue...my system is still broken. I'm getting a ton of MSSQLSERVER errors, for example:

System.IO.FileLoadException: Could not load file or assembly 'cryptoutility, Version=5.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)

Also, I'm getting told to run DBCC CHECKDB. Lastly, I'm getting DistributedCOM errors; all kinds of CLSID and APPID's are getting incorrect security permissions errors.

Following the restore, I'm adding the new root CA and then registering all associated client certs for the server, but something else seems to be wrong...would "NT AUTHORITY/SYSTEM" be impacted by the cert issue, like did the SID change or something?

 

[surfrock66]

So I've done a ton of work on this, but need some help.

I've got my DB working, but I have a broken trigger and I think it's truly corrupted. I'm wondering if someone could look on their SCCM server (pref 2016, but I'll take 2012) and share the contents of the following trigger:

CM_Site -> Tables -> dbo.SiteControl -> Triggers -> SMSDBMON_SiteControl_SiteControl_AddUpd_HMAN_upd

All the other ones work in there, but that one gives me this message when I click modify:

TITLE: Microsoft SQL Server Management Studio
------------------------------

The Trigger '[SMSDBMON_SiteControl_SiteControl_AddUpd_HMAN_upd]' cannot be scripted as its data is not accessible. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?Prod...tes.FailedOperationExceptionText&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

Property TextHeader is not available for Trigger '[SMSDBMON_SiteControl_SiteControl_AddUpd_HMAN_upd]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?Prod...edExceptionText&EvtID=TextHeader&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------