title: System DSN ODBC connections working initially but failing after at least 4 hours
body: <p>I am trying to migrate data from our Enterprise Data Warehouse to one of our own reporting databases via ODBC and Python scripts.  The python scripts import <a href="https://github.com/mkleehammer/pyodbc" rel="nofollow noreferrer">pyodbc</a> to handle the database connections and execute SQL queries.  A system DSN is defined on our ETL server for each database involved in the process.</p>
  <ul>
  <li>Enterprise Data Warehouse (Data Source)</li>
  <li>ETL Server - Windows Server 2016 with SQL Server 2016 instance</li>
  <li>Reporting database (Data Destination) - Windows Server 2016 with SQL Server 2019 instance</li>
  </ul>
  <p>When the ETL server is freshly rebooted, all the scripts successfully execute and transfer data as expected.  After a number of hours though one of the connections no longer works until a system reboot is performed.  Rebooting the ETL server before every scheduled ETL job isn't ideal and I would like to have these connections persist without a reboot.</p>
  <p>Here is the error</p>
  <blockquote>
  <p>[Microsoft][ODBC Driver 17 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [53].  (53) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has
  occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (53)')</p>
  </blockquote>
  <p>I tested each of the DSNs using ODBC Data Source Administrator (64-bit)</p>
  <ul>
  <li>DSN for EDW connectivity test successful</li>
  <li>DSN for ETL connectivity test successful</li>
  <li>DSN for Reporting database connectivity test failed
  <a href="https://i.stack.imgur.com/PzGxR.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/PzGxR.png" alt="(Screenshot)" /></a></li>
  </ul>
  <p>Even when the scripts throw errors and the connectivity tests fail</p>
  <ul>
  <li>I am still able to remotely connect to Reporting Database in SQL Server Management Studio and execute queries</li>
  <li>The Reporting Database server also remains accessible via Windows Remote Desktop Connection so its not powered off or unavailable on the network.</li>
  </ul>
  <p>Once the ETL server is rebooted, the DSN connection tests succeed and everything returns to normal for a few hours.</p>
  <p>This problem also occurs with OLE DB connections defined in SSIS packages, which is why I'm trying python and ODBC for this project.  Turns out I'm running into the exact same issue.  The SSIS errors are also driven by the Connection Manager for the Reporting Database.</p>
  <p>I have full administrative privileges on the ETL server as well as the Reporting Database but I am in an Enterprise environment and lack privileges to administrate networking.</p>
  <p>Please let me know what other information would be helpful to diagnose and troubleshoot this problem.</p>
  <p>Thanks in advance!</p>