SQL Server 2000 - absolute beginner! Please help me

[Danzarak]

Hello there...
Pending receiving the full version from the higher powers at my work, I have downloaded the Evaluation version of SQL 2000, and set up an instance on my desktop machine. This was set up on an administrator account, and works fine.
My main aim with SQL 2000 is to upsize all the companies piddling little Access 97 databases into an SQL backend and keep the 97 frontends. I have migrated some of the tablesets into SQL, and linked to them in Access 97 and they work fine.
However, the problems are when I try and set up ODBC. When I enter ODBC as a different user on the server itself, the ODBC list of available SQL Servers shows me the correct server information - my PC ID no, followed by the server name. However, when I try and connect with another machine on the network, it only displays the PC ID no of the server, and not the server name. When I attempt to add the server with just the PC name, it doesnt find the server - same when I add the name of the database to the end of the PC ID no.
I'm sure there is something really basic I have not done, but I'm totally stumped now. I am using Windows Auth, and have set up the login of the other PC I am trying to connect, as an authorised user.
Please help me - I am just a doofus, please feel free to laugh at me...

[Mike John]

Not laughing - we all started somewhere!

First thing to check.

Are the machines members of a domain or not. Windows auth across machines requires a domain.

[Danzarak]

Yes they are all part of the same NT domain.
Cheers for not laughing, I have training for this booked for Jan, but a project to complete prior to them - IT eh.

[rmiao]

Did you install sql server on those pcs with problem? What's error message?

[Danzarak]

No I'm not going to be installing SQL server on those machines, only using ODBC to connect Access 97 databases to them - they are extremely tight for user licences in this company, and they refuse...
Hope I have understood/explained correctly.

[rmiao]

Do you have odbc driver for ms sql on those pcs?

[Jimdoe39]

try using system ODBC DSN ....

[rmiao]

Go to drivers tab to see if sql server driver is there.

[Danzarak]

Hi there,
I do have SQL drivers on the other PCs. I'm not sure what you are suggesting when you say "Use ODBC DSN"... please could you elaborate.

[rmiao]

Then create system dsn that points to your sql server.

[Danzarak]

Hi... I have tried to create a System DSN on the client machines, and have one set up on my server that works. However, I still am unable to "see" the instance on a remote machine - only the server name.

[rmiao]

Have to type it.

[Danzarak]

Hi... I'm afraid I have tried that too... and it doesn't work when you type it either.
I have tried it from a machine with SQL Server Client software on it, and the Client network utility picks it up on TCP/IP - server name and instance. However, it still won't set it up in ODBC.

[rmiao]

Choose tcp/ip netlib in odbc.

[Danzarak]

When I am configuring the Client configuration on ODBC, it is set to TCP/IP already, and to dynamically determine the port - the same as the one working connection I have set up. It still doesn't work.