This is a brief post about fixing the “certificate chain was issued by an authority that is not trusted” when using dbatools in PowerShell.
Example of the full error message:
Error occurred while establishing connection to LOCALHOST\VSQL2019 | The certificate chain was issued by an authority that is not trusted.
If you’re looking for a way to fix this error using newer versions of sqlcmd on Linux, check out this post.
Or, if you’ve switched to SQL Server 2025 and ran into this error, check out this post.
Context
I was just trying to refresh in my environment the stored procedures from Brent Ozar’s First Responder Kit, and I figured I might as well do it the fast way, through dbatools, and got the following error.
Cause
According to the Database Connectivity and Authentication documentation, this happens because the new client drivers assume encryption to be ON by default and, as a result, the driver tries to validate the server’s certificate.
If a self-signed certificate is used instead of a proper CA-signed one, or TLS encryption was never properly configured, any connection attempt using a client driver that assumes encryption to be on will result in that error.
Fix for “certificate chain authority not trusted” in dbatools
For this connection only
Since in this case I don’t have encrypted connection configured for SQL Server, the solution is to just tell dbatools to trust the server’s certificate.
I create a connection to my instance using Connect-DbaInstance with the -TrustServerCertificate switch and load it into a variable that I will later pass to the Install-DbaFirstResponderKit.
| 1 2 3 | $MyConnection = Connect-DbaInstance -SqlInstance LOCALHOST\VSQL2019 -TrustServerCertificate Install-DbaFirstResponderKit -SqlInstance $MyConnection -Database DBATools ` -OnlyScript Install-Core-Blitz-With-Query-Store.sql |
For the current PS session
This is perfect when connecting to multiple instances that don’t use encrypted connections, but you don’t want to permanently overwrite the new client defaults
For older versions of dbatools (but works with new version too)
- To trust self-signed server certificate:
| 1 | Set-DbatoolsConfig -FullName sql.connection.trustcert -Value $true |
- To set encryption to false altogether:
| 1 | Set-DbatoolsConfig -FullName sql.connection.encrypt -Value $false |
For dbatools v 2 and above
| 1 | Set-DbatoolsInsecureConnection -SessionOnly |
The permanent fix
Obviously, the ideal permanent fix is to properly configure TLS with a trusted certificate.
Otherwise, if you’re ok with not using encrypted connections or using the server certificate you have the following options:
For older versions of dbatools (but works with new version too)
- To trust self-signed server certificate:
| 1 | Set-DbatoolsConfig -FullName sql.connection.trustcert -Value $true -Register |
- To set encryption to false altogether:
| 1 | Set-DbatoolsConfig -FullName sql.connection.encrypt -Value $false -Register |
For dbatools v 2 and above
| 1 | Set-DbatoolsInsecureConnection |
Conclusion
That’s it, that’s the post. Not everyone has configured SQL Server for encrypted connections, and this should help avoid constantly running into the “certificate chain authority not trusted” error in dbatools.
4 comments
Hello I am using below code to sync the SQL agent jobs from primary to secondary nodes in the SQL AG cluster servers. But I am not able to resolve the certificate chain was issued by an authority that is not trusted issue. I am using DBATOOLS.
Can you please help me to fix this error message?
Code:
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names+’,’, ”) +””+ JobName +”” FROM dbo.sdct_DBAU_DbaJobSyncSkip
DECLARE @primaryNode VARCHAR(100)
SET @primaryNode = (SELECT nodename FROM dbo.sdct_DBAU_DbaJobSyncNodes WHERE nodename = (SELECT @@SERVERNAME))
DECLARE @DisasterNode VARCHAR(100)
SET @DisasterNode = (SELECT nodename FROM dbo.sdct_DBAU_DbaJobSyncNodes WHERE nodename @primarynode AND DisasterNode = 1)
–DECLARE @MyConnection VARCHAR(1000)
–SET @MyConnection =(SELECT ‘powershell.exe -command “Connect-DbaInstance -SqlInstance @primaryNode -TrustServerCertificate”‘)
–EXEC xp_cmdshell @MyConnection
DECLARE @command_ps VARCHAR(8000)
SET @command_ps = (SELECT ‘powershell.exe -command “Copy-DbaAgentJob -Source ‘ + @primaryNode + ‘ -Destination ‘ + @DisasterNode + ‘ -excludejob ‘+ @Names +’ -Force”‘)
–SELECT @command_ps
–EXEC xp_cmdshell @command_ps
CREATE TABLE #temp_trb (logtext VARCHAR(8000))
INSERT INTO #temp_trb EXEC xp_cmdshell @command_ps
INSERT INTO dbo.sdct_DBAU_DbaJobSyncLog
SELECT *,CONVERT(DATE,GETDATE()) FROM #temp_trb
DROP TABLE #temp_trb
DELETE FROM dbo.sdct_DBAU_DbaJobSyncLog WHERE PSOutput IS NULL
DELETE FROM dbo.sdct_DBAU_DbaJobSyncLog WHERE dateran < DATEADD(DAY, -90,GETDATE())
ERROR Message:
03:16:03dbatools.psm1]
/ / / /
| O | | O |
| |- – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – -| |
| O | | O |
| | | |
| O | | O |
| | C O M P U T E R | |
| O | | O |
| | M E S S A G E | |
| O | | O |
| | | |
| O |- – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – -| O |
| | | |
Microsoft changed the encryption defaults in their SqlClient library, which may
cause your connections to fail.
You can change the defaults with Set-DbatoolsConfig but dbatools also makes it
easy to setup encryption. Check out dbatools.io/newdefaults for more information.
To disable this message, run:
Set-DbatoolsConfig -Name Import.EncryptionMessageCheck -Value $false -PassThru |
Register-DbatoolsConfig
WARNING: [03:16:04][Get-DbaAgentJob] Failure | The certificate chain was issued by an authority that is not trusted
WARNING: [03:16:04][Copy-DbaAgentJob] Failure | The certificate chain was issued by an authority that is not trusted
Hello,
The issue with your command is that you’re not really using the connection you’ve defined in this line
–SET @MyConnection =(SELECT ‘powershell.exe -command “Connect-DbaInstance -SqlInstance @primaryNode -TrustServerCertificate”‘)you should load that into an object (see my example with $MyConnection which you should then pass to the next line
SET @command_ps = (SELECT ‘powershell.exe -command “Copy-DbaAgentJob -Source ‘ + @primaryNode + ‘ -Destination ‘ + @DisasterNode + ‘ -excludejob ‘+ @Names +’ -Force”‘)Since they’re both independent PS commands, and I’m fairly certain they don’t share the same execution context, you’ll have to look at a way to have all in one command
something like
SET @command_ps = (SELECT ‘powershell.exe -command “$PrimaryNode = Connect-DbaInstance -SqlInstance @primaryNode -TrustServerCertificate; $DisasterNode = Connect-DbaInstance -SqlInstance @DisasterNode -TrustServerCertificate; Copy-DbaAgentJob -Source $PrimaryNod -Destination $DisasterNode -excludejob ‘+ @Names +’ -Force”‘)Or, if you want to simplify things, just RDP into the host of the instance where you’re running that code on, and run this in PS opened as admin:
Set-DbatoolsInsecureConnectionThanks for posting this!
I now have it bookmarked after multiple times of having to deal with this
Vlad…I spent hours trying to track this down as I have not used dbatools/dbachecks for a long time…thanks for figuring this out, oh security!