Fix Invoke-SqlCmd Certificate Error: 3 Quick Steps (2025)

You’re running a PowerShell script to automate a routine SQL Server task. Everything is humming along, your code is clean, your logic is sound, and then... BAM! A wall of red text stops you in your tracks. Your script, which worked perfectly last year, now fails with a cryptic error from Invoke-SqlCmd.

If this message looks familiar, you're not alone:

Invoke-SqlCmd : A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)

This error has become increasingly common as Microsoft tightens security defaults in its SQL Server client drivers. What was once a permissive connection is now secure-by-default, and that’s a good thing! But it can be a frustrating roadblock when you just need your script to run. Don't worry. This guide will walk you through why this happens and provide three clear, practical steps to get you back on track, updated for 2025.

Why Are You Seeing This Error in 2025?

The root cause of this issue isn't a bug; it's a feature. Modern SQL Server client drivers, specifically versions of Microsoft.Data.SqlClient that PowerShell’s SqlServer module relies on, have changed a critical default setting. The Encrypt connection property, which used to be optional (false), now defaults to true (or more accurately, Mandatory in many contexts).

Here’s the sequence of events:

1. Your PowerShell script using Invoke-SqlCmd attempts to connect to SQL Server.
2. The client driver says, "Okay, security first! I must encrypt this connection." It sends a request to the server to start a secure TLS/SSL handshake.
3. Your SQL Server, especially in a development or default-install environment, responds with a self-signed certificate. This certificate essentially says, "Trust me, I am who I say I am."
4. The client driver on your machine examines the certificate and checks who issued it. It sees the certificate was signed by the server itself, not by a Certificate Authority (CA) that your machine trusts (like DigiCert, Let's Encrypt, or your own internal Enterprise CA).
5. Because the issuer is not trusted, the client driver aborts the connection to protect you from a potential man-in-the-middle (MITM) attack. This is when it throws the "certificate chain... not trusted" error.

This change forces developers and administrators to be intentional about their connection security, which is a significant step forward for protecting data in transit.

A Quick Word on Security Risks

Before we dive into the fixes, it's crucial to understand the implications. When you bypass certificate validation, you are telling your script, "Connect to any server that responds at this address, even if you can't verify its identity." In an unsecured network (like public Wi-Fi), an attacker could redirect your traffic to a malicious server, capture your credentials, and steal your data. Therefore, the "quick fix" below should only be used in environments you completely control and trust, like your local development machine connecting to a local SQL instance.

Solution 1: The Quick Fix for Dev/Test (TrustServerCertificate)

This is the fastest way to resolve the error in a non-production environment. You explicitly tell Invoke-SqlCmd to skip the certificate validation check.

When to use this:

Development machines, isolated lab environments, or any situation where the network between the client and server is completely secure and trusted.

How it works:

You add the -TrustServerCertificate switch to your command. This parameter passes the TrustServerCertificate=True property into the connection string, instructing the driver to accept any certificate the server presents, regardless of who issued it.

Implementation:

Simply append the switch to your existing command.

Failing Command:

# This will likely fail on a default SQL install
Invoke-SqlCmd -ServerInstance "YourServer\SQLEXPRESS" -Database "master" -Query "SELECT @@VERSION;"

Working Command (The Fix):

# This tells the client to trust the server's self-signed certificate
Invoke-SqlCmd -ServerInstance "YourServer\SQLEXPRESS" -Database "master" -Query "SELECT @@VERSION;" -TrustServerCertificate

That's it. Your script will now connect successfully. Remember, use this power wisely and only in safe environments.

Solution 2: The Proper Fix for Production (Encrypt Connection)

This is the correct, secure, and robust solution for production, staging, or any environment where data integrity is important. Instead of telling the client to be less secure, you make the server verifiably secure.

When to use this:

Production, UAT, QA, and any multi-user or sensitive data environment. This should be your default goal.

How it works:

The goal is to make the server's certificate trusted by the client. This involves two main parts: configuring SQL Server with a valid certificate and ensuring the client machine trusts that certificate's issuer. This is an infrastructure task, not just a code change.

1. Obtain a Proper Certificate: Get a TLS certificate for your SQL Server. This can be from a trusted public CA (e.g., Let's Encrypt) or from your internal corporate Active Directory Certificate Services (AD CS). The key is that the certificate's "Issued To" name (Common Name or Subject Alternative Name) must match the fully qualified domain name (FQDN) of your SQL Server.
2. Configure SQL Server: Install this certificate on the SQL Server machine and configure the SQL Server instance to use it via the SQL Server Configuration Manager. This requires specific permissions and a service restart.
3. Ensure Client Trust: If you used a public CA, your client machine (where the PowerShell script runs) already trusts it. If you used an internal AD CS, you must ensure the client machine has your company's Root CA certificate installed in its "Trusted Root Certification Authorities" store. This is often handled automatically via Group Policy in a domain environment.

Implementation:

Once the server-side and client-trust configurations are complete, your PowerShell code can be explicit about its security expectations. While Encrypt=True is often the default, being explicit is always better for clarity and future-proofing.

# This command works because the client can now validate the server's trusted certificate
# The -Encrypt parameter can be set to Connection, Mandatory, or Strict for different levels of security
Invoke-SqlCmd -ServerInstance "sqlprod.mycompany.com" -Database "Sales" -Query "SELECT 'Success!';" -Encrypt Connection

This approach takes more effort upfront but is the only way to guarantee a secure, encrypted connection that protects against MITM attacks.

Solution 3: The Modern Approach with dbatools

For anyone regularly working with SQL Server and PowerShell, the community-built dbatools module is an absolute game-changer. It's a comprehensive library of over 700 commands that simplifies nearly every aspect of SQL Server administration, including connection handling.

When to use this:

Recommended for all DBAs, developers, and sysadmins who frequently interact with SQL Server via PowerShell. It provides a more consistent and powerful experience.

How it works:

dbatools uses the same underlying Microsoft.Data.SqlClient driver, so it faces the same certificate validation rules. However, its commands, like Invoke-DbaQuery, provide the same easy-to-use parameters to control this behavior. It also offers a wealth of other benefits that make it worth the switch from the built-in SqlServer module.

Implementation:

First, if you don't have it, install the module from the PowerShell Gallery.

# Install for the current user
Install-Module -Name dbatools -Scope CurrentUser

Next, use Invoke-DbaQuery, the dbatools equivalent of Invoke-SqlCmd. It also has the -TrustServerCertificate switch for dev/test scenarios.

# The dbatools way to run a query while trusting the certificate
Invoke-DbaQuery -SqlInstance "YourServer\SQLEXPRESS" -Database "master" -Query "SELECT @@VERSION;" -TrustServerCertificate

While the fix is the same, adopting dbatools will streamline your future scripting tasks and make you more efficient in the long run.

Comparison: Which Method Should You Use?

Here’s a quick table to help you decide which solution fits your needs.

Conclusion: Secure Your Scripts, Banish the Errors

The "certificate chain not trusted" error in Invoke-SqlCmd is a sign of progress toward a more secure-by-default world. While initially jarring, it's a straightforward problem to solve once you understand the cause. By choosing the right solution for your environment—the quick -TrustServerCertificate switch for trusted dev/test scenarios or a properly configured certificate for production—you can ensure your PowerShell automations are both reliable and secure.

And if you haven't already, take this as your cue to explore the dbatools module. It will not only solve this problem but will also revolutionize the way you manage SQL Server with PowerShell. Happy scripting!