Access front end SQL Server backend connection problems 2

[slames]

Hi,
I am hoping that someone will be able to help with my problem.
I have an Access 2000 front end which connects to a SQL Server 2000 back end. In total we have about 15 users, half of whom can access the database, half can't. I'm not sure where the problem is originating from as one of the computers that can't connect to the database tables etc is using a log on (to windows) that has access to it on one of the other computers. Each user is logging on to the database via SQLSever authentication as it is located on a different domain (it will soon move to the same domain, but has not yet been done).
The error I receive when testing the connection is unknown server, uanable to establish a connection.
I'm baffled as the same application on a different computer with the same log in and authentication connects without problems. The other thing is that when I log on as local administrator on to the computer that won't work with a user log on, I can access the application and connect to SQLServer without any problems.

Does anyone have an idea what might be causing this.

Any ideas gratefully received.

Thanks

Steph

 

[gojohnnygogogogo]

can you ping the sql server from the computers ?

 

[slames]

Yes, it is possible to ping the server from the computers unable to connect to it...

 

[gojohnnygogogogo]

I was going to say maybe the computer is not in the lmhosts file for those users.
but not sure now if thats the case.
are the odbcs connecting via tcpip or namedpipes ?

 

[slames]

Sorry to appear dim here, but how do I tell if they are connecting via tcpip or named pipes?

Thanks for your help so far.

 

[gojohnnygogogogo]

go into your odbc, click configure then client configuration

 

[gojohnnygogogogo]

are these computers connecting via odbcs ( dsns )

go intothe odbc, configure, then click client configuration

 

[slames]

Thanks - they are connecting via tcpip, have just realised - this seems to be affecting all of the windows 2000 machines, all those on xp are working ok.

 

[gojohnnygogogogo]

don't know if this will work, but its worth a try, can you install the latest MDAC onto the 2000 computers. ?
This will give you uptodate odbc drivers.

 

[slames]

I'll have a go and get back to you, Thanks for your suggestions so far.

Steph

 

[slames]

I should probably add that a week or so ago these computers were all runnng perfectly with exactly the same application file. In the meantime all the users' computers have been moved on to a new domain (so a number of profile settings were lost), whilst the sql server computer remains on the old domain. I don't know if this will change anyone's thoughts on the problem.

 

[SQLBill]

Are the XP and 2000 machines all on the same domain? Or are the XP ones still on the same domain as SQL Server and the 2000 machines on a different domain?

Is there a firewall between the 2000 machines and the SQL Server machine?

-SQLBill

 

[slames]

Yes, there is a firewall between the users computers and the sql server. This also exists between the xp machines and the sql server.

 

[SQLBill]

Is it the same firewall between the 2000 machines and SQL Server as between the XP ones and SQL Server?

If it's a different one, what TCP port are you using for SQL Server (default is TCP 1433). That might be blocked on the firewall that is between the 2000 machines and SQL Server.

If it's the same one, check that the IP addresses for the 2000 machines aren't being blocked.

-SQLBill

 

[slames]

Hi, yes all computers are on the same domain and going through the same firewall, on port 1433. I will check and see if the IP addresses are being blocked.
Thanks

 

[slames]

Thinking about it, I don't think it can be related to the ip address as when I log on as administrator on that machine I can connect to sql server no problem.

 

[SQLBill]

Correct me if I'm wrong on any of this....

You have two types of computers - XP and 2000
SQL Server uses Windows Authentication
You are using MS Access to get data from the SQL Server.
A user (user A) can get data from SQL Server when using an XP machine.
The same user (user A) can not get data from SQL Server when using a 2000 machine.

Correct so far?

Also, you didn't answer my question about the domain(s). You said SQL Server is on the old domain (call it DomA). But you didn't say which domain the XP and 2000 systems are on. Is XP on DomA and 2000 on DomB (new domain)? Or are they both on the same domain (DomB)?

-SQLBill

 

[slames]

Thanks for your continued help on this. All of your statements are correct except I am using SQL Server authentication not windows authentication to access the SQL Server, as the users who are on the new domain.

So in answer to your second bit SQL Server is on the old domain (DomA) and all users, xp and 2000 are on DomB. Hope that clarifies things.

Thanks

 

[SQLBill]

Sorry I missed that about the SQL Server authentication.

Let's test something. First, do you have a non-admin account? If not, create one (something like login= TestLogin). Test it using the application on an XP machine then a 2000 machine. It should fail on the 2000 machine (obviously). Next find adminstrative tools...you can probably find it via Control Panel. Then find the Data Source (ODBC) icon. Click on that and create an ODBC connect to the SQL Server (this will test to see if it's just a connection problem, or if it's a problem connecting using the application).

If you can connect using just the ODBC connection wizard and not by using the application, then there's something in 2000 blocking the application. Check the security settings.

-SQLBill

 

[slames]

Thanks everyone for all your help on this. I have now figured the problem. It was a combination of stuff, the ODBC DSNs had been wiped and the MDAC needed updating. So Thank You!