[Fixed] Powershell – The certificate chain was issued by an authority that is not trusted (dbatools) – Powershell

The Problem:

When executing dbatools scripts, various commands encounter a warning stating: ‘The certificate chain was issued by an authority that is not trusted’. Despite attempts to set ‘TrustServerCertificate=True’ in the SQL Server connection string, the issue persists. A PowerShell-specific solution is sought to resolve this warning.

The Solutions:

Solution 1: Bypass the SQL Server Certificate Check

To avoid certificate trust issues when using dbatools scripts, you can disable the SQL data file check by using the -NoSqlCheck parameter. However, it’s recommended to obtain a valid certificate for your SQL Server instance to ensure security.

Solution 2: Force Trusting Untrusted Certificates

If you wish to connect to SQL Server despite an untrusted certificate, you can force PowerShell to trust it. However, this approach presents significant security risks. You can use the following code snippet to force certificate trust:

“`powershell
$server = Connect-DbaInstance `
-SqlInstance 'yourMachine.domain.com' `
-Database 'YourDb' `
-TrustServerCertificate;
# add credentials using -SqlCredential

Backup-DbaDatabase -SqlInstance $server…..

Solution 2: DbaToolsConfig Settings

If you want to keep your current version of dbatools and don’t want to add code to each script, you can set the DbaToolsConfig settings:

Set-DbatoolsConfig -FullName sql.connection.trustcert -Value $true -Register
Set-DbatoolsConfig -FullName sql.connection.encrypt -Value $false -Register

This will set the sql.connection.trustcert setting to $true, which will cause dbatools to trust all certificates, and the sql.connection.encrypt setting to $false, which will cause dbatools to use unencrypted connections.

Solution 3: Update the Module and Use the -TrustServerCertificate Switch

Rather than reverting to a previous module version, it’s advisable to update the module for security and other reasons. After updating, consider the following steps:

1. Install an Appropriate Certificate (Recommended): Acquire and install a certificate that is trusted by the system.
2. Use the -TrustServerCertificate Switch: If installing a certificate is not feasible, use the -TrustServerCertificate switch in your connection string. For example, when using Invoke-Sqlcmd to interact with a SQL database using the SQLServer module (applicable to DBATools as well):
   

   $server = "Your_Server"
   $db = "Your_Database_To_Connect"
   $query = "Select name from sys.databases;"
   Invoke-Sqlcmd -ServerInstance $server -Database $db -Query $query -ConnectionTimeout 3 -TrustServerCertificate
   

Video Explanation: