Powershell - The certificate chain was issued by an authority that is not trusted (dbatools)

Question

I am using dbatools scripts, but most commands return:

PS C:\Users\Administrator> Test-DbaDiskAlignment -ComputerName sqlag| Format-Table
WARNING: [23:22:13][Get-DiskAlignment] Failure | The certificate chain was issued by an authority that is not trusted

I've found some references to "TrustServerCertificate=True", but it seems to be used in SQL Server connection string. How to set it in powershell?

Answer 1

One of the things that script does is check if the disk is actually being used for SQL data files.

But it only connects using an instance name and credentials, no other parameter is passed, so if the certificate is untrusted then it will fail. See the source code.

You can avoid this by using the -NoSqlCheck, which prevents the check from happening. But I urge you to get a proper certificate for your SQL Server instance.

If you want, you can create a pull request on Github to add other parameters to the connection settings.

---

It seems you actually do want to connect to SQL Server, in order to run other scripts such as Backup-DbaDatabase.

In which case you need to force it to trust the server certificate, assuming you don't want to install a proper certificate. As I'm sure you know, this is a significant security issue.

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

Backup-DbaDatabase -SqlInstance $server.....

Answer 2

You must have installed a dbatools version 2.0 or higher and I think, you have 3 options.

Option 1. Go back to an older version of dbatools like 1.1.145

uninstall-module dbatools -force -verbose
install-module -name dbatools -requiredversion 1.1.145

Option 2. You want to keep your current version and get this fixed for your script only hence add the following at the beginning of your script

Set-DbatoolsInsecureConnection -SessionOnly

Option 3. As dbatools suggested, set DbaToolsConfig to use the old settings

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

Answer 3

Instead of going back to previous version, it's better to use the updated version due to various reasons (security etc.)

Ensure to update the module, then either install appropriate certificate (recommended) if not, use -TrustServerCertificate switch in your connection string. I encountered this same error when using "SQLServer" module and here is what I did (whilst awaiting for the certificate installation):

#Updated module:

Update-Module SQLServer

#Used -TrustServerCertificate switch when interacting with server: (following example uses Invoke-Sqlcmd to interact with the SQL database that uses "SQLServer" module, same applies for the "DBATools" or any other relevant module):

$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

HTH.