Sockets: code works locally but fails over LAN

[n00m]

import socket, thread
host, port = '192.168.0.3', 1434
s1 = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
s2 = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
s2.connect((host, 1433))
s1.bind((host, port))
s1.listen(1)
cn, addr = s1.accept()

def VB_SCRIPT():
while 1:
data = cn.recv(4096)
if not data: return
s2.send(data)
print 'VB_SCRIPT:' + data + '\n\n'

def SQL_SERVER():
while 1:
data = s2.recv(4096)
if not data: return
cn.send(data)
print 'SQL_SERVER:' + data + '\n\n'

thread.start_new_thread(VB_SCRIPT,())
thread.start_new_thread(SQL_SERVER,())

=============================================

The above code acts as an intermediator between a very simple VB script
and SQL Server. Like follows:
the vbs sends its requests to "fake" port 1434 and the python code
catches and re-sends them to sql server which listens to its DEFAULT
port = 1433... And vice versa.


=========================================================
VB script:
=========================================================

Set cn = CreateObject("ADODB.Connection")
cn.Open _
"Provider=sqloledb;Data Source=192.168.0.3,1434;" & _
"Network Library=DBMSSOCN;Initial Catalog=pubs;" & _
"User ID=qwe;Password=asdasd;"

cn.Execute "select * from authors;"
cn.Close
Set cn = Nothing

=========================================================


It works fine (I see all client/server data printed in IDLE window)but
only locally. I.e. if vbs, python and sql server run on the same
machine.

If I run the vbs from some other machine in my LAN then it fails to
work out properly. Below is all that vbs and sql server are able to say
to each other:


===========================================================

VB_SCRIPT:
 
SERVER qwe asdasd 000000a5 Р·€Ut



Microsoft (r) W 192.168.0.3,1434  asdasd
 OLEDB 


SQL_SERVER:

Щ 3
г

pubsmaster«0 E  # Changed database context
to 'pubs'.
W г

us_english «4 G
' Changed language setting to us_english.
W г
cp1251
­
 Microsoft SQL Server _ Вг 40964096Ñ


VB_SCRIPT:

G 
4096 

==========================================================


In abt 30 seconds OLE DB Provider (on the vbs side) reports "General
Network Error".


PEOPLE, WHY ON THE EARTH IT DOES NOT WORK OVER LAN ???

PS:
Of course, without involving Python into the process vbs and sql server
communicate with each other just fine - no matter locally or over LAN;
on win2k machines or on/from NT 4.0 machine.

 

[Peter Hansen]

import socket, thread
host, port = '192.168.0.3', 1434
s1 = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
s2 = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
s2.connect((host, 1433))
s1.bind((host, port))
s1.listen(1)
cn, addr = s1.accept()

def VB_SCRIPT():
while 1:
data = cn.recv(4096)
if not data: return
s2.send(data)
print 'VB_SCRIPT:' + data + '\n\n'

def SQL_SERVER():
while 1:
data = s2.recv(4096)
if not data: return
cn.send(data)
print 'SQL_SERVER:' + data + '\n\n'

Several suggestions:

1. Use repr(data) instead of just 'data' above, to see better the actual
bytes without the possibility of control characters like \r and \b
screwing things up.

2. I'm not at all sure that accessing the same socket object
simultaneously from two threads is safe. You might consider creating a
pair of Queue objects to safely communicate the information between the
two threads. That, of course, poses the problem of how do you wait on
data to arrive from the socket and from the Queue at the same time. One
approach is to use non-blocking sockets or timeouts, while the other is
to use a pre-existing asynchronous framework such as, say, Twisted, and
avoid reinventing the wheel (and making all the same mistakes that other
programmers have made zillions of times before you).

It's also possible this is not remotely related to your problem, but I
suspect without knowing more about SQL Server, VB, and your own setup
I'd be guessing wildly anyway...

-Peter

 

[Fredrik Lundh]

PEOPLE, WHY ON THE EARTH IT DOES NOT WORK OVER LAN ???

what happens if you change

s1.bind((host, port))

to

s1.bind(("", port))

?

</F>

 

[Grant Edwards]

2. I'm not at all sure that accessing the same socket object
simultaneously from two threads is safe.

It's OK under Unix. Having one thread handle rx and a
different one handle tx is a pretty widely used method.

Don't know about Win32...

 

[n00m]

Thank you all for your replies!
1.
repr() is not what I need (currently).
I'd better like to see the pure text of "talkings" between VBS and SQL
Server.
2.
Jp, thank you very much for the links!
I just oblige to test this Twisted stuff, but I'm afraid it's a bit
above my head
so far.
And, frankly speaking, above all I'd like to understand why my code
only works
locally... I wish it did not work AT ALL. Sure you understand what I
mean.

 

[John Hazen]

import socket, thread
host, port = '192.168.0.3', 1434
s1 = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
s2 = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
s2.connect((host, 1433))
s1.bind((host, port))

I think the problem is that you're using the same host for both the send
and recieve sockets. Wouldn't you want one host to be the localhost,
and one to be sql server?

If it doesn't work even when only the VBscript is on a different box,
are you running an OS with a firewall that you have to disable?

-John

 

[Bryan Olson]

> Peter Hansen wrote:
>
>
>
> It's OK under Unix. Having one thread handle rx and a
> different one handle tx is a pretty widely used method.
>
> Don't know about Win32...

A thread for send and one for recv is also frequently used with
Winsock. Can anyone vouch for the Python wrapping?

 

[Grant Edwards]

A thread for send and one for recv is also frequently used
with Winsock. Can anyone vouch for the Python wrapping?

The last time I looked, it was a pretty thin wrapper, and
shouldn't cause a problem. I just looked at 2.3.5 sources. [I
think the timeout feature is new since I hacked on socketmodule.c
last.] I don't really see anything troublesome.

Basically, socket.recv() just calls recv() and socket.send()
just calls send().

 

[Bryan Olson]

> import socket, thread
> host, port = '192.168.0.3', 1434

Consider using INADDR_ANY instead of the specific host IP
address. The empty string will resolve to INADDR_ANY if passed
as the host to bind(). (Though that's not the problem.)

> s1 = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
> s2 = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
> s2.connect((host, 1433))
> s1.bind((host, port))
> s1.listen(1)
> cn, addr = s1.accept()

You probably want the the accept() in a loop.

> def VB_SCRIPT():
> while 1:
> data = cn.recv(4096)
> if not data: return
> s2.send(data)

Use sendall(), not send().

> print 'VB_SCRIPT:' + data + '\n\n'
>
> def SQL_SERVER():
> while 1:
> data = s2.recv(4096)
> if not data: return
> cn.send(data)

As above, use sendall().

> print 'SQL_SERVER:' + data + '\n\n'
>
> thread.start_new_thread(VB_SCRIPT,())
> thread.start_new_thread(SQL_SERVER,())
>
> =============================================
>
> The above code acts as an intermediator between a very simple VB script
> and SQL Server. Like follows:
> the vbs sends its requests to "fake" port 1434 and the python code
> catches and re-sends them to sql server which listens to its DEFAULT
> port = 1433... And vice versa. [...]
> It works fine (I see all client/server data printed in IDLE window)but
> only locally. I.e. if vbs, python and sql server run on the same
> machine.

[...]


My first two guess are:

The client is trying to make more than one connection.
Putting accept in a loop and moving some stuff will support
that.

The send() call is not sending all the data. Using sendall()
should fix that.


Try the following, but *not in Idle*. The code doesn't yet have
a clean stopping method, and the run-on threads will screw-up
Idle.



import socket, thread

sqls_host, sqls_port = '192.168.0.3', 1443
proxy_host, proxy_port = '', 1434

# How I tested it:
# sqls_host, sqls_port = 'www.google.com', 80


def VB_SCRIPT():
while 1:
data = cn.recv(4096)
if not data: return
s2.sendall(data)
print 'VB_SCRIPT:' + data + '\n\n'

def SQL_SERVER():
while 1:
data = s2.recv(4096)
if not data: return
cn.sendall(data)
print 'SQL_SERVER:' + data + '\n\n'


s1 = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
s1.bind((proxy_host, proxy_port))
s1.listen(5)

while 1:
cn, addr = s1.accept()
s2 = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
s2.connect((sqls_host, sqls_port))
thread.start_new_thread(VB_SCRIPT,())
thread.start_new_thread(SQL_SERVER,())

 

[Dennis Lee Bieber]

Thank you all for your replies!
1.
repr() is not what I need (currently).
I'd better like to see the pure text of "talkings" between VBS and SQL
Server.

repr() shows everything -- including the values of any "non
printable" characters.
This is
strangely spaced?
Note that without the repr() you can't tell that the "spaces" in the
second line are the result of a single <tab>, nor that there are a lot
of spaces following the "is". And the last character (displays as a ?
here, shows up as a HEART on the command console) is a <ctrl-c>.

"x" was created using:

--

 

[n00m]

Bryan;
I tested your code locally (in I*D*L*E) - it works fine!
And of course I'll test it over LAN but only tomorrow - at work.
See the picture of my IDLE window with output of your code:
http://free.7host02.com/n00b/socket_Br.gif
Note the 4th line in Blue: there Z is the name of my home machine,
qwe is the login name (from VBS(ADO) connection string) and right
after "qwe" - this login's password ("asdasd") but it's encrypted
by oledb provider.

Dennis;

I understand pretty well what you mean. Thank you for the repr().

Fredrik;

Locally it works! The rest - tomorrow.

John;

How exactly should I "correct" the code?

 

[Bryan Olson]

>
> I tested your code locally (in I*D*L*E) - it works fine!

Glad it worked, but I'd still disrecommend IDLE for that
version. Threads may live after the program seems to be done
(and may still own the port you need). Below is a version that
respects ^C to terminate more-or-less cleanly.

> And of course I'll test it over LAN but only tomorrow - at work.
> See the picture of my IDLE window with output of your code:
> http://free.7host02.com/n00b/socket_Br.gif

I didn't touch the printing, so it should output the same thing
as your version. Looks like you've got some UTF-16 action there,
and Python may be able to print it nicer if you use the
unicode/codec stuff.


--
--Bryan


import socket, threading, select

sqls_host, sqls_port = '192.168.0.3', 1443
proxy_host, proxy_port = '', 1434


def start_deamon_thread(func, args):
""" Run func(*args) in a deamon thread.
"""
thread = threading.Thread(target=func, args=args)
thread.setDaemon(True)
thread.start()


def sock_copy(s_from, s_to, annotation):
while 1:
data = s_from.recv(4096)
if not data:
break
s_to.sendall(data)
print annotation + data + '\n\n'


s1 = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
s1.bind((proxy_host, proxy_port))
s1.listen(5)

while 1:
s, _, _ = select.select([s1], [], [], 1.0)
if s:
cn, _ = s1.accept()
s2 = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
s2.connect((sqls_host, sqls_port))
start_deamon_thread(sock_copy, (cn, s2, 'VB_SCRIPT:'))
start_deamon_thread(sock_copy, (s2, cn, 'SQL_SERVER:'))

 

[Dennis Lee Bieber]

I tested your code locally (in I*D*L*E) - it works fine!
And of course I'll test it over LAN but only tomorrow - at work.
See the picture of my IDLE window with output of your code:
http://free.7host02.com/n00b/socket_Br.gif
Note the 4th line in Blue: there Z is the name of my home machine,
qwe is the login name (from VBS(ADO) connection string) and right
after "qwe" - this login's password ("asdasd") but it's encrypted
by oledb provider.


I understand pretty well what you mean. Thank you for the repr().

Your screen capture would be a lot easier to read if it had been
used... We'd see what all those boxes are hiding (Offhand, I'd suspect
unicode string data, the way recognizable words are being spaced...)
--

 

[Bryan Olson]

> Below is a version that respects ^C to terminate
> more-or-less cleanly.

Oops, one more bug^H^H^H improvement. I forgot to shutdown
writing.

> import socket, threading, select
>
> sqls_host, sqls_port = '192.168.0.3', 1443
> proxy_host, proxy_port = '', 1434
>
>
> def start_deamon_thread(func, args):
> """ Run func(*args) in a deamon thread.
> """
> thread = threading.Thread(target=func, args=args)
> thread.setDaemon(True)
> thread.start()
>
>
> def sock_copy(s_from, s_to, annotation):
> while 1:
> data = s_from.recv(4096)
> if not data:

Insert:
| s_to.shutdown(socket.SHUT_WR)

> break
> s_to.sendall(data)
> print annotation + data + '\n\n'
>
>
> s1 = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
> s1.bind((proxy_host, proxy_port))
> s1.listen(5)
>
> while 1:
> s, _, _ = select.select([s1], [], [], 1.0)
> if s:
> cn, _ = s1.accept()
> s2 = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
> s2.connect((sqls_host, sqls_port))
> start_deamon_thread(sock_copy, (cn, s2, 'VB_SCRIPT:'))
> start_deamon_thread(sock_copy, (s2, cn, 'SQL_SERVER:'))

 

[n00m]

My today's tests (over LAN).
I think *it* will drive me mad very soon.

Firstly I tested both Bryan's codes. And they worked fine!
Just as if they were tested locally!

Then I tested Fredrik suggestion. And it worked out too.
Expect unexpected, - as they say.

At last I decided to test my own version (just for the
experiment's purity). And... pfffff... it worked OK!

What happened to those machines over the night?
NOTHING! Nobody can access them but me.

Then I decided to test it from other ("the third") machine.
And again both Bryan's codes worked out like champs.
But my own ... failed! (as usually)!

Then I ran the vbs from the 4th machine...
Bryan's and *mine* worked OK...

The client is trying to make more than one connection.

I don't think so. Look at the very first line of the vbs:

Set cn = CreateObject("ADODB.Connection")

Create .Connection! NOT .Connections.

Glad it worked, but I'd still disrecommend IDLE...

But it does NOT work without IDLE!

PS:

Oops, one more bug^H^H^H improvement.
I forgot to shutdown writing.

LOL Thank you! But while I don't understand what's going on
with the core part of the code this improvement doesn't much
matter.

 

[Bryan Olson]

> My today's tests (over LAN).
> I think *it* will drive me mad very soon.

<Conflicting results snipped>

Network programming is like that. Just because something worked
once doesn't mean it really works. I had guessed two causes for
the behavior you were seeing, and either could result in sporadic
failures.

> Bryan wrote:
>
>
> I don't think so. Look at the very first line of the vbs:
>
> Set cn = CreateObject("ADODB.Connection")
>
> Create .Connection! NOT .Connections.

An ADODB connection is not the same as a TCP connection; unless
you have a documented promise, don't expect them to map one-to-
one. I don't know much about ADODB, but it's perfectly normal in
network programming to run multiple logical connections over one
TCP connection, or use multiple TCP connections to carry one
logical connection, or both at the same time.

>
> But it does NOT work without IDLE!

Odd. What happens if you run it from a command line?

> [...] But while I don't understand what's going on
> with the core part of the code this improvement doesn't much
> matter.

Do you want to be a network engineer? What's going on is too
many unsafe assumptions, and isolating the one (or ones) biting
you could take a lot of your time. My last version, with the
shutdown fix, is a pretty general TCP spy-proxy. The printing is
a bit dodgy, but other than that, I don't think it assumes
anything that TCP doesn't guarantee. (But don't let it walk
alone in crime-ridden neighborhoods such as the Internet.)

 

[Dennis Lee Bieber]

My today's tests (over LAN).
I think *it* will drive me mad very soon.

If going over a LAN, you have more things to consider.

1) are there any firewalls that might block a connection (in either
direction)

2) could the DBMS privilege tables be set up to reject connections
and/or commands from certain machines
--

 

[n00m]

Do you want to be a network engineer?

lol... definetely not! It's just my curiosity.
At my work my tools are: vba, vbs, jet-sql (ms access),
t-sql (ms sql server). The pretty humble set.

My first two guess are:
The client is trying to make more than one connection.
Putting accept in a loop and moving some stuff will support
that.

BUT your *1st version* does NOT support more than ONE client's
connection! How do I know that? I just ran several (3) instances
of my.vbs (almost) simultaneously and only the first of them could
connect to SQL Server via Python!
Just in case: SQL Server itself can accept up to 32767 clients.

Then (if I'm not wrong here) what's the difference with my own
version? Maybe replacing send() with sendall() will be quite enough
to make it working over LAN? I've NOT tested this!

PS Yes! Your last version works like a champ. It easily handles up
to 5 instances of my.vbs! Except of this thing:

AttributeError: 'module' object has no attribute 'SHUT_WR'

Seems it's a pure Unix constant.

Odd. What happens if you run it from a command line?

In a moment - AFTER I start my.vbs - in console window new prompt
"D:\>" appears... and vbs reports "General Network Error".

Dennis;
No no no! It's NOT firewalls trick. It's NOT db permissions issue.
VBS and SQL Server work with each other ABSOLUTELY FINE - from ANY
machine at ANY time at ANY free port - BUT WITHOUT Python being a
mediator for them.

 

[Bryan Olson]

> Your last version works like a champ. It easily handles up
> to 5 instances of my.vbs! Except of this thing:
>
>
> Seems it's a pure Unix constant.

No, my guess is that you're running an old version of Python.
The constant was added in the source on 27 Nov 2003; I'm not
sure what version that would first appear in. You can fix it
either by upgrading your Python distribution (a good idea
anyway), or replacing:

sock.shutdown(socket.SHUT_WR)

with:

sock.shutdown(1)

>
> In a moment - AFTER I start my.vbs - in console window new prompt
> "D:\>" appears... and vbs reports "General Network Error".

Hmmm... not much to go on there. I tested it on Win-XP, running
it from a command line. I set the server to ('www.googl.com',
80), and I was able to web-search through it.

Are you running XP's local firewall?

 

[Peter Hansen]

Bryan wrote:
PS Yes! Your last version works like a champ. It easily handles up
to 5 instances of my.vbs! Except of this thing:


Seems it's a pure Unix constant.

Definitely not. Are you sure you've got a proper Python install?

c:\>python
Python 2.4.1 (#65, Mar 30 2005, 09:13:57) [MSC v.1310 32 bit (Intel)] on
win32['AF_APPLETALK', 'AF_DECnet', ... 'SHUT_RD', 'SHUT_RDWR', 'SHUT_WR',
'SOCK_DGRAM', ... 'sys', 'timeout']
1

If you're really getting this error it would seem something is very
wrong on your machine.

-Peter