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
Last activity: Jul 15, 2022, 10:45 AM