0

this may be a simply question but I hope its not.

I run some very long winded SQL operations on my local PC, which is hard wired to an ADSL modem for internet. The SQL Server and databases are ALL local on my PC, and the processing seems fine. (as fast as normal).

However, if my internet drops out - which happens perhaps a few times a night (at later hours usually), my SQL connection also drops with the familiar Connection lost error. (An error one would get if connecting to SQL over a network)

For me this does not make sense, my SQL connection string refers to the local instance only and no processing is over a network of any kind (I have VPN sometimes active, but not always when this happens)

I can run the same SQL processing without the modem connected with no issues at all. (Although sometimes it can take many many hours so I prefer to have the modem connected)

Could this possibly be due to the extra SQL Services ie Browser - that is somehow affected by the modem losing its internet connection?

(I would like to know that my ISP-provided modem is not doing any funny business in the background - like examining my data / traffic / etc)

Any help appreciated

Grantly
  • 2,546
  • 2
  • 21
  • 31
  • 1
    Are you connecting to `127.0.0.1` or the IP your Internet connection uses? – Mike Christensen Apr 24 '14 at 21:15
  • Hi Mike. No, I use the PCNAME\INSTANCENAME name syntax in the connection string. ie **Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=DATABASE_I_USE;Data Source=PCNAME\INSTANCENAME** – Grantly Apr 24 '14 at 21:18

1 Answers1

2

Try this connection string:

Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=DATABASE_I_USE;Data Source=127.0.0.1\INSTANCENAME

This should force the traffic onto the loopback adapter, which will allow it to avoid cases where your physical card's network link may drop.

If that does not work, try explicitly installing a loopback adapter.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
  • Thanks Joel, I will certainly try this. (may not know if it works for a few days) But I will try pulling the cable to see if that emulates the problem and your solution. I was starting to consider if the VMWare network adapters were involved, but they are disabled. – Grantly Apr 24 '14 at 21:24
  • OK thats weird. Pulled the cable out of my modem (network cable to the PC) and no problems...SQL processing keeps running...(With the unchanged connection string - not 127.0.0.1 yet) – Grantly Apr 24 '14 at 21:26
  • (Will turn OFF modem, see what that does, hopefully emulates the problem so I can test your solution - will return in 5 minutes) – Grantly Apr 24 '14 at 21:27
  • OK more strangeness... Turn modem OFF, and no problem. SQL keeps chugging away. Turn modem ON after a few seconds and all is fine...Until the ADSL starts actually connecting after about 2 minutes - then I get the normal Network Connection Error and my SQL application is killed. (I could manage the error and re-connect, but seems alot of hassle for something that I should be able to avoid) – Grantly Apr 24 '14 at 21:33
  • It sounds like the modem may be trying to act as some sort of weird network bridge, where it replicates your internal IP address to the outside. I'd still fall back to changing the connection string and manually installing the loopback adapter, to ensure that the packets go through a path that has nothing to do with your modem. – Joel Coehoorn Apr 24 '14 at 21:35
  • OK finally tried your suggestion (as I had to reconnect to restart the SQL app) and using **Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=databasename;Data Source=127.0.0.1\instancename** I get the following error, 'Login failed. The login is from an untrusted domain and cannot be used with Windows authentication'... Strange as this current User (me) is also sysadmin for all of SQL objects. Will try with explicit credentials... – Grantly Apr 24 '14 at 21:37
  • Well, restarted the modem twice - slowly - and the application is unaffected. Thanks - this has solved my issue :) And thanks Mike C - perhaps you were on the same track. (Now to get around to not using explicit credentials...I'm still suspicious of the ISP-provided modem snooping somehow) – Grantly Apr 24 '14 at 21:51