Hi Chad,
To preface a long post:
Laserfiche Support is focusing on the area most likely to have the actual root cause of the issue (the remote SQL server and/or connectivity to it) based on the information you've provided. They have likely made the same assessment I have about not taking the error message displayed in Cloud strictly at face value and are correctly interpreting it as "Cloud doesn't know why it didn't receive a response, only that one didn't show up within the timeout window."
I have provided a set of three validation tests you can perform to help pinpoint where exactly in the chain things are going wrong and rule out certain potential sources of the issue through a process of elimination.
Finally, this is not an appropriate avenue to complain about troubleshooting instructions for a specific support case. In the future, should you feel it's necessary, please request an escalation through the normal support process and a support manager will review your case. To be clear, we're not saying you can't post about issues you're encountering here, but rather that "I don't understand an error and don't understand why Support isn't focusing on my theory about what's wrong" isn't an okay way to make a post.
An appropriate way to phrase your question here on Answers would be "Hi, I'm encountering this error message in Cloud while trying to make a SQL query through a remote agent to a remote SQL server. While I currently have a support case open, I'm curious if anyone else has run into the same error and could share what they found and if/how they resolved the issue." Please see that you follow those guidelines in the future.
--------------------------------------------------------------------
If Cloud shows the agent is online, that means there is not a general network connectivity issue causing the timeout. The error Cloud is reporting is that it's not receiving a response back from the remote agent within the time window Cloud expects a response.
So the question is: "After receiving a query request from Cloud, why is the remote agent not sending the query response back within the timeout window?" Given that we know Cloud and the remote agent can communicate, the most likely answer is "The remote agent isn't returning the query results to Cloud within the timeout window because it's either (a) not receiving them from the SQL server at all, or (b) not receiving them fast enough."
The Cloud side has zero visibility into what's happening between the remote agent and the SQL instance, so how is it supposed to surface an error from SQL? All it knows is that it hasn't received a response. By way of analogy, say I ask I friend to go pick up a takeout food order and request they return within in 30 minutes. If 30 minutes passes and they're not back, all I know is that they're late. They could have run into traffic, the restaurant might not have actually had the food ready, they might have lost the order, or any number of things. If someone asks me what the status of the food is, there's nothing I can tell them aside from that I expected it to arrive within 30 minutes and it didn't arrive in that timeframe.
There are a few straightforward ways to sanity check this yourself if you'd like.
- Temporarily install a local SQL Express instance on the same machine as the remote agent, load or create any basic sample database, craft a simple query like selecting a single row from a small table, run it in SSMS locally to ensure it works, and then have Cloud try to run the exact same query through the remote agent. Assuming you set up the local SQL Express data source correctly, this test completely eliminates network connectivity issues between the remote agent and the SQL instance as a potential issue.
If Cloud successfully receives the response to this query within the timeout window, you know the underlying cause of the issue you're observing is not connectivity between the remote agent and Cloud. On the other hand, if the simple query to the local database also returns a timeout, that would be suspicious and you should report those results through your support case.
- Write a very simple "Hello World" type script in the supported language of your choice on the remote agent and try invoking it from Cloud. If Cloud successfully receives a response within the timeout window, you know the underlying cause of the issue you're observing is not general connectivity between the remote agent and Cloud.
- Install SSMS on the same server as the remote agent and run the exact same SQL query with the exact same parameters against the exact same database that you're trying to have the remote agent run. Do this several times. Record how long the query took to return for each test. By default, the .NET query execution timeout is 30 seconds. I'm not 100% sure remote agent uses the default .NET value, if your query is taking longer than (or anywhere close to) 30 seconds, I'd be highly suspicious that it does and you're probably hitting that limit.
If you can't connect to the remote SQL instance from SSMS on the same machine as the remote agent, you know there is likely a connectivity issue to the SQL instance that has nothing to do with the remote agent.
If can can connect to the remote SQL instance from SSMS but running the query from there fails or times out, you know the issue is on the SQL side.
Best,
Sam