July 20, 2015 at 2:45 pm
I have searched through these forums for answers as to why our Access connections to SQL Server keep being forcibly closed and found several things to try, but none of them have worked. When we link from Access to SQL Server we get the data, but if the table is of a decent size, say 10,000 rows the connection times out within 10 minutes.
I've unsuccessfully tried using both SQL Server Native Client 10 and ODBC Driver 11 for SQL Server. The issue happens when connecting to both our SQL Server 2005 and SQL Server 2008 R2 instances. I tried connecting from multiple Windows 7 clients and even an XP client and had the same issue.
Below for reference I have listed some of the solutions I tried after searching this site and online and their sources, but none of them worked either:
=============================================
1. triggering SYN attack?
Windows 2003 SQL Server 2005- disabled SYN attack protection and rebooted and still had the issue
https://msdn.microsoft.com/en-us/library/ms187005.aspx
Windows 2008 R2 SQL Server 2008 R2 - syn attack protection cannot be disabled, but ran a netsh trace then searched the .etl file with MS Message Analyzer and did not see any SynAttacksDetected flags
most likely not a SYN attack
2. disabled TCP chimney offload, both with netsh command and from the NIC's Advanced device properties - no luck
https://support.microsoft.com/en-us/kb/951037
3. don't see any error messages listed here:
4. TCP port exhaustion - netstat -b does not show excessive ports used
https://msdn.microsoft.com/en-us/library/aa560610%28BTS.20%29.aspx
5. set Named Pipes to have a higher priority in SQL Native Client Configuration. Named Pipes connection does not work, then defaults back to TCP
=====================================================================
If anyone has any more ideas to try please share. Thanks.
July 20, 2015 at 4:03 pm
It's funny after taking the time to post in detail what I tried already I found a solution. I found out that the connection is lost only if opening up the table to view it's contents. Making the linked tables is fine. So I searched specifically for Access linked tables and the error message and found pretty much a direct hit here. It turns out I only had to click on the Last Record arrow and the connection does not close. Apparently it's a known issue that was not fixed as of yet; I'm using Access 2013.
Also, in thinking about this there isn't much use to view an entire table's contents for the most part. It's just too much information. However, if the user needs to do it the workaround it's easy.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply