Connect to a remote DB server

Hello everybody.

I have two computers at home and both have MS SQL Server 2000 Personal Edition installed. I use windows authentication and I want to access one db server from another computer. When I'm trying to add a new server registration, I can see the server I'm trying to connect to, but at the end I have an error: "Login failed for SERVER_Name\Guest". How can I fix it?

Thank you,
Yurich
LVL 21
YurichAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

nmcdermaidCommented:
To implement windows authentication between machines (which is always a worthy cause), one of the following needs to be done:

1. Ensure both computers are part of the same domain/trusted accross domains

This doesn't seem worthwhile for just two computers!

2. The exact same logon and password must be set up on both computers.

Sounds silly hey but without a central domain to authenticate to its the only way to do it.
YurichAuthor Commented:
thank you for your reply,

1. Both computers are in the same workgroup. One is XP Pro, another one is XP Home Edition. I'm not sure how to setup a domain for them both.

2. I didn't quite understand this one. They in fact don't have any passwords - they use all default settings, they of course have different names and different logins for loggin into the Windows but apart from that, there's no authenitaction.

Any way to have it done? I mean in theory I would suggest creating probably an account on my db server (the one I'm trying to access) for another computer to access, I'm just not sure how to do it...

Regs,
Yurich
YurichAuthor Commented:
OK, got it working.

Here is the steps to perform it:

1. On the db server setup a new login in the Logins of the Security folder - can be any name, use SQL server authentication as Windows authentication will work only for the domain computers

2. In the 'Database Access' tab mark all databases that will be accessible from another computer and set (tick) this login as 'Owner' (I tried public and couldn't see conents of any table)

3. On the client computer, create new registration, and select SQL server Authentication - use credentials that you created in the step 1.

4. The registration should complete with no errors or warnings and now you can access databases specified in the step 2 from the client machine.

That's it ;)
Yurich
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

nmcdermaidCommented:
1. Like I say don't bother setting up a domain if you only have two computers.
2. Are you saying when you log into windows as that user, you don't type in a password?

So you actually got it working with SQL Server authentication instead. That is the most practical method for your setup.
YurichAuthor Commented:
2. No, I don't - do you type a password after you started your home pc?

I actually didn't care how - windows authentication was set by default - I just cared about connecting.

Regs,
Yurich
nmcdermaidCommented:
I don't mind refunding the points but perhaps Yurich you could be a little clearer when asking questions in future. If you reread your original question then you might see that it could be a little ambiguousas to what you are asking for.
YurichAuthor Commented:
I'm never mingy for the points and I would give 'em to anyone who'd help me. Sorry if my question was not clear. I needed instructions how to do it (as I posted in my own solution) and whether it would be done with windows authentication or with sql securities didn't matter to me - at least if something was suggested (beside of not adding my two home computers into the domain where there weren't anyway or setting the same login, which sounded rather strange and probably would create collision in the network).

Regards,
Yurich
nmcdermaidCommented:
If it had been clear that SQL authentication was an option that is the first thing I would have suggested. Anyway it doesn't matter, I'm glad you managed to resolve it yourself.
GranModCommented:
Closed, 500 points refunded.
GranMod
The Experts Exchange
Community Support Moderator of all Ages

Experts Exchange Solution brought to you by

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.