WSUS DB Optimization Fails

[amurbain]

Hello!

When running the script with -FirstRun flag and running the optimizations, I'm getting the following error when it approaches the WSUS database optimizations:

Creating custom indexes in WSUS index if they don't already exist. This will speed up future database optimizations.
Invoke-Sqlcmd : A connection was successfully established with the server, but then an error occurred during the login
process. (provider: SSL Provider, error: 0 - The target principal name is incorrect.)
At C:\Scripts\Optimize-WsusServer-master\Optimize-WsusServer.ps1:440 char:5
+     Invoke-Sqlcmd -query $createCustomIndexesSQLQuery -ServerInstance ...
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException
    + FullyQualifiedErrorId : SqlExceptionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Invoke-Sqlcmd : Incorrect syntax was encountered while parsing 'IF 0 = (SELECT COUNT(*) as index_count'.
At C:\Scripts\Optimize-WsusServer-master\Optimize-WsusServer.ps1:440 char:5
+     Invoke-Sqlcmd -query $createCustomIndexesSQLQuery -ServerInstance ...
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ParserError: (:) [Invoke-Sqlcmd], BatchParserException
    + FullyQualifiedErrorId : ExecutionFailureException,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Running WSUS SQL database maintenence script. This can take an extremely long time on the first run.
Invoke-Sqlcmd : A connection was successfully established with the server, but then an error occurred during the login
process. (provider: SSL Provider, error: 0 - The target principal name is incorrect.)
At C:\Scripts\Optimize-WsusServer-master\Optimize-WsusServer.ps1:444 char:5
+     Invoke-Sqlcmd -query $wsusDBMaintenanceSQLQuery -ServerInstance $ ...
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException
    + FullyQualifiedErrorId : SqlExceptionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Invoke-Sqlcmd : Incorrect syntax was encountered while parsing 'SET NOCOUNT ON;'.
At C:\Scripts\Optimize-WsusServer-master\Optimize-WsusServer.ps1:444 char:5
+     Invoke-Sqlcmd -query $wsusDBMaintenanceSQLQuery -ServerInstance $ ...
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ParserError: (:) [Invoke-Sqlcmd], BatchParserException
    + FullyQualifiedErrorId : ExecutionFailureException,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Not sure what to do here. Thoughts?

[solskogen]

There's a pull request for this problem. Take a look at #19 for the solution.

[TheBootsman]

Add -TrustServerCertificate to the end of lines 440 and 444

Invoke-Sqlcmd -query $createCustomIndexesSQLQuery -ServerInstance $serverInstance -QueryTimeout 120 -TrustServerCertificate