SQL Server Domains/Workgroups

Question

SQL Server Domains/Workgroups

bobbabuoy

Old Hand

Points: 378
More actions
December 9, 2005 at 8:00 am

#111024

I have a network that has 10 machines-6 of them have sql server registrations. I am able to access all 6 from any machine while they are on the domain. However I also need to take 5 of them off of the domain to a remote site and set up a workgroup. I am able to log in to each sql server from the workgroup login but I am not able to run software on one machine that connects to a sql server registration on another machine in the workgroup. I get a "sql server does not exist or access denied" message. Further, when I open enterprise manager, the other sql servers don't show up and I am unable to add them using the wizard.
Any help would be much appreciated.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

Since the machines are in a workgroup, there is no trust between any of the machines. Therefore, Windows accounts cannot be verified. Ensure that the username/password combination you're logging in with exists on all machines you need to talk to. Since SQL Server can't authenticate the username/password with a domain controller, it will do so locally. If it finds a match in this manner, you're okay. Otherwise, you must resort to SQL Server logins. Either case, you're setting up multiple accounts across several servers.

Is there any possibility of setting up a domain at that remote site?

K. Brian Kelley
@kbriankelley

bobbabuoy Old Hand Points: 378 More actions · Answer 2

Thanks but there is no chance to use a domain controller at the remote site. I thought that if I used "sa" and the associated password that I would be ok with my databases set up in mixed mode. Not so? Here is the code that I am trying to access the db on one machine from another:

conn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=VIRA;UID=sa;Pwd=my_pwd;Data Source=" & sSource

I am pretty sure that the same password exists in all sql server regsitrations. If so then what do I have to do?

Thanks~

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

First, scrub your post to erase the password. Second, verify the sa account password is what you think it is. With that done, if they're all in the same workgroup they should see each other if they are all on the same network. The computer browser service can take care of that. However, if you try to connect based on the IP address, do you get the same results?

K. Brian Kelley
@kbriankelley

bobbabuoy Old Hand Points: 378 More actions · Answer 4

Password hidden. I have verified that the password is the same on all machines in the workgroup. I am now getting a message that says "Login failed for computer_name\Guest"

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 5

Make sure the guest account for OS is disabled. Also, get rid of the following part of your connection string:

Integrated Security=SSPI

This tells the client to try and make a Windows connection.

K. Brian Kelley
@kbriankelley

bobbabuoy Old Hand Points: 378 More actions · Answer 6

Ok I am now connecting from machine to machine but it is REALLY SLOW-to the point where the system times out prior to connecting. I am connecting to the machines via a visual basic application and here is my connection string:

conn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;Initial Catalog=VIRA;UID=unknown;Pwd=unknown;Data Source=" & sSource

What would cause that to operate so slowly?

Thanks~