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
|