?
Solved

How to configure server machine to accept remote SQL server connections

Posted on 2006-05-16
13
Medium Priority
?
282 Views
Last Modified: 2012-06-21
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).
0
Comment
Question by:WorldsTallestTree
  • 5
  • 4
  • 2
  • +2
13 Comments
 
LVL 3

Expert Comment

by:wildcard76
ID: 16696532
Try tweaking the server network utility and client network utility that comes with the SQL installation..

hope this helps

Regards
0
 

Author Comment

by:WorldsTallestTree
ID: 16696564
unfortunately I am using MSDE so don't have any utilities available
0
 
LVL 3

Expert Comment

by:bharatbutani
ID: 16696619
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 30

Expert Comment

by:nmcdermaid
ID: 16696668
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
 
LVL 3

Expert Comment

by:bharatbutani
ID: 16696710
ok how abt the ping report , you can alteast get that ..... from the command prompt.
0
 

Author Comment

by:WorldsTallestTree
ID: 16696747
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
 

Author Comment

by:WorldsTallestTree
ID: 16696784
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
 

Author Comment

by:WorldsTallestTree
ID: 16696825
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
 
LVL 3

Accepted Solution

by:
bharatbutani earned 2000 total points
ID: 16696857
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
 
LVL 3

Expert Comment

by:bharatbutani
ID: 16696873
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
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 16697297
... or there is a firewall blocking incoming connections on port 1433 on the server.



That gernerally happens by default.
0
 
LVL 2

Expert Comment

by:mitchellm44
ID: 16698100
By any chance is the "Server" in a Domain, and the "remote" computer in a "Workgroup"..
That could create authentication issues.
0
 

Author Comment

by:WorldsTallestTree
ID: 16749013
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question