The Spotlight Diagnostic Server connects to a SQL Server instance via TCP/IP; however there is "not" a collection schedule for "Monitored Server - SQL Server Connection Failure" alarms.
"SQL Server Connection Failure" alarms get raised by any collection that fails to return the requested data and instead sends whatever SQL Server error message back to the Diagnostic Server (DS), which determines if the error indicates the connection has failed. If you go to "Configure | Scheduling" you'll see all the collections for Spotlight with their respect collection frequency under "Collect in Background." Anyone of them that fails will signal back to the DS with the SQL Server error.
The Diagnostic Server will group these SQL Server errors under the alarm category like "Monitored Server - SQL Server Connection Failure." Spotlight looks at the SQL Server error code. Then groups it using the error code that SQL Server sends over. The take away point is that these error messages are SQL Server messages. Spotlight only groups them.