This is a brief post about fixing the following error when using dbatools.
| 1 2 | Error occurred while establishing connection to LOCALHOST\VSQL2019 | The certificate chain was issued by an authority that is not trusted. |
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.
![PS C:\Users\Vlad> Install-DbaFirstResponderKit -SqlInstance LOCALHOST\VSQL2019 -Database DBATools -OnlyScript Install-Core-Blitz-With-Query-Store.sql WARNING: [21:48:15][Install-DbaFirstResponderKit] Error occurred while establishing connection to LOCALHOST\VSQL2019 | The certificate chain was issued by an authority that is not trusted. SEO certificate chain authority trusted dbatools dbatools the certificate chain was issued by an authority that is not trusted](https://vladdba.com/wp-content/uploads/2023/07/dbatools_certerror.png)
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 and fails.
Fix – 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 |
Fix – for the current PS session
This is perfect when connecting to multiple instances that don’t use encrypted connections, but don’t want to permanently overwrite the new client defaults
For older versions of dbatools
| 1 2 | Set-DbatoolsConfig -FullName sql.connection.trustcert -Value $true Set-DbatoolsConfig -FullName sql.connection.encrypt -Value $false |
For dbatools v 2 and above
| 1 | Set-DbatoolsInsecureConnection -SessionOnly |
Fix – the permanent kind
This is perfect if your environment consists of mostly or all instances not using encrypted connections, and you’re ok with overwriting the new client defaults.
For older versions of dbatools
| 1 2 | Set-DbatoolsConfig -FullName sql.connection.trustcert -Value $true -Register 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 those errors.