So I've had this bug while using Set-RsDatabase on an offline machine.
At first it didn't recognize the Invoke-SqlCmd command because the SqlServer PowerShell module was not installed on the machine, and bacause the machine is offline the ReportingServicesTools couldn't download it itself I guess(?).
After that I manually installed the SqlServer module from a .nupkg file as per microsoft instructed, and I ran into a TLS error so I had to set the -TrustServerCertificate flag.
Then it popped am error that said the current version of Invoke-SqlCmd used does not support -TrustServerCertificate and to consider upgrading the module version to at least version 22.
But that's odd - I just installed the latest version of the SqlServer module, so how is it possible?
I dug a bit into the code of Set-RsDatabase and I found an interersting if-condition that pops this error:
$supportSQLServerV22Parameters = (Get-InstalledModule -Name "SQLServer" -MinimumVersion 22.0 -ErrorAction SilentlyContinue) -ne $null
$containsSQLServerV22Parameters = $PSBoundParameters.ContainsKey("Encrypt") -or $TrustServerCertificate -or $PSBoundParameters.ContainsKey("HostNameInCertificate")
if ($containsSQLServerV22Parameters -and -not $supportSQLServerV22Parameters)
{
throw "The current version of Invoke-Sqlcmd cmdlet used in this script doesn't support -Encrypt, -TrustServerCertificate and -HostNameInCertificate parameters. Consider installing SQLServer module version 22 or higher and restarting PowerShell to use the script with these parameters."
}
So the problem lies in the Get-InstalledModule function. It only shows modules that were installed using Install-Module, ignoring all the other ways it could be installed. When I run Get-InstalledModule I notice it doesn't recognize any module called SqlServer although its clearly there in $env:PSModulePath.
If instead of Get-InstalledModule it was )Get-Module -ListAvailable) it would have worked regardless on how SqlServer was installed, which would make it work smoother on offline SSRS machines.
You can reproduce this steps by using an offline machine and manually installing both ReportingServicesTools and SqlServer PowerShell modules. Then try to use Set-RsDatabase with the -TrustServerCertificate flag.
So I've had this bug while using Set-RsDatabase on an offline machine. At first it didn't recognize the Invoke-SqlCmd command because the SqlServer PowerShell module was not installed on the machine, and bacause the machine is offline the ReportingServicesTools couldn't download it itself I guess(?). After that I manually installed the SqlServer module from a .nupkg file as per microsoft instructed, and I ran into a TLS error so I had to set the -TrustServerCertificate flag.
Then it popped am error that said the current version of Invoke-SqlCmd used does not support -TrustServerCertificate and to consider upgrading the module version to at least version 22.
But that's odd - I just installed the latest version of the SqlServer module, so how is it possible?
I dug a bit into the code of Set-RsDatabase and I found an interersting if-condition that pops this error:
So the problem lies in the Get-InstalledModule function. It only shows modules that were installed using Install-Module, ignoring all the other ways it could be installed. When I run Get-InstalledModule I notice it doesn't recognize any module called SqlServer although its clearly there in $env:PSModulePath. If instead of Get-InstalledModule it was )Get-Module -ListAvailable) it would have worked regardless on how SqlServer was installed, which would make it work smoother on offline SSRS machines.
You can reproduce this steps by using an offline machine and manually installing both ReportingServicesTools and SqlServer PowerShell modules. Then try to use Set-RsDatabase with the -TrustServerCertificate flag.