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

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

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?

Solution

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.....