Sample Header Ad - 728x90

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

0 votes
1 answer
731 views
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)
Asked by MrVimes (629 rep)
Jul 14, 2022, 12:47 PM
Last activity: Jul 15, 2022, 10:45 AM