Anything but small ODBC queries failing over long distance VPN after SQL Server Swap

odbcsql-server-2005sql-server-2008vba

I recently swapped database servers (restored databases from old to new and then swapped IPs)…

Old: Sql Server 2005, Windows Server 2003
New: Sql Server 2008, Windows Server 2012

All processes that connect to the server (dot net website, PHP, vbscript, mssql) from a geographically nearby VPN work fine on the new server.

But we have a VPN between the server (UK) and a satelite office (KY USA) and a database application running on Microsoft Access, and this application is throwing an error when performing queries on the new server. The error is….

Error: ODBC–call failed.

[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (recv()).
(#10054) [Microsoft][ODBC SQL Server Driver][DBNETLIB]General network
error. Check your network documentation. (#11)

If I temporarily have this application query the old server (by changing the local hosts file) the error doesn't occur and everything works.

To help with troubleshooting this I built a small script…

'ConString="Provider=SQLOLEDB;Data Source=172.17.201.11,1433;" & _
'   "OLE DB Services=-2;Initial Catalog=Perf;Network=DBMSSOCN;User Id=*;Password=*"

ConString="Provider=SQLOLEDB;Data Source=172.17.201.11,1433;" & _
    "Initial Catalog=Perf;Network=DBMSSOCN;User Id=*;Password=*"
    set conn = CreateObject("ADODB.Connection") 
    
Conn.connectiontimeout=500
Conn.commandtimeout=500
conn.open ConString

SCRIPTTIMEOUT = 1000

'sqlstring="SELECT top 100 ClientID FROM Clients"
sqlstring="SELECT top 1000 ClientID FROM Clients"
'sqlstring="SELECT ClientID FROM Clients"

'succeeds with top 100
'fails with top 1000 or no limit


set Rcount = Conn.Execute(sqlstring)

msgbox Rcount(0)

Rcount.Close
set Rcount = Nothing

set sqlstring = Nothing
Conn.Close
set Conn = Nothing

The script works if I query for 100 rows. If I try 1000 it fails every time (I haven't yey tried intermediate amounts but I don't see much point narrowing it down)

The script works if I switch the IP address to the old server (Everything else is the same – same vpn, same client, same script)

The script and the access application work fine in every way over a different VPN (Also UK)

  • I have trawled through every setting I can find on each server (in MS SQL Studio, Configuration Manager, Network Settings) to try to match, and try different settings.
  • I have stopped and started services
  • I have googled this and tried every suggestion I could find (I've lost track, but I have tried adding OLE DB Services=-2. I have tried Encrypt=on (and off). I have tried Trusted_connection=on (and off). I have tried Pooling=on (and off). I have tried specifying the instance name after the IP.

Both servers are in the same physical rack, on the same network/subnet, connected to the same physical switch.

Before I go down the route of messing with VPN settings, swapping wires (Requiring access to the DC) is there something I can/should do/look at on the new server to help?

Edit: If I add Packet Size=1024; to my connection string it works! Thing is, both servers have that set to 4096 by default, and the old server works just fine with that.

Edit 2 The above worked for my test script, but not for the access app.

I am hoping to find a workaround that doesn't require a connection string change because that is going to be a hassle (involving the developer of the access app)

Best Answer

Not my ideal solution (as I was hoping to avoid changing connection strings) but I solved this in my test script by adding Packet Size=1024 into the connection string.

This didn't work for the Access app. However I then created a little test access app, and by doing so I was able to figure out that the change wasn't working in the main Access app because the connection string was using a different driver and fields. By using the same connection string in the Access app that I was using in my test script it worked.

So it seems the Packet Size=1024 setting either isn't recognised or is ignored in the connection string of the following format...

DRIVER=SQL Server;SERVER=live-db-server,1433;Packet Size=1024;UID=xxx;PWD=xxx;
APP=Microsoft Office 2016;DATABASE=Perf

But does work in the alternate connection string...

Provider=SQLOLEDB;Data Source=live-db-server,1433;Packet Size=1024;
Initial Catalog=Perf;Network=DBMSSOCN;User Id=****;Password=****
Related Question