Support Tip: WSUS Server Cleanup Wizard task “Delete computers that have not contacted the server in 30 days or more” fails with connection error
~ Author: Moni R S
Hello Everyone, my name is Moni and I am a Support Engineer in the Windows Devices and Deployment team here at Microsoft. In this post, I’ll be discussing an issue where the Server Cleanup Wizard for WSUS 3.0 Service Pack 2 times out when attempting to delete computers that have not contacted the server in 30 days or more.
Background
The following screenshot shows what the WSUS Server Cleanup Wizard looks like in WSUS 3.0 SP2:
We could execute all the tasks except the second one: Delete computers that have not contacted the server in 30 days or more. This task was timing out, resulting in a loss of WSUS console connectivity.
<An Error occurred trying to connect the WSUS server. This error can happen for a number of reasons. Check connectivity with the server. Please contact your network administrator if the problem persists>
Details of the error are as follows:
The WSUS administration console was unable to connect to the WSUS Server Database.
Verify that SQL server is running on the WSUS Server. If the problem persists, try restarting SQL.
System.Data.SqlClient.SqlException -- Access to table dbo.tbDownstreamServerClientSummaryRollup is blocked because the signature is not valid.
Source
.Net SqlClient Data Provider
Stack Trace:
at System.Windows.Forms.Control.MarshaledInvoke(Control caller, Delegate method, Object[] args, Boolean synchronous)
at System.Windows.Forms.Control.Invoke(Delegate method, Object[] args)
at Microsoft.UpdateServices.UI.SnapIn.Wizards.ServerCleanup.ServerCleanupWizard.OnCleanupComplete(Object sender, PerformCleanupCompletedEventArgs e)
It appears that access to the table dbo.tbDownstreamServerClientSummaryRollup is blocked because the signature is not valid, thereby resulting in a timeout while attempting to delete obsolete computers.
Attempting to access this table by executing the following query against the SUSDB:
select * from tbDownstreamServerClientSummaryRollup
It failed with the message below:
Msg 33002, Level 16, State 1, Line 1
Access to table dbo.tbDownstreamServerClientSummaryRollup is blocked because the signature is not valid.
What we can infer:
- Windows Internal Database (WID) was being used to host the SUSDB. WID basically is SQL Server Embedded Edition.
- The table named tbDownstreamServerClientSummaryRollup has an invalid signature and this is because this is not allowed in SQL Server Embedded Edition.
- The trust is broken between WSUS and SQL Embedded edition.
- When a query is executed which tries to access such a table or module with invalid signature it fails.
Fixing this is a laborious task as we must sign the concerned table with a valid signature and re-establish the trust between WSUS and SQL Server Embedded Edition.
This led me to perform the below testing:
Testing / Repro
I built a new WSUS 3.0 SP2 lab test machine and verified the certificate by executing this SQL query against the SUSDB:
select * from sys.certificates
This returned MS_SchemaSigningCertificateD7A4348D8F461363128D655AE4589B8206B74257
There was only one certificate, which comes built in.
I removed the signature for tbDownstreamServerClientSummaryRollup by executing the SQL query below against SUSDB:
DROP SIGNATURE FROM [dbo]. [tbDownstreamServerClientSummaryRollup] BY CERTIFICATE [MS_SchemaSigningCertificateD7A4348D8F461363128D655AE4589B8206B74257]
Next I tried to verify whether we could access the concerned table by executing the following SQL query against the SUSDB:
select * from tbDownstreamServerClientSummaryRollup
We got the following message:
Msg 33002, Level 16, State 1, Line 1
Access to table dbo.tbDownstreamServerClientSummaryRollup is blocked because the signature is not valid.
So this means that we were able to break the signature of this table and thereby reproduce the issue that the customer was facing.
The latest hardening update for WSUS (2938066) includes a new certificate which is [MS_SchemaSigningCertificate77F29F0BD53B7D715AB7B4671A559131C9F9EAF6].
We verified this by downloading the update and extracting its contents: Extract the .exe file -> extract the .MSP -> extract the PCW_CAB_SUS –> open the DbCertSql file in a text editor such as Notepad ++.
The first section in this file will give us details about the certificate that comes with the update. Scrolling down below will give you information about the objects that will be re-signed using this certificate.
If this update is installed, then all the tables should be re-signed and the new certificate should be added.
I installed this update on the test machine and it needed a system restart, but before restarting the machine we verified the certificate by executing the following query:
select * from sys.certificates
The two certificates below were present now.
[MS_SchemaSigningCertificateD7A4348D8F461363128D655AE4589B8206B74257]
[MS_SchemaSigningCertificate77F29F0BD53B7D715AB7B4671A559131C9F9EAF6]
The second one above is the one that comes with the hardening update.
We also ran the query below to check which certificate signed the table [tbDownstreamServerClientSummaryRollup].
SELECT object_name(major_id), cer.name,cp.crypt_property,*
FROM sys.crypt_properties AS cp
JOIN sys.certificates AS cer
ON cp.thumbprint = cer.thumbprint
where object_name(major_id) = 'tbDownstreamServerClientSummaryRollup'
It returned stating that this table is signed by the new certificate which is [MS_SchemaSigningCertificate77F29F0BD53B7D715AB7B4671A559131C9F9EAF6].
Now we executed the query below to see if we could access the table tbDownstreamServerClientSummaryRollup, which we couldn't previously due to an invalid signature.
select * from tbDownstreamServerClientSummaryRollup
It was accessible, and since there weren't any client computers contacting this new WSUS server, it returned stating that there wasn’t any data in it.
In-house testing confirmed that installing the latest hardening update for WSUS will re-sign all of the objects with the new certificate that it brings with it.
Resolution
The issue can be resolved with the following steps:
1. Make a backup of the SUSDB.
- Right click the SUSDB –> Tasks -> Back Up…
2. Install the latest WSUS hardening update KB2938066.
3. Restart the machine when prompted for a restart.
4. Execute the following SQL query to see if table tbDownstreamServerClientSummaryRollup is accessible:
select * from tbDownstreamServerClientSummaryRollup
5. Verify the certificate that signed the table tbDownstreamServerClientSummaryRollup by executing the following SQL query:
SELECT object_name(major_id), cer.name,cp.crypt_property,*
FROM sys.crypt_properties AS cp
JOIN sys.certificates AS cer
ON cp.thumbprint = cer.thumbprint
where object_name(major_id) = 'tbDownstreamServerClientSummaryRollup'
If the table tbDownstreamServerClientSummaryRollup is accessible, try to execute the Server Cleanup Wizard to delete obsolete computers again, and verify whether that operation is executed successfully.
Conclusion
This approach can be used in any case where WSUS related tables in the SUSDB become inaccessible due to an invalid signature. This assumes that we have the option to install a newer WSUS hardening update of course. The key is that each WSUS hardening update comes in with a unique certificate which will re-sign the tables using it.
I hope this helps, and thanks for taking the time to read this post!
Moni R S | Support Engineer | Microsoft Windows Devices and Deployment
Disclaimer: This information is provided ‘as-is’ with no warranties