Connecting to SQL Server 2005 on a server in a workgroup

[BobRoyAce]

NOTE: This is a modified repost of
http://groups.google.com/group/micr...t/browse_thread/thread/f50e59feea1d12ab?hl=en

I have a bunch of lcient PCs that are primarily running Windows XP and
Windows 2003 and they all belong to the same workgroup. In addition, I
have a server PC that is running SQL Server 2005 and it also belongs to
the same workgroup. I am writing a Windows Forms application, using
Visual Studio 2005, which needs to run on each of the client PCs, and
which needs to communicate with the SQL Server 2005 database server.
The connection string below, which works if I run the application on
the server box itself, does not work on the client PCs. What do I need
to change to make this work?

Data Source=R-SERVER\RSERVER;Initial Catalog=KERMIT;Persist Security
Info=True;User ID=bobrod;Password=toad4x;Connect Timeout=0;Packet
Size=4096

Note that the Machine name of the Server box is R-SERVER and the
instance
name I used for the SQL Server 2005 server is RSERVER. The SQL Server
is set up to allow for Local and remote connections using TCP/IP and
named pipes (tried just TCP/IP first with no success).

Someone also tried putting an lmhost file on the client PC to resolve
R-SERVER to an IP address but still no-go.

I changed the connection string to timeout after 60 seconds and it
does, in fact, timeout giving an error that it can't establish a
connection.

As mentioned, the connection string works just fine when run from the
server box.
Does the connection string need to be different in some way to run from
client machines?

 

[William \(Bill\) Vaughn]

Have you run the Surface Area Configuration tool to enable the ports and
protocols?


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[BobRoyAce]

Yes, ran Surface Area Configuration tool and made sure that Local and
remote connections was enabled. We tried TCP/IP only setting as well as
TCP/IP and named pipes setting...neither worked.

 

[BobRoyAce]

Bill:

I was just reading an entry in your blog, called "Getting and Staying
Connected," and shown below, and wonder if my problem might be related
to the issue described. It rings true with what I suspect is the
problem. Forgive my networking ignorance, but where would the DNS be
running? I'm suspecting it's provided by something running on the
Windows 2003 server box (on which SQL Server happens to be installed in
my case). Does the lmhost file that the networking guy put on the
client PC not ultimately serve the same purpose of helping to resolve
the server (put into Windows\system32\drivers\etc)? Is there something
we must install on the server box?

As an aside, I just played around with my home network configuring a
laptop running Windows 2003 to be named R-SERVER and have an instance
of SQL Server 2005 running called RSERVER. When I attempt to run the
application here at my house, I also get a connect failure. I have
turned off firewall software. Hmmm...

---BLOG TEXT BEGINS---
4. The DNS must be able to identify the named machine (it must resolve
to a valid IP address). If the DNS is not working, the server can't be
found. It might be necessary to alter the "hosts" file
(Windows\system32\drivers\etc) to map a specific machine name to an IP
address to circumvent this issue. DNS accessibility can be a function
of how the NIC is configured or the availability (state) of the domain
controller.
---BLOG TEXT ENDS---

 

[William \(Bill\) Vaughn]

When you work in a network that has a domain controller, the DNS (Directory
Name Service) resolves names to specific system (by IP address). In a home
network or one without a DNS service, there is no need for a DNS service as
all of the members of the workgroup are visible--assuming your systems have
file sharing enabled and have properly configured the systems. No, I have
not worked with Workgroup configurations in some time. I recommend that a
domain controller be established and used to manage the domain members and
the rights assigned there-to. Can you ping the other workstations (from a
workstation)? What do you see from the XP file explorer when you explore the
Workgroup? Have you tried disabling the firewall--at least temporarily? Make
sure that the firewall has ports open that permit the TCP/UDP ports to
communicate with the network.

hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[BobRoyAce]

Please see comments below. Besides those, I am wondering if it could be
a licensing issue. How can I check to see how the SQL Server software
was installed as far as licensing is concerned? What should I check and
whoat should be looking for?

When you work in a network that has a domain controller, the DNS (Directory
Name Service) resolves names to specific system (by IP address). In a home
network or one without a DNS service, there is no need for a DNS service as
all of the members of the workgroup are visible--assuming your systems have
file sharing enabled and have properly configured the systems. No, I have
not worked with Workgroup configurations in some time. I recommend that a
domain controller be established and used to manage the domain members and
the rights assigned there-to.

OK...assuming filesharing is enabled on the server box, which rights
have to be given to what?

Can you ping the other workstations (from a workstation)? yes

What do you see from the XP file explorer when you explore the Workgroup?

The client PCs have a drive mapped to the server and can see it from
Explorer through "My Network Places" where it shows up under the the
workgroup which shows up under "Entire Network\Microsoft Windows
Network.

Have you tried disabling the firewall--at least temporarily? Yes...

Make sure that the firewall has ports open that permit the TCP/UDP ports to
communicate with the network.

How do I do this? What are the actual ports that I need to open?

 

[William \(Bill\) Vaughn]

Use the SQL Server configuration manager to view the specific server port
configurations. Depending on the server and the instances you have
installed, these can be TCP1433 or other ports. If you've disabled the
firewall then port blocking should not be an issue. One other factor: did
you enable the SQL Browser service on the server?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[BobRoyAce]

SQL Server Browser service is running on the server. We shut off
firewalls on client and server. Still no go...any other ideas?

 

[BobRoyAce]

By the way, file sharing is turned on (server is a fileserver), but am
wondering what folders we need to give users permission to. Please
advise...