Write-DbaDbTableData in my .ps1 file works if file saved locally but fails when on network location

[geomaxson]

We have been using the TestRestore now successfully for some time. However, we desire to not keep our .ps1 file local to each machine but saved on the network. However since this change, the Write-DbaDbTableData step now fails with "The certificate chain was issued by an authority that is not trusted".
Here are the commands used inside our SQL Agent, CmdExec, step, before and after

powershell.exe -file "C:\Software\TestRestore.ps1" -Instance "abc,1234" -Destination "xyz,5678"

C:\Program Files\PowerShell\7\pwsh.exe -ExecutionPolicy Unrestricted -File "\network\loc\PowerShell\Prod\TestRestore.ps1" -Instance "abc,1234" -Destination "xyz,5678"

Below is the core of the TestRestore.ps1 file

# Restore and test each database on the instance
$Results = Test-DbaLastBackup -SqlInstance $Instance -Destination $Destination -EnableException -IgnoreLogBackup -DataDirectory R:\Restores -LogDirectory R:\Restores -ExcludeDatabase $excludeDb

# Push the results of the test to the dbo.DBA.backuptest table on the Destination instance
$DataTable = ConvertTo-DbaDataTable -InputObject $Results
Write-DbaDataTable -SqlInstance $Destination -Database DBA -Schema dbo -Table backuptest -KeepNulls -InputObject $DataTable -AutoCreateTable -EnableException

Any chance this is why, and is there a possible workaround? SQL 2019
https://learn.microsoft.com/en-us/troubleshoot/sql/connect/certificate-chain-not-trusted?tabs=ole-db-driver-19

[potatoqualitee]

Hey @geomaxson - can you paste the sanitized output of Get-DbatoolsError? I'm wondering if this is an execution policy issue and it has issues with our signed module? Can you test with Set-ExecutionPolicy bypass? Throwing the kitchen sink, you can even just remove the manual data table conversion and let the Write command do it for you.

It's very weird, but if it's on the SQL side, then read here for more info #7680

[geomaxson]

Good Morning Chrissy, and thank you for taking a look at this with me!
Allow me to add, previously when running using local file we used PowerShell 5. To run it with a remote file we are using 7.
I did check the Execution Policy on the box, Unrestricted at the moment, but I also tried the script with Bypass, same results.

Yesterday, all attempts to run from the ISE with ver. 5 ended with the ISE closing. Here is an attempt from pwsh ver. 7 today, on the SQL instance. I was unable to get it to even talk to another box, so I ran it against itself and it is now failing during TestRestore as follows:

PS C:\Software> $Results = Test-DbaLastBackup -SqlInstance "target,123" -Destination "destination,456" -EnableException -IgnoreLogBackup -DataDirectory R:\Restores -LogDirectory R:\Restores -database dba
WARNING: [08:43:26][Get-DbaDatabase] Failure | The certificate chain was issued by an authority that is not trusted.
PS C:\Software> Get-DbatoolsError

CategoryInfo : ConnectionError: (destination,456:PSObject) [Write-Error], Exception
ErrorDetails : The certificate chain was issued by an authority that is not trusted.
Exception : System.Exception: The certificate chain was issued by an authority that is not trusted.
---> Microsoft.Data.SqlClient.SqlException (0x80131904): 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.)
---> System.ComponentModel.Win32Exception (0x80090325): The certificate chain was issued by
an authority that is not trusted.
at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean
breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParserStateObject.ThrowExceptionAndWarning(Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParserStateObject.SNIWritePacket(PacketHandle packet, UInt32& sniError, Boolean canAccumulate, Boolean callerHasConnectionLock) 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) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.Login(ServerInfo server, TimeoutTimer timeout, String newPassword, SecureString newSecurePassword) 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.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal&
connection)
at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection
owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry,
DbConnectionOptions userOptions)
at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry,
SqlConnectionOverrides overrides)
at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
at Microsoft.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnectImpl()
at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect()
at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
ClientConnectionId:a58eb227-5554-4cd6-94fa-33dceddc50e4
Error Number:-2146893019,State:0,Class:20
--- End of inner exception stack trace ---
FullyQualifiedErrorId : dbatools_Get-DbaDatabase,Stop-Function
InvocationInfo : System.Management.Automation.InvocationInfo
PipelineIterationInfo : {0, 1}
PSMessageDetails :
ScriptStackTrace : at Stop-Function, C:\program files\powershell\7\Modules\dbatools\1.1.134\allcommands.ps1: line
148972
at Get-DbaDatabase, C:\program
files\powershell\7\Modules\dbatools\1.1.134\allcommands.ps1: line 34791
at Test-DbaLastBackup, C:\program
files\powershell\7\Modules\dbatools\1.1.134\allcommands.ps1: line 131662
at , : line 1
TargetObject : destination,456

PS C:\Software>

[geomaxson]

I was now able to look at the link you sent, and the error "The target principal name is incorrect" is one I saw this morning. I applied the change recommended and that resolved my issues running it manually against a different target server. I have run the SQL job again with success, after discovering the Table structure changes. I guess we also updated our install of DBATools when we added PowerShell v.7.
It looks like this has resolved the issue!
I will add, that I also tried updating our script parameters with the full server name as recorded on the Cert, and returning the config to false, but still same issue. We are functional again now though because of that help. Guess I failed to search with the right terms when I tried searching the discussions.
Thanks for making it happen with these tools, and your help today to get us back up and running!