DbaTools and SQL server connection and query

PowerShell Help
1

This touches specifically on the dbatools module, so it may be a bit out of scope for this forum. I’m just chancing that someone has used the module and may point me in the right direction.
I’ve tried to see if the DbaTools module had their own forum or community.

I’m trying to create a table in a SQL database with the dbatools module and it’s not working for me.
I’ve got my instance in the variable $instance, database in $db, I’ve created a credential object in $cred:

$cred = New-Object -TypeName System.Management.Automation.PSCredential ($user,(ConvertTo-SecureString -String $pw -AsPlainText -Force))

I’ve successfully connected to the database with:

$connection = Connect-DbaInstance -SqlInstance $instance -SqlCredential $cred -TrustServerCertificate

The important part is the -TrustServerCertificate parameter as SQL Server 2022 seems to require this for connections.

When I try to run an Invoke-DbaQuery (the query itself is supposed to populate a table based on a CVS and is not important in this case) using the established connection like this:

Invoke-DbaQuery -SqlInstance $connection -Database $db -Query $createTableQuery

I get this error:

WARNING: [16:12:10][Invoke-DbaQuery] Failure | The certificate chain was issued by an authority that is not trusted

It seems like it’s not really using the existing connection even though I’m sending it through.

Am I missing something entirely here?
Is the DbaModule the wrong tool for this or should I do this with the SqlServer module instead?
Or is there a better way of creating and populating a SQL database?

2

Have you tried setting it globally for all commands?

Set-DbatoolsConfig -FullName sql.connection.trustcert -Value $true

That will set it for the session only. If you want to make that permanent, use the -Register switch.

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

Thank you and sorry for the late feedback.

Added this line at the top of the script:

Set-DbatoolsConfig -FullName sql.connection.trustcert -Value $true

And that did the trick.

I’d seen that line before, but for some reason I took it to mean that it set that preference for the module not for a specific session. Probably missed the significance of the -Register parameter.