SQL connection timeouts in Integration Host

I had a Database Query ready to go. The SQL was the same statement I already trusted. As soon as the query ran, there was a timeout. Integration Host showed the error clearly. The workflow stopped. The rows never came back.

Firstly, what does that actually mean?

It means the wait is not a workflow setting. There is no timeout box in the UI. Database Query, Database Receiver, the SQL sender. Same Connection panel on all of them. There is a Data Provider dropdown. There is a Connection String. There is the SQL underneath. There is not a separate timeout field sitting next to any of that.

The simple answer is that Sql Client already knows how long to wait. You tell it in the connection string. But that raises the next question: isn't a timeout just a timeout?

No. There are two of them, and they do different jobs.

Connect Timeout is how long to wait for the connection itself, in seconds. The instance is slow to accept you. The network path is having a moment. The server name is wrong and it sits there until it gives up. That clock starts before any SQL runs.

Command Timeout is how long a query or command may run once you are connected, also in seconds. A SELECT that scans more than it used to. A command waiting on a lock. A table that has grown since the last time anyone looked at it. Different clock.

Raise Connect Timeout when you cannot get in. Raise Command Timeout when you got in, then the query sat there. Read the error. Integration Host tells you which wait you hit.

To me it feels like an awkward leftover from treating the connection string as a secret you paste once and never read again. 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. Stay on Sql Client, not Sql Client (Legacy). The encryption post covers that dropdown.

Then add onto the connection string you already have. Do not start again. Keep Server, Database, and the credentials. Stick the two keys on the end.

Server=SQL01;Database=Orders;User Id=iface;Password=****;Connect Timeout=60;Command Timeout=120;

I used 60 and 120 because they are easy to read, not because they are magic. Use what the site actually needs. The values are seconds. Connect Timeout of 60 is one minute to get in. Command Timeout of 120 is two minutes for the query or command to finish.

If you only change one of them, change the one that matches the wait you are actually seeing. A connection error after a short pause is Connect Timeout. A query that starts and then dies later is Command Timeout.

The same Connection UI is on Database Query, Database Receiver, and the SQL sender. Same string. Same two keys. You set it on the connection the activity is using.

Give the wait the site needs. If a query needs two minutes because the table is large, say two minutes. If the connection is just slow to accept you, say that on Connect Timeout and leave Command Timeout alone.

Anyway. As soon as the query ran, there was a timeout, and Integration Host showed it. There is no timeout box. Add Connect Timeout and Command Timeout to the connection string, in seconds, and give the wait you actually need.

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.