Azure SQL Connection. Connection reset (due to timeout)

[Jesperrekuh]

I cant find a solution to solve the Connection reset error. So I have various sql-scripts that run more or less between 20 min - 1 hour.

The job-setup is plain and simple and is triggered from the crontab with different filepath parameter sql-script:

Open a connection (auto commit=true) , read a file with sql , execute it. Done > Close connection.

My additional parameters/properties: The -1 = indefinite

"MultipleActiveResultSets=false

;PersistSecurityInfo=false

;encrypt=true

;trustServerCertificate=false

;hostNameInCertificate=*.database.windows.net;loginTimeout=30

;cancelQueryTimeout=-1

;queryTimeout=-1"

However Azure SQL Gateway drops the connection, it basically states... screw you, bye bye...  Connection reset

Just a little extra context because finding root cause of errors is always tricky, but 9 outta 10 its straightforward.

Ive been running into this issue for over two years, it happened occasionaly once every 3 months... adding more jobs the frequency of this error increased.

Jobs are tested and run perfectly fine also in production. The common denominator between these jobs is that it is always a sql-script where I get the same error: SQLServerException: Connection reset

From the beginning I blamed Azure, it must have something to do with maintenance or cleanup, so if I wanted to avoid that I had to switch to the SQL Datawarehouse and pay way more instead of running this cheap instance... but no real harm was done. I live with the fact that a job takes ages (if everything is done to get the max performance out of the engine its ok) and if once a while the same error pops up, the next day its fine.

Searching lead me to :

* Idle by the Azure SQL Gateway, where TCP keepalive messages might be occurring (making the connection not idle from a TCP perspective), but not had an active query in 30 minutes. In this scenario, the Gateway will determine that the TDS connection is idle at 30 minutes and terminate the connection.