Figured this out thanks to the quick help from the Qlik Support team. This was happening because the ODBC and OLE Engines were only installed on the Central Node.
Load balancing was enabled and I was being routed to a RIM node that did not have the ODBC connection or OLE Engines installed.
The solution was to install the ODBC connection or OLE Engines on all nodes.
If you are trying to connect the SQL server from Qlik, you can select the OLE DB provider for SQL server and use the below connection string.
OLEDB CONNECT TO [Provider=SQLOLEDB;Data Source=OLEDB CONNECT TO [Provider=SQLOLEDB;Data Source=OLEDB CONNECT TO [Provider=SQLOLEDB;Integrated Security=SSPI;Data Source=SQL-ServerName;Initial Catalog=Database-Name;];];]
This connection string doesn't mandate for you to create ODBC connections across multiple nodes.