Fix of problem connecting VS2005 to remote Sql 2000 server

  • Thread starter Thread starter RvGrah
  • Start date Start date
R

RvGrah

After much hair-pulling, I've finally found the answer to a problem
that many are fighting with, difficulty connecting from Sql 2005 Server
Management or VS2005 to a remote Sql Server running Sql 2000. In my
case the server I couldn't reach was across a vpn connection, behind
ISA server 2000, (which had port 1433 open).

The answer was in this post:
http://groups.google.com/group/micr...k=st&q=dotnet+vs+2005+connect+sql+2000&rnum=8

(See the 10th post in the thread, by Pablo Castro).

Adding the simple prefix "np:" in front of the server name fixed the
problem immediately. Why didn't Microsoft hire blimps and sky-writers
to publicize this hugely frustrating change?

Hope this helps someone.

Bob Graham
 
| After much hair-pulling, I've finally found the answer to a problem
| that many are fighting with, difficulty connecting from Sql 2005 Server
| Management or VS2005 to a remote Sql Server running Sql 2000. In my
| case the server I couldn't reach was across a vpn connection, behind
| ISA server 2000, (which had port 1433 open).
|
| The answer was in this post:
|
http://groups.google.com/group/micr...k=st&q=dotnet+vs+2005+connect+sql+2000&rnum=8
|
| (See the 10th post in the thread, by Pablo Castro).
|
| Adding the simple prefix "np:" in front of the server name fixed the
| problem immediately. Why didn't Microsoft hire blimps and sky-writers
| to publicize this hugely frustrating change?
|

Well, they did.... look at:
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx

or your local VS2005 MSDN docs.

Willy.
 
|
|
| || After much hair-pulling, I've finally found the answer to a problem
|| that many are fighting with, difficulty connecting from Sql 2005 Server
|| Management or VS2005 to a remote Sql Server running Sql 2000. In my
|| case the server I couldn't reach was across a vpn connection, behind
|| ISA server 2000, (which had port 1433 open).
||
|| The answer was in this post:
||
|
http://groups.google.com/group/micr...k=st&q=dotnet+vs+2005+connect+sql+2000&rnum=8
||
|| (See the 10th post in the thread, by Pablo Castro).
||
|| Adding the simple prefix "np:" in front of the server name fixed the
|| problem immediately. Why didn't Microsoft hire blimps and sky-writers
|| to publicize this hugely frustrating change?
||
|
| Well, they did.... look at:
|
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx
|
| or your local VS2005 MSDN docs.
|
| Willy.
|
|

Just forgot to add that when using named pipes as protocol, something you
shouldn't do on a corporate network, you are using port 445 at the server,
so you need to open this port at the firewal or NAT as well. Anyway your
issue is not related to the ISA sever, it's just that you have set-up your
SQL server to use named pipes as protocol, something that you need to
indicate in your ConnectionString, or by specifying the protocol in your
Server variable (data source=np:...) or your Net variable (...;
net=dbnmpntw).

Willy.



Willy.
 
How should you connect to a remote Sql server across a vpn connection
if you shouldn't use the "np:" named pipes prefixing? (I am in a
working across corporate networks, although we're talking about quite
diminutive corporations.)

Or when I'm in Sql Management Studio and wish to push some database
tables to our branch office (not really a branch, more of a clone
company in another state that shares the same software development and
database design). In Sql Server 2000 Enterprise Manager, if I just made
a vpn connection to the remote office, I could copy tables or stored
procedures etc from server to server without any special effort. Is
using np:<servername> not the right way to do it using 2005 tools? I
want to use the newer interface to get accustomed to it, and because it
offers superior tools for copying tables, as a for instance.

Any further guidance appreciated.

Bob
 
| How should you connect to a remote Sql server across a vpn connection
| if you shouldn't use the "np:" named pipes prefixing? (I am in a
| working across corporate networks, although we're talking about quite
| diminutive corporations.)
|
| Or when I'm in Sql Management Studio and wish to push some database
| tables to our branch office (not really a branch, more of a clone
| company in another state that shares the same software development and
| database design). In Sql Server 2000 Enterprise Manager, if I just made
| a vpn connection to the remote office, I could copy tables or stored
| procedures etc from server to server without any special effort. Is
| using np:<servername> not the right way to do it using 2005 tools? I
| want to use the newer interface to get accustomed to it, and because it
| offers superior tools for copying tables, as a for instance.
|
| Any further guidance appreciated.
|
| Bob
|

Use the TCP protocol over port the default SQL server port 1433. Named pipes
use the SMB protocol (over port 445), which is good for 'direct' connections
or where client and server are on the same box, for anything else you should
set-up SQL server to use TCP (SQL server uses port 1433 by default). Note
that named pipes is not a new protocol (the first SQL version had it
already), I'm also not clear on what you mean by ....superior tools...,
named pipes are no tools, they are protocol end-points like sockets are for
TCP

Willy.
 
Willy,

I'm not referring to the named pipes vs tcp when I refer to "tools", I
mean VS2005 and Sql server 2005 tools versus VS2003 and Sql 2000. The
method that worked in the older version to connect to a server in
another Lan doesn't work in the 2005 versions without setting the "np:"
prefix to force named pipes, as Pablo on the Ado.Net team recommended.

I'm also a little confused, because the ISA server at the remote server
has port 1433 open, and always allowed Vs2003 and Sql 2000 Enterprise
Manager to funtion fine. I don't see port 445 open anywhere, either in
IP Packet Filters or in Protocol Definitions, so I'm a little mystified
as to why adding the np: prefix worked for me instantly both in VS and
Sql Management Studio, where all attempts to go by just the server name
(I assumed using tcp) failed.

This is all so deep, and quite hard to master for somone who's handling
everything from hardware issues to Sql admin and database front-end
development in addition to many other things... I know the number of
hats I'm wearing does not constitute emergency on your part, I'm just
trying to get up to speed with the 2005 tools at least to the extent
that I was functioning well in 2000/2003 tools.

Regards, Bob
 
| Willy,
|
| I'm not referring to the named pipes vs tcp when I refer to "tools", I
| mean VS2005 and Sql server 2005 tools versus VS2003 and Sql 2000. The
| method that worked in the older version to connect to a server in
| another Lan doesn't work in the 2005 versions without setting the "np:"
| prefix to force named pipes, as Pablo on the Ado.Net team recommended.
|
| I'm also a little confused, because the ISA server at the remote server
| has port 1433 open, and always allowed Vs2003 and Sql 2000 Enterprise
| Manager to funtion fine. I don't see port 445 open anywhere, either in
| IP Packet Filters or in Protocol Definitions, so I'm a little mystified
| as to why adding the np: prefix worked for me instantly both in VS and
| Sql Management Studio, where all attempts to go by just the server name
| (I assumed using tcp) failed.
|
| This is all so deep, and quite hard to master for somone who's handling
| everything from hardware issues to Sql admin and database front-end
| development in addition to many other things... I know the number of
| hats I'm wearing does not constitute emergency on your part, I'm just
| trying to get up to speed with the 2005 tools at least to the extent
| that I was functioning well in 2000/2003 tools.
|
| Regards, Bob
|

"np:servername" is a shortcut for "Net=dbnmpntw", herewith you 'force' the
client to use the named pipe protocol to connect to the SQL server. If you
don't specify np or dbnmpntw, the client will use what is configured as
default client protocol(s) (see SQL2005 - SQL Server Configuration Manager)
to connect with SQL server, I assume the default enabled protocol is shared
memory, the other possible protocols are TCP/IP, Named pipes and VIA.
Now, suppose you have shared mem. and TCP enabled in that order. When a
client tries to connect it will use shared memory is the server name is
local, but it will use TCP/IP when the server is remote (using port 1433),
but this isn't working in your case so my guess is that, or, TCP/IP is not
enabled at the SQL server side, or, that you don't have the TCP/IP enabled
at the client, so what you have done is forced the client to use named pipes
network library, but you could also have enabled TCP/IP or Named pipes at
the client using the "SQL Native client configuration" in SQL Server Config.
manager.
For you port 445 issue, rest assured that named pipes use port 445, you can
check this by issuing a netstat -a command at the server, when a client is
connected you will see that the connection is established. Note that this
same port is used by the File server as well, so make sure no clients are
connected to a share on the server.

Willy.
 
Still wrestling....
The remote machine is an sbs 2000 machine (win2k server, isa 2k, sql
2k). Port 1433 *Inbound* open and defined by isa itself. From what
Pablo said in the other thread, it seems that all this time working
with Sql 2000 Enterprise manager and Visual Studio 2003 they have
implicitly been jumping to named pipes because tcp didn't work, without
"telling" me...
Now with Sql 2005 Management Studio and VS 2005, I'm being forced to
deal with this more granularly, in other words, know what protocol I'm
using and figure out why it's not working, in the case of tcp at least.

If I'm following what you're saying, tcp is higher performing and more
secure. In case there was an issue with two way communcation, I've
opened port 1433 *outbound* on the remote machine, but that made no
difference. I've also checked that the remote machine's sql server 2k
has tcp enabled in network configuration. There doesn't seem to be a
way to change the priority of enabled protocols, should I try disabling
Named Pipes? How else can I troubleshoot tcp to through this vpn
connection? Using the ip address of the remote server doesn't work, nor
does using the vpn's 10.1.1.1 ip address. I'm able to open a my
computer window to the remote machine and browse it's folders (with a
windows xp vpn connect open). I can open a connection from VS 2003 and
Sql 2000 just fine, as mentioned earlier. It's only in VS 2005 and Sql
Manager 2005 that I can only connect if I "force" named pipes.

Just to confuse the issue a bit more, I'm doing all this from behind an
sbs server at my local site that's configured pretty much the same way.
When I connect from home, using the np: prefix doesn't work, only using
the sa id and password in the connection string works. The vpn logon
I'm using is the win2k administrator logon, with full priveleges. I
don't need this functionality for my users, I only need it for
development purposes. The finished versions that I deploy to my users
at both locations only need to function within their local respective
lan's. There is a possibility that I will be asked to provide the owner
with versions that function remotely, however.

I appreciate your help and you definitely have my attention.

Bob
 
Don't know about VS2003, but as far as I know SQL2000 EM should work over
tcpip (could be wrong though), anyway we aren't talking about VS2003 or EM
here, this is about VS2005 and SQL2005 Management Studio right?
Make sure your SQL server is listening on port 1433, that is, on service
port 'ms-sql-s' (1433), you can verify this by issuing a "netstat -a -b"
command on the server. If you have a listener on port 1433, before you try
to connect using SQL client stuff, you can try to connect using telnet (from
the command line);
"telnet servername 1433", when SQL accepts the connection the command window
should have been cleared, else you'll get an error message. Once this works
you can move on, else you are dealing with network issues, so you have to
solve them first.

On the client, you need to make sure you connect to the SQL server using the
IP hostname, not the instance name, or a configured alias. You can verify
and configure this by running the "SQL Server Configuration Manager" (SQL
2005 tools must be installed at the client).
In the "SQL Native Client Configuration", select Client Protocol and make
sure TCP/IP is enabled, the order defines the priority in which the protocol
is selected in an attempt to connect, that means the first protocol (order
1, the default) is tried first. Make sure the port is set to 1433 (or to the
listener port of the SQL server if different from the default port number).
You can also define an alias (or a number of aliases) and configure the
server name, port and protocol corresponding to this alias. The alias can be
used by all client tools (sqlcmd.exe , VS2005, SQL Management Studio) and by
your application, by specifying the alias as "Server" or "Data Source" in
the ConnectionString instead of the host name.

Willy.

Note that this really belongs to the SQL NG, so I would suggest you post
further question over there.



| Still wrestling....
| The remote machine is an sbs 2000 machine (win2k server, isa 2k, sql
| 2k). Port 1433 *Inbound* open and defined by isa itself. From what
| Pablo said in the other thread, it seems that all this time working
| with Sql 2000 Enterprise manager and Visual Studio 2003 they have
| implicitly been jumping to named pipes because tcp didn't work, without
| "telling" me...
| Now with Sql 2005 Management Studio and VS 2005, I'm being forced to
| deal with this more granularly, in other words, know what protocol I'm
| using and figure out why it's not working, in the case of tcp at least.
|
| If I'm following what you're saying, tcp is higher performing and more
| secure. In case there was an issue with two way communcation, I've
| opened port 1433 *outbound* on the remote machine, but that made no
| difference. I've also checked that the remote machine's sql server 2k
| has tcp enabled in network configuration. There doesn't seem to be a
| way to change the priority of enabled protocols, should I try disabling
| Named Pipes? How else can I troubleshoot tcp to through this vpn
| connection? Using the ip address of the remote server doesn't work, nor
| does using the vpn's 10.1.1.1 ip address. I'm able to open a my
| computer window to the remote machine and browse it's folders (with a
| windows xp vpn connect open). I can open a connection from VS 2003 and
| Sql 2000 just fine, as mentioned earlier. It's only in VS 2005 and Sql
| Manager 2005 that I can only connect if I "force" named pipes.
|
| Just to confuse the issue a bit more, I'm doing all this from behind an
| sbs server at my local site that's configured pretty much the same way.
| When I connect from home, using the np: prefix doesn't work, only using
| the sa id and password in the connection string works. The vpn logon
| I'm using is the win2k administrator logon, with full priveleges. I
| don't need this functionality for my users, I only need it for
| development purposes. The finished versions that I deploy to my users
| at both locations only need to function within their local respective
| lan's. There is a possibility that I will be asked to provide the owner
| with versions that function remotely, however.
|
| I appreciate your help and you definitely have my attention.
|
| Bob
|
 
Back
Top