Invoke-DbaDbTransfer - Certificate Chain Not Trusted

[mattcargile]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

Exception             : 
    Type           : System.Exception
    Message        : The certificate chain was issued by an authority that is not trusted.
    InnerException : 
        Type               : Microsoft.Data.SqlClient.SqlException
        Errors             : 
            Source  : Core Microsoft SqlClient Data Provider
            Number  : -2146893019
            Class   : 20
            Server  : destinationsql\sql2017
            Message : 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.)
        ClientConnectionId : 942901c0-3352-4901-b83f-89c46218d1ef
        Class              : 20
        Number             : -2146893019
        Server             : destinationsql\sql2017
        Source             : Core Microsoft SqlClient Data Provider
        ErrorCode          : -2146232060
        TargetSite         : 
            Name          : ThrowExceptionAndWarning
            DeclaringType : [Microsoft.Data.SqlClient.TdsParser]
            MemberType    : Method
            Module        : Microsoft.Data.SqlClient.dll
        Message            : 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.)
        Data               : System.Collections.ListDictionaryInternal
        InnerException     : 
            Type            : System.ComponentModel.Win32Exception
            NativeErrorCode : -2146893019
            ErrorCode       : -2147467259
            Message         : The certificate chain was issued by an authority that is not trusted.
            HResult         : -2147467259
        HResult            : -2146232060
        StackTrace         : 
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParserStateObject.SNIWritePacket(PacketHandle packet, UInt32& sniError, Boolean canAccumulate, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParserStateObject.WriteSni(Boolean canAccumulate)
   at Microsoft.Data.SqlClient.TdsParserStateObject.WritePacket(Byte flushMode, Boolean canAccumulate)
   at Microsoft.Data.SqlClient.TdsParser.TdsLogin(SqlLogin rec, FeatureExtension requestedFeatures, SessionData recoverySessionData, FederatedAuthenticationFeatureExtensionData fedAuthFeatureExtensionData, SqlConnectionEncryptOption encrypt)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool)
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
   at Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
    HResult        : -2146233088
CategoryInfo          : NotSpecified: (:) [Write-Error], Exception
FullyQualifiedErrorId : dbatools_Invoke-DbaDbTransfer,Stop-Function
ErrorDetails          : The certificate chain was issued by an authority that is not trusted.
InvocationInfo        : 
    MyCommand        : Stop-Function
    ScriptLineNumber : 51351
    OffsetInLine     : 17
    HistoryId        : 12
    Line             : Stop-Function -ErrorRecord $_ -Message "Transfer failed"
                       
    Statement        : Stop-Function -ErrorRecord $_ -Message "Transfer failed"
    PositionMessage  : At line:51351 char:17
                       + …              Stop-Function -ErrorRecord $_ -Message "Transfer failed"
                       +                ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    InvocationName   : Stop-Function
    CommandOrigin    : Internal
ScriptStackTrace      : at Stop-Function, <No file>: line 97987
                        at Invoke-DbaDbTransfer<Process>, <No file>: line 51351
                        at <ScriptBlock>, <No file>: line 1
PipelineIterationInfo : 
      0
      1

Steps to Reproduce

$src = 'sourcesql'
$dest = 'destinationsql'
$db = 'trnf'
set-dbatoolsInsecureConnection -sessionOnly
new-dbadatabase $src,$dest -Database $db -Owner sa
invoke-dbaquery $src -Database $db -Query 'create or alter procedure dbo.p as select 2 as c;'
invoke-dbaquery $dest -Database $db -Query 'create or alter procedure dbo.p as select 1 as c;'
$t = New-DbaDbTransfer -SqlInstance $src -DestinationSqlInstance $dest -Database $db -DestinationDatabase $db -CopyAll StoredProcedures
$t | invoke-dbadbtransfer

Please confirm that you are running the most recent version of dbatools

2.1.24

Other details or mentions

I tried passing in connect-dbainstance -trustservercertificate -encryptconnection:$false as well into New-DbaDbTransfer. Additionally, I tried $t.TransferData() and received the same error.

It might have been because sourcesql was forcing encryption and had a set and destinationsql did not have it. Might need a test with two instances without an certs or forced encryption.

What PowerShell host was used when producing this error

PowerShell Core (pwsh.exe)

PowerShell Host Version

Name                           Value
----                           -----
PSVersion                      7.4.5
PSEdition                      Core
GitCommitId                    7.4.5
OS                             Microsoft Windows 10.0.19045
Platform                       Win32NT
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
WSManStackVersion              3.0

SQL Server Edition and Build number

Microsoft SQL Server 2017 (RTM-CU20) (KB4541283) - 14.0.3294.2 (X64) 
	Mar 13 2020 14:53:45 
	Copyright (C) 2017 Microsoft Corporation
	Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

.NET Framework Version

.NET 8.0.8