Solve "Certificate Not Trusted" in Invoke-SqlCmd (2025)

You’ve been there. You're automating a deployment, running a health check, or just pulling some data. You fire off your trusty PowerShell script, call Invoke-SqlCmd, and lean back, expecting sweet success. Instead, your screen flashes red with an error that’s become increasingly common: "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.)"

Frustrating, right? It feels like a sudden roadblock on a familiar highway. If you’re seeing this more often, you're not alone. This isn't a bug; it's a sign of the times. As we move through 2025, the world of data is becoming secure by default, and this error is a direct consequence of that positive shift. Let's break down why it happens and, more importantly, the right way to fix it for good.

Why Is This Happening Now? The Shift to Secure by Default

For years, the default connection settings for SQL Server drivers were, let's say, a bit too trusting. Encryption was often optional. You could connect to a server, and if encryption wasn't explicitly configured, the data might fly across the network in plain text. In a modern IT landscape, that's simply not acceptable.

Recent versions of SQL Server (2019, 2022, and beyond) and the client drivers that PowerShell uses—especially the modern Microsoft.Data.SqlClient—have changed the game. They are moving towards a "secure by default" posture. This means the default value for the connection's Encrypt property is shifting from false (or Optional) to true (or Mandatory).

When your client is told to encrypt the connection, it does more than just scramble the data. It performs a crucial security check: it validates the server's TLS/SSL certificate. It asks, "Can I trust the identity of the server I'm talking to?" If the SQL Server is using a self-signed certificate (very common in development and test environments) or a certificate issued by an internal company Certificate Authority (CA) that your client machine doesn't know about, the validation fails. The client essentially says, "I can't verify who this is," and throws the error to protect you from a potential man-in-the-middle (MITM) attack.

The Tempting Shortcut: -TrustServerCertificate

When you're up against a deadline, a quick search will inevitably lead you to a simple, tempting solution: add the -TrustServerCertificate switch to your Invoke-SqlCmd command.

# The quick fix... that you should avoid in production.
Invoke-SqlCmd -ServerInstance "YourDevServer" -Database "Master" -Query "SELECT GETDATE();" -TrustServerCertificate

And just like magic, it works. But what did you just do? You told your client, "I know you can't verify the server's identity, but connect anyway. I trust it." You've effectively disabled a critical security feature.

Is this ever okay? Maybe for a quick test against a local SQL Server instance on your own laptop that you know is safe. But using this in any shared environment—Dev, Test, QA, and especially Production—is a dangerous habit. It teaches your scripts to ignore potential security warnings, leaving you vulnerable to attackers who could impersonate your SQL Server.

The Right Way: Establishing Genuine Trust

The professional, secure, and long-term solution isn't to bypass the check; it's to make the check pass. You need to teach your client machine to trust the certificate that your SQL Server is presenting. It's a one-time setup on each client machine that pays dividends in security and peace of mind.

Step 1: Get the Server's Public Certificate

First, you need the public part of the certificate from the SQL Server. You don't need the private key, just the public certificate (usually a .cer or .crt file) that the server uses to identify itself. The easiest way to get this is to ask your DBA or server administrator. They can export it directly from the server's certificate store.

On the SQL Server itself, they can find it by:

1. Running mmc.exe.
2. Going to File > Add/Remove Snap-in... > Certificates > Add > Computer account.
3. Navigating to Certificates (Local Computer) > Personal > Certificates.
4. Finding the certificate used by the SQL Server service, right-clicking it, and choosing All Tasks > Export.
5. Following the wizard, selecting "No, do not export the private key" and choosing the "Base-64 encoded X.509 (.CER)" format.

Once you have that .cer file, transfer it to your client machine.

Step 2: Import the Certificate on Your Client

Now you need to tell your client machine's operating system that this certificate is trustworthy. You do this by importing it into the correct certificate store. For system-wide trust (which is what you want for services and scripts), you must import it into the Local Machine's Trusted Root Certification Authorities store.

You can do this via the GUI or, even better, with PowerShell for automation.

Using PowerShell (Recommended):

Place the certificate file (e.g., MySqlServer.cer) in a folder like C:\temp\certs\. Then, run PowerShell as an Administrator and execute this command:

# Import the certificate into the correct store for system-wide trust.
Import-Certificate -FilePath "C:\temp\certs\MySqlServer.cer" -CertStoreLocation "Cert:\LocalMachine\Root"

This command places the certificate in the list of authorities that the machine implicitly trusts. It's clean, scriptable, and the correct location for this type of certificate.

Step 3: Connect Securely and Confidently

With the certificate now trusted by your client's OS, you can go back to your original Invoke-SqlCmd command. This time, it will work without the scary bypass switch.

# The secure and correct way. This now works!
# We explicitly ask for encryption for clarity and security.
Invoke-SqlCmd -ServerInstance "YourDevServer" -Database "Master" -Query "SELECT GETDATE();" -Encrypt Connection

Notice we added -Encrypt Connection. While modern drivers might make this the default, being explicit is always a good practice. It clearly states your intent: "I want a secure, encrypted connection." Because the certificate is now trusted, the validation succeeds, and the connection is established securely.

A Quick Look at the -Encrypt Parameter

The behavior of your connection can change based on the -Encrypt parameter. Understanding the options is key to a robust security posture.

Using -Encrypt Strict provides the highest level of security, as it ensures you are not only talking to a server with a trusted certificate but that you are talking to the exact server you intended to.

Conclusion: Security Is a Feature

That "Certificate Not Trusted" error isn't just an obstacle; it's a warning sign from a system that's trying to protect your data. While the -TrustServerCertificate switch offers a quick bypass, it's a crutch that undermines the very security it's designed to enforce.

By taking a few extra minutes to properly export and import the server certificate, you create a truly secure and reliable connection. You're not just fixing an error; you're adopting the modern, security-first mindset that's essential for any data professional in 2025. You're treating security as the critical feature it is.