Problem over VPN

[Firecat1970]

Dear experts,

I have a wierd problem and I hope some of you may have some clues for me.

I have a VPN between 2 database servers. One is Hong Kong, One is Europe.

The VPN is working file, I can copy files between the 2 servers.

If I open Query Analyser on HK server and connect to Europe server using sa login, I can connect, however I CANNOT execute any query, what I got was General Network Error after a minute or two.

If I open Query Analyser on Europe server and connect to Hong Kong server using sa login, I can connect, and I can execute query.

I am only 'select * from sysobjects'

Any idea what went wrong? According to my network guy, all ports on the VPN is opened.

Cheers!

 

[1DMF]

Is it a permissions problem?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[Firecat1970]

Nope, I am logging in as 'sa' of the other machine.

 

[1DMF]

yeah, but you can still change permissions on the SA account in EM under the security section, logins.

It was just a though if someone had tampered with the sa account and removed db_owner or access to any of the DB's

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[mrdenny]

ok, let's take away the tables (who knows how much data is in there, if there is blocking etc).

Try just a "select getdate()". It should return the date and time of the remote machine.

When you ping the remove machine what response time comes back?

Is this a new problem, new servers, or it's always been like this.

How about if you use osql to run the select statement? (QA adds some extra data transfer that may be screwing you up.)

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Firecat1970]

Thanks for your suggestion.

The 'select getdate()' works.
Ping also works.

The replication was always OK for years, it just broke all of a sudden. :S

I am still clueless.

 

[mrdenny]

If you can return data via a select getdate() then the server on the other end is working and can transmit data back to you.

Try returning a single record back from the sysobjects table.

select top 1 *
from sysobjects

If that works, start adding a record until it breaks.

If you can only pull down a few records I'd say there is something seriously funky going on with the network that's killing you.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Firecat1970]

Hi Denny, thank you for you suggestion.

Last week, our Firewall contractor came to do something and he did some test for me on this issue. One interesting thing he found out was, when he shrink the packetsize of a SQL connection, he could somehow see more result text coming back before it broke.

He applied the -a option of iSQL in command prompt.

If he used the default packetsize, thing broke pretty quickly. And usually the same query and a smaller packet size (e.g. 1000), he saw more result text and since the query would return a lot of result, it still broke but the result was quite different from the default packetsize.

I will further look into this area and wish me good luck.

 

[Firecat1970]

Dear experts,

I figured out again, it has nothing to do with PacketSize.

As long as the return result is small, it works. If the return result is larger, it would give the following error after halting for a minute or two.

DB-Library: Possible network error: Error in sending out-of-band data to SQL Server. General network error. Check your documentation.
Net-Library error 10054 : ConnectionWrite(send()).

Best Regards,
Firecat

 

[Firecat1970]

Dear experts,

Thank you for your many suggestion.

We seem to find the culprit. Our firewall on the other side of the earth. Now we change it from SideWinder to Netscreen, it works perfectly. :S

Thank you.

I would concur if we see "General Network Error" from SQL, it is really the network problem more than SQL.