Re: Fix of problem connecting VS2005 to remote Sql 2000 server

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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.



"RvGrah" <rvgrahamsevatenein@xxxxxxxxxxxxx> wrote in message
news:1141751195.863891.308390@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
| 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
|


.

Relevant Pages

  • Re: Merge replication in SQL Server
    ... However, port 443 is for https, did you want your SQL ... Configuring an instance of SQL Server to use a static port ... you can script out replication jobs by right clicking on a publication ...
    (microsoft.public.sqlserver.replication)
  • Re: IIS, SQL 2000 & XPs Firewall
    ... Will anything change when I install SQL 2008 on the laptop? ... Queries to the Data Engine must go to the port that SQL Server is ... More info: How to: Configure a Windows Firewall for Database Engine Access ...
    (microsoft.public.sqlserver.connect)
  • Re: Access 2007->SQL Server2005 "connection was forcibly closed",G
    ... I moved every table I was able to move to the SQL ... closed connections - but all of these errors are in the version which used ... the SQL Server 2000 and everything worked ... communication between ODBC (OLEDB and Native Client, ...
    (microsoft.public.sqlserver.connect)
  • Re: hack using xp_cmdshell
    ... > Fortunately 14 years of SQL experience, and a little common sense, would ... > should run it on a different port and just have my developers connect to ... Tibor Karaszi, SQL Server MVP ... >> install SQL Server in Windows Only mode and then Switch down to Mixed ...
    (microsoft.public.sqlserver.server)
  • Re: connecting to sql svr express over internet
    ... also add the prefix tcp: to specify the TCP protocol (or np: for named ... of the server and the protocol and the port to be used. ... See SQL Server Configuration Manager | SQL Native Client Configuration | ... I have Sql Server 2005 Express edition installed on a server. ...
    (microsoft.public.sqlserver.server)