Cannot Restore-BcDatabaseFromArtifacts because of SSL Provider issue

[mkilinskidev]

Hi. I can't restore a database from artifacts on my external SQL because of issue "SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted".

Regarding this issue: microsoft/SQLServerPSModule#35 there is a switch -TrustServerCertificate in the SQLServer module. Can you add that switch to the Restore-BcDatabaseFromArtifacts script?

PS C:\Users\mkilinski> Restore-BcDatabaseFromArtifacts -artifactUrl (Get-BCArtifactUrl -type OnPrem -country w1 -version 24 -select Latest) -databaseServer SRV-SQL-01 -databasePrefix 'MKI-' -databaseName 'TEST'
Starting Database Restore job from https://bcartifacts-exdbf9fwegejdqak.b02.azurefd.net/onprem/24.2.20227.20424/w1
C:\Program Files\WindowsPowerShell\Modules\BcContainerHelper\6.0.15\Import-BcContainerHelper.ps1
BcContainerHelper version 6.0.15
BC.HelperFunctions emits usage statistics telemetry to Microsoft
Running on Windows, PowerShell 5.1.22621.2506
Downloading Artifacts https://bcartifacts-exdbf9fwegejdqak.b02.azurefd.net/onprem/24.2.20227.20424/w1
Importing PowerShell module C:\bcartifacts.cache\onprem\24.2.20227.20424\platform\ServiceTier\program files\Microsoft Dynamics NAV\240\Service\Management\Microsoft.Dynamics.Nav.Management.dll
Restore-BcDatabaseFromArtifacts Telemetry Correlation Id: a3c22923-895d-4b9f-8b3e-0c7e4e93c71d
A connection was successfully established with the server, but then an error occurred during the login process. (provid
er: SSL Provider, error: 0 - Łańcuch certyfikatów został wystawiony przez urząd, którego nie jest zaufany.)
At C:\Program Files\WindowsPowerShell\Modules\BcContainerHelper\6.0.15\Bacpac\Restore-BcDatabaseFromArtifacts.ps1:222 c
har:13
+             $job | Receive-Job
+             ~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException
    + FullyQualifiedErrorId : SqlExceptionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
    + PSComputerName        : localhost

[mkilinskidev]

The same situation when I am trying to create a new BC container with external SQL.

BcContainerHelper is version 6.0.18
BcContainerHelper is running as administrator
HyperV is Enabled
Host is Microsoft Windows Server 2022 Standard - 10.0.20348.2461
UsePsSession is True
UsePwshForBc24 is True
UseWinRmSession is allow
UseSslForWinRmSession is True
Docker Client Version is 26.1.4
Docker Server Version is 26.1.4
Removing Desktop shortcuts
New-BcContainer Telemetry Correlation Id: 5b18c24e-bb65-48cb-9260-bbb198da507d
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 certificate chain was issued by an authority that is not trusted.)
At C:\Program Files\WindowsPowerShell\Modules\BCContainerHelper\6.0.18\Bacpac\Remove-BcDatabase.ps1:41 char:16
+     $dbFiles = Invoke-SqlCmd `
+                ~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException
    + FullyQualifiedErrorId : SqlExceptionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

[freddydk]

Full script (that I can run and repro the problem) + full output please.
Thanks

[mkilinskidev]

Sorry, I thought my first comment was enough :)
My SQL Server is reachable from my computer and the version is 16.0.4105.2

Script to create a new BC Container

PS C:\Admin> New-BcContainer -accept_eula -containerName test -Credential (New-Object pscredential 'dev', (ConvertTo-SecureString 'Logon2me' -AsPlainText -Force)) -auth NavUserPassword -artifactUrl (Get-BCArtifactUrl -type OnPrem -country w1 -version 24.1 -select Latest) -isolation process -updateHosts -shortcuts None -doNotCheckHealth -databaseServer srv-sql-1 -databaseCredential (Get-Credential) -databasePrefix 'TEST-' -databaseName 'test' -replaceExternalDatabases -licenseFile C:\Admin\latest.bclicense

Output

cmdlet Get-Credential at command pipeline position 1
Supply values for the following parameters:
Credential
BcContainerHelper is version 6.0.18
BcContainerHelper is running as administrator
HyperV is Enabled
Host is Microsoft Windows Server 2022 Standard - 10.0.20348.2461
UsePsSession is True
UsePwshForBc24 is True
UseWinRmSession is allow
UseSslForWinRmSession is True
Docker Client Version is 26.1.4
Docker Server Version is 26.1.4
Removing Desktop shortcuts
Downloading artifact /onprem/24.1.18927.19498/w1
Downloading C:\Users\m.kilinski\AppData\Local\Temp\6fc771a5-08dc-4cc7-9cfc-475de8e4e443.zip
Unpacking artifact to tmp folder using 7zip
Downloading platform artifact /onprem/24.1.18927.19498/platform
Downloading C:\Users\m.kilinski\AppData\Local\Temp\5aa3527b-47aa-437a-a2e9-c10eb9a7a259.zip
Unpacking artifact to tmp folder using 7zip
Downloading Prerequisite Components
Downloading c:\bcartifacts.cache\onprem\24.1.18927.19498\platform\Prerequisite Components\DotNetCore\DotNetCore.1.0.4_1.1.1-WindowsHosting.exe
Downloading c:\bcartifacts.cache\onprem\24.1.18927.19498\platform\Prerequisite Components\IIS URL Rewrite Module\rewrite_2.0_rtw_x64.msi
New-BcContainer Telemetry Correlation Id: e38f1ab4-c4eb-49e0-a266-5e9433a3a963
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 certificate chain was issued by an authority that is not trusted.)
At C:\Program Files\WindowsPowerShell\Modules\BCContainerHelper\6.0.18\Bacpac\Remove-BcDatabase.ps1:41 char:16
+     $dbFiles = Invoke-SqlCmd `
+                ~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException
    + FullyQualifiedErrorId : SqlExceptionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Another example:

PS C:\Users\mkilinski> Restore-BcDatabaseFromArtifacts -artifactUrl (Get-BCArtifactUrl -type OnPrem -country w1 -version 24 -select Latest) -databaseServer SRV-SQL-1.NMIERP.PL -databasePrefix 'MKI-' -databaseName 'TEST'

Output

BcContainerHelper version 6.0.15
BC.HelperFunctions emits usage statistics telemetry to Microsoft
Running on Windows, PowerShell 5.1.22621.2506
Starting Database Restore job from https://bcartifacts-exdbf9fwegejdqak.b02.azurefd.net/onprem/24.2.20227.20424/w1
C:\Program Files\WindowsPowerShell\Modules\BcContainerHelper\6.0.15\Import-BcContainerHelper.ps1
BcContainerHelper version 6.0.15
BC.HelperFunctions emits usage statistics telemetry to Microsoft
Running on Windows, PowerShell 5.1.22621.2506
Downloading Artifacts https://bcartifacts-exdbf9fwegejdqak.b02.azurefd.net/onprem/24.2.20227.20424/w1
Importing PowerShell module C:\bcartifacts.cache\onprem\24.2.20227.20424\platform\ServiceTier\program files\Microsoft Dynamics NAV\240\Service\Management\Microsoft.Dynamics.Nav.Management.dll
Restore-BcDatabaseFromArtifacts Telemetry Correlation Id: 23d05c7b-dc8f-42ea-b61f-627bc73ef356
A connection was successfully established with the server, but then an error occurred during the login
process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)
At C:\Program Files\WindowsPowerShell\Modules\BcContainerHelper\6.0.15\Bacpac\Restore-BcDatabaseFromArtifacts.ps1:222 c
har:13
+             $job | Receive-Job
+             ~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException
    + FullyQualifiedErrorId : SqlExceptionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
    + PSComputerName        : localhost

I need to use a -TrustServerCertificate switch when the BcContainerHelper communicates with the SQL Server using SqlServer PS module. That's all...

F.ex. here: https://github.com/microsoft/navcontainerhelper/blob/b2410696c2c9ce68b63f3ef1a65c0c09d0c58ed1/Bacpac/Remove-BcDatabase.ps1#L41C16-L44C1, I thin if we add this -TrustServerCertificate switch it will be ok.

When I use Invoke-Sqlcmd command, there is an error

PS C:\Users\mkilinski> Invoke-Sqlcmd -ServerInstance 'SRV-SQL-1' -Query 'select * from sys.sysdatabases'
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 certificate chain was issued by an authority that is not trusted.)
At line:1 char:1
+ Invoke-Sqlcmd -ServerInstance 'SRV-SQL-1' -Query 'select *  ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException
    + FullyQualifiedErrorId : SqlExceptionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

But if I add this switch, it is OK

PS C:\Users\mkilinski> Invoke-Sqlcmd -ServerInstance 'SRV-SQL-1' -Query 'select * from sys.sysdatabases' -TrustServerCertificate


name      : master
dbid      : 1
sid       : {1}
mode      : 0
status    : 65544
status2   : 1090520064
crdate    : 08.04.2003 09:13:36
reserved  : 01.01.1900 00:00:00
category  : 0
cmptlevel : 150
filename  : S:\MSSQL\SystemDBs\MSSQL15.BCSQL\MSSQL\DATA\master.mdf
version   : 957

[freddydk]

What SQL Server are you running on the host?
Does this work if you use BC23?
Does it work if you set $bcContainerHelperConfig.usePwshForBc24 = $false before you run the script?

[mkilinskidev]

SQL Server Standard, version 16.0.4105.2
Same issue after set $bcContainerHelperConfig.usePwshForBc24 = $false.
With BC23 same situation. With SQL2019 (15.00.2000.05) still the same.

[freddydk]

Is your SQL Server installed with a self-signed certificate for SSL communication?
Could you remove that?

[mkilinskidev]

IDK how to check it. If I go to SQL Server Configuration Management, all encryptions etc. are turned off. Going through this article https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-sql-server-encryption?view=sql-server-ver16 I didn't see, if the SQL have some certificates installed. I've checked the system register, even sp_configure T-SQL queries, nothing. I don't know, what can I do more, so why I am asking about add the TrustServerCertificate switch. I can fork the repository and make it by myself and then make a Pull Request.

[freddydk]

The problem is that the current implementation works for everyone else. Any change needs to be tested with various versions.
Feel free to create a pr, which works for you - then we can test

[freddydk]

Looking The reason for this is that you have a newer version of the SqlServer powershell module on your computer.
This obviously have to be supported - but without removing support for the old version of Invoke-SqlCmd.
Feel free to grab the changes from my PR (link above) and test it.

[freddydk]

Fixed in preview