How to configure server machine to accept remote SQL server connections

I have a machine with SQL server running on it, and another machine that needs to access this data.
I am trying to set up a DSN on the remote machine to connect to this SQL server.

When I use the ODBC utility in the control panel, and attempt to add a System DSN, this SQL Server
does not seem to be visible.  The two computers are connected by a LAN.  The SQL Server uses
Windows Authentication.  From remote machine I can see the server machine's filesystem, though the
only visible folder is Printers.  I have created an account on the server machine with the same username
and password as the account on the remote machine.

What do I need to do on the server machine to make the SQL Server visible?

Incedentally, going the other way around works fine (i.e. from the server machine I can see an  instance of
SQL Server running on the remote machine).
WorldsTallestTreeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
wildcard76Commented:
Try tweaking the server network utility and client network utility that comes with the SQL installation..

hope this helps

Regards
0
 
WorldsTallestTreeAuthor Commented:
unfortunately I am using MSDE so don't have any utilities available
0
 
bharatbutaniCommented:
hmm ok WorldsTallestTree  there are a couple of ways to trouble shoot this particular problem

1) Try & ping the SQL Server from the remote machine, use the name of the server & the ip address both. If the ping is successful using the ip address only then there seems to be a problem with the DNS server cause its not resolving the names of the machines in the network.

2) You have mentioned that the SQL Server is using windows authentication , for the time being switch that to the mixed mode & use the sa password to connect to the server.

3) Try installing the SQL Server client on the remote machine & try registering the SQL Server.

Go thrugh the above steps & let us knw the results , if you still get access to the box or not , once you have the access  we can then troublshoot the other aspects.


Regards
Bharat Butani
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
nmcdermaidCommented:
One more to add....

try running this in a command prompt (Start/Run/CMD)

TELNET <sql server host name> <sql server port>



<sql server port> is usually 1433

0
 
bharatbutaniCommented:
ok how abt the ping report , you can alteast get that ..... from the command prompt.
0
 
WorldsTallestTreeAuthor Commented:
Bharat, thanks for your advice:

1. I ping'ed the server machine using the I.P. Address of the machine and the hostname of the machine.  The ping
was successful in both cases.  I am a little confused though about the distinction between the server machine (which has an IP address) and the instance of SQL Server running on the server machine (of which there may be more than one on the server machine - I presume each instance of SQL Server does not get its own IP address).

2.  How do I do switch to mixed mode?

3.  I'm not sure what you mean by the SQL Server client.  Is this some sort of GUI that comes with SQL Server for running queries?  I am using the MSDE, which is the free version of SQL Server with no bells and whistles.  It does come with osql.exe and a couple of other command line tools.

0
 
WorldsTallestTreeAuthor Commented:
OK the server machine has IP address of 192.168.16.206

I tried
      TELNET 192.168.16.206  1433

it returned "Connecting to 192.168.16.206...Could not open connection to the hot, on port 1433: Connect failed".

For good measure I then tried TELNET'ing to a third machine running sql server, which I am able to create a DSN for, and it seemed
to connect OK (the screen went blank and there was a cursor).

 
0
 
WorldsTallestTreeAuthor Commented:
Then I tried TELNET'ing to the server from yet another machine and it said

Connecting to 192.168.16.206...Could not open connection to host, on port 1433.  No connection could be made
because the target machine actively refused it.
0
 
bharatbutaniCommented:
WorldsTallestTree,

For example, consider a computer running one default instance and two named instances of the SQL Server Database Engine. The computer is configured such that the network addresses that the three instances listen on all have the same IP address. The default instance would listen on TCP port 1433, while the other named instances could listen on TCP ports 1434 and 1954, respectively.

Its the way you configure it to be .....

SQL Server is a Winsock application that communicates over TCP/IP using the sockets network library. The SQL Server listens for incoming connections on a particular port; the default port for SQL Server is 1433. The port doesn't need to be 1433, but 1433 is the official Internet Assigned Number Authority (IANA) socket number for SQL Server.

SQL Server Client Tool , contains the Query Analyser.... you use this to query the DBs....

Anyways lets have a look at the problem , so you dont have access to any of the client tools & it the MSDE Version ..... oopppssssss.... ok atleast you are able to ping to the box .... thats good , that means there is not network problem ......

Let me think will catch up soon ....


Regards
Bharat Butani.
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
bharatbutaniCommented:
If you saying that you could connect to some other SQL Server & not the one we trying to truble shoot then, in this case I beleive that maybe the port SQL Server is listening to is some other than the default 1433

Hey do you have access to the SQL Server Errorlog....In the Errorlog, you will see several lines that discuss what SQL Server is listening on.

Below is an example:
2006-01-04 01:41:07.65 server    SQL server listening on 10.254.1.150: 1433.     <--

I have never worked with the MSDE version ... lets seeeeeeee


HOPE FOR THE BEST


Regards
Bharat Butani.



0
 
nmcdermaidCommented:
... or there is a firewall blocking incoming connections on port 1433 on the server.



That gernerally happens by default.
0
 
mitchellm44Commented:
By any chance is the "Server" in a Domain, and the "remote" computer in a "Workgroup"..
That could create authentication issues.
0
 
WorldsTallestTreeAuthor Commented:
Guys,

apologies for slow response, i'm only in this office once a week.

Well it turned out that MSDE wasn't listening on any port!  This, it seems was because I was using MSDE SP3a for the faulty server, whilst I was using MSDE SP3 for the one that was working.  SP3a disables network access by default when you install it, and then there is it would seem no way of changing this short of unistalling MSDE and reinstalling it with the switch DISABLENETWORKPROTOCOLS=0.  I did this and it works ticketyboo!

anyway thanks for all your comments, and I'll give the points to bharatbhuruni as I found your comments most informative.

thanks


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.