SQL Client encryption in Integration Host

I pointed a Database Query at a SQL Server I use all the time. As soon as it tried to connect, it failed. Integration Host showed the error clearly. It was not a bad password. It was not the wrong instance name. Newer Sql Client wanted encryption on, and that server was not set up for it.

Firstly, what does that actually mean?

It means the client now assumes Encrypt=True unless you tell it otherwise. The connection string you have been pasting for years may no longer be enough on its own. You get a failed connection before any SQL runs.

The simple answer is that Sql Client changed its default. But that raises the next question: isn't encryption just something the server does?

No. Both sides have to agree. The server has to be ready to talk TLS. The client has to ask for it, or at least accept it. If the client insists and the server is not configured, the handshake fails. You never reach the query. You get a connection error.

I have seen this look like a network problem. It is not. The instance is there. The credentials can be fine. Integration Host is using the current Sql Client, and that one wants encryption on by default.

To me it feels like one of those defaults that is correct on paper and awkward on a hospital SQL box that has been running the same way since someone stood it up. However, it is what it is...

But then the important question is how to deal with it?

Well, now that I do my integrations in Integration Host, things have become simpler (yeah, like everything else). Open the activity. Look at Connection. Data Provider first.

The dropdown shows Sql Client and Sql Client (Legacy). Stay on Sql Client. Not Legacy.

That is the Connection panel with the Data Provider list open. The red arrows sit on Sql Client and Sql Client (Legacy). Query Database and SQL Query sit underneath. Database Receiver uses the same Connection UI. The SQL sender does too.

Legacy is the old provider. Stay on Sql Client. If the server is not set up for encryption, tell the current client what you want, in the connection string you already have.

Do not start a new string. Keep Server, Database, and the credentials. Add this on the end:

Encrypt=False;TrustServerCertificate=True;

Encrypt=False turns off the new default. The client will not require a TLS handshake the server cannot do.

TrustServerCertificate=True is the other half. If something still presents a certificate the machine does not trust, this says to accept it. I put both on when the server is not set up for this.

If the server is set up for encryption, you do not need Encrypt=False. Leave the default alone, or set Encrypt=True so it is obvious in the string.

Try it in a dummy workflow first. Not the live one. A throwaway Database Query against a table you do not care about. Same Data Provider. Same style of connection string. A catalog that does not matter. Change one thing. Run it. Then copy the line you trust into the real activity.

Anyway. Newer Sql Client wants encryption on. If the server is not set up for it, the connection fails. Stay on Sql Client, add Encrypt=False;TrustServerCertificate=True; to the string you already have, and try it somewhere that is not live first.