TCP error “An existing connecion was forcibly closed by the remote host.”

I have an 3rd-party application that uses a SQL back-end, but uses Access 2000 (linked tables all using the same DSN) as an intermediary. We recently upgraded from SQL 7 to SQL 2005, which went fine. Now, however, we want to setup SQL Mirroring to provide automatic failover.

The mirroring works fine. In my test environment, the mirror server becomes active automatically when the service is stopped on the principal server. I can see this in SQL Management Studio. WMI events fire properly, logs are kept synchronous, and all is generally well.

The problem is in getting Access to continue operation after the failover. I redid the DSN to use the SQL Native Client driver instead of the standard SQL Server driver, specified the failover partner, made sure the default database was correct, made sure that the specified login (using a SQL account) had the proper SPID and had its default database configured properly. Everything looks correct. I’ve redone the links using he Linked Table Manager. (The problem also happens if I start a fresh database instead of changing the existing one.) If I look at the properties page of each table I see this connection string:

ODBC;DSN=;DATABASE=;Failover_Partner=;;TABLE=

The first time I attempt to open any table I get the following error:

“ODBC – call failed. [Microsoft][SQL Native Client]TCP Provider: An existing connecion was forcibly closed by the remote host. (#10054)”

Subsequent attempts give me:

“ODBC – call failed. [Microsoft][SQL Native Client]Communication link failure (#0)”

If I close and reopen the Access database, everything will work fine again. If I leave the Access database open and alone for an indeterminate amount of time (maybe 10-15 minutes), it will begin working again. I don’t see anything odd in the SQL logs or in Events on the client machine or either SQL server.

I already tried disabling Shared Memory in the SQL Server Configuration Manager, and made sure TCP/IP is the top protocol (and Named Pipes second).

I tried adding “pooling=false” to the connection string of a linked Access table and that failed. I added a CPTimeout value of 1 to HKLM\Software\ODBC\ODBC.INI\. Still nothing works.

I’ve noticed this also happens if I use a direct connection string from ADO. I step through code, open the connection, force a failover, then continue stepping through code.

I’ve been searching all over and haven’t found anything that seems relevant to my specific problem, and no sure-fire solutions.

Any ideas about how I can get this to work?