I have posted this in the DB/SQL Server area - my apologoes for posting it here. I got no joy there and believe this is a triky problem.

My objective is to connect to a remote SQL 2000 instance running on win 2003. I have connected to a remote instance many times successfully but never on a 2003 machine - Is there any difference?

It took me a large part of yesturday to figure out (command line net stats) it is not listening on port 1433 at all. The next step was to attempt an ODBC connection. Using tcp-ip no luck, using a named pipe connection I had success.

Can I make an OLEDB named pipe connetion? If so an exmple string would be great.

BTW : In the Client Network Utility I have told it to use TCP-IP and use port 1433 - It is still not listening. If I put the web pages on the same box a normal OLEDB connection to its self works fine.

I am going a bit nuts on this one, has anybody connected remotly under these circumstances?



Wind is your friend
Matt

__________________
Wind is your friend
Matt

Hi Matt,

I've never connected to a 2003 server, but you can force Named Pipes by adding Network Library=dbnmpntw to you conn string.

See http://support.microsoft.com/?kbid=238949 for all the options.

hth
Phil

pgTips

Thank you for your suggestion however I couldnt get that method to work. after almost a day the solution is as follows:

1..Installing SQL Server SP3a forces post 1443 to listen
2..Open the Client Network Manager, disable Nammed Pipes, cick on alias, create a tcp-ip connection, click into it,UNTICK dynamicly choose port and specify 1433

Now a normal OLEDB (TCP-IP)connection is accepted.

Wind is your friend
Matt

For the purpose of assisting others with this problem/solution I wish to add to the scenario. BTW my solution above is a result of reading MS articles and allot of trial and error.

We have 2 x 2003 servers here. 1 x internal only (has SQL Server) and 1 x external (No data store) To eliminate any potential firewall etc problems (correct, I am not very network savvy), I was testing this remote connection from my dev machine (third machine XP) to the internal only 2003 machine. Both of these machines have SQL Server installed and a duplicate set of web pages. After implementing the solution in my previous post I had remote connectivity between these two machines (my dev machine and the 2003 internal only box) using:

"Provider=sqloledb;Data Source=xx;Initial Catalog=xx;User Id=xx;Password=xx;"

When I tried to connect from the external 2003 machine to the internal only 2003 machine I was getting the old 'sql server does not exist or access denied' error (original problem and error)

After determining there was no firewall or other in between problems, trial and error resulted in the following connection string working:

Provider=sqloledb;Data Source=machineName,1433;Initial Catalog=xx;User ID=xx;Password=xx;

Yes, problem solved. Why do I have to specify the port when the machine holding the DB has been told to accept connections there?

Another question came up through this process. The .1 part of some connection strings EG:
Provider=SQLOLEDB.1
What is this in reference to and how do I find out what version a particular instance in running - therefore what dot/integer to include in a connection.

My third question directly relates to the input/suggestion by pgtips's. Are Network Librarys EG "Network Library=dbmssocn;" optional in connection strings? or is the one I have listed above the default (being TCP-IP) therefore can be left out of the string. NOTE: the connection I attempted to make including the named pipe library failed to work ("Network Library=dbnmpntw").

As you can see I still have some gray areas going on this topic. I would appreciate any opinions/answers very much.

TYIA


Wind is your friend
Matt