ODBC connection issue

[ScottishFencer]

First of all I would like to apologise if this has been placed in the wrong forum.

I have a SQL Server 2k DB which has an Access 2003 front end. I have a number of users who would like to use it. The problem that I have is that only some users can access it. Those who cannot get an ODBC error:

"Connection to Server X has failed"

There are a number of apps on that server that these users CAN access - all have the same setup. The user's access rights appear to be correct and I have setup ODBC data link files that check out when I test the. I am a bit perplexed. Has anyone out there come across anything similar?

 

[SQLBill]

Do the users have accounts in SQL Server?

Are their machines Windows XP with SP2? If so, is the firewall on? Windows firewall automatically blocks port 1433, which is the default for SQL Server.

-SQLBill

Posting advice: FAQ481-4875

 

[ScottishFencer]

yes to the accounts on SQL server.

No to the XP issues - they are using windows 2k.

 

[SQLBill]

You say the ODBC connection works when you test it. Have you tried to run the application from one of the computers where it's not working?

-SQLBill

Posting advice: FAQ481-4875

 

[ScottishFencer]

Yes.

Sorry, perhaps I wasn't clear. I tested that the problem computer could be reached by ODBC by setting up a system DSN links in Data Sources. I got all the way through and tested it with an "OK" at the end. So the DB appears to be reachable.

 

[SQLBill]

Yes, you were clear...you stated you tested the ODBC connection. I understood that. But did YOU test the application itself? Did you RUN the application and get it to work? If you ran the application and it didn't work, that indicates the application is not using the users login credentials to access the database. In that case, you will have to set the connection up to use the application's credentials. If you can run the application, then there is something different between your login and the other users.

-SQLBill

Posting advice: FAQ481-4875

 

[ScottishFencer]

Hmm... Interesting.

I have attempted to run the application on the affected pc. It will NOT run from the problem pc - it will run from elsewhere.I will have a looksee if I can spot where the alternate login credentials are being entered.


FYI: I can log into the DB ok using my laptop. I can log in using another users log in. The user whose pc is affected by this can log into the application from another pc. I can't see where differing credentials are being taken from but your suggestion makes sense. This app is using AD credentials to log in.

 

[SQLBill]

The user whose pc is affected by this can log into the application from another pc.

Now we know it's not the user or your login credentials. We are left with:

1. the application using the wrong credentials. When a user logs into and runs the application, does the app ask for your login and password?

2. the application using the wrong ODBC connection. Does it ask you which connection to use?

Have you checked the SQL Server Error Logs for login errors or any other errors that might pertain to this?

Have you checked the Windows Event Viewer logs both on the users computer and the SQL Server?

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

One other thought from experience....is this application something that was installed on the computer or is it a script that you provided?

Some of my users use Crystal Reports and I provided them a 'template' for their desktop. They click on the template and it asks them for the parameters for the report (Dates, times, etc). Then it runs the report. Sometimes this fails and they have to delete their template and have me send them the template again.

-SQLBill

Posting advice: FAQ481-4875

 

[ScottishFencer]

Thanks SQLBill. I will check your suggestions. There is nothing in Event Viewer on the target pc that is showing up as an error - neither ODBC or Office. As the error message appeats as a dialogue from Access I didn't think to check the SQL logs (slaps head) I'll check that tomorrow when I am back in the office.

The application does ask for a login but this is a simple login based on a table in the app itself. You can bypass it and still use the app. The login credentials are not passed to the server. For example 'I' can use the app on my laptop but not on the problem pc.

The app doesn't ask which ODBC connection to use. But I am intrigued by the possibility that it is using the wrong one. Again I'll check tomorrow.

The application is an Access 2003 front end on a SQL backend. It's written in normal VBA. The application itself is just an MDB.

 

[SQLBill]

It is sounding like the application might have the 'credentials' built-in to it. Can you remove the application from the 'problem' computer and reinstall it? Maybe with a copy from a computer where it is working?

-SQLBill

Posting advice: FAQ481-4875

 

[timscronin]

Is it using nt or sql authentication. Load sql client tools on failing machine, make sure correct protocols are loaded and test connection with QA. THis might very well be an MDAC issue. You can download the MDAC checker from MS to verify this

 

[ScottishFencer]

I'll check out the MDAC tools.

It's using NT authentication. In fact there is a script that places a copy of the app onto the user's machine.

This has been quite an interesting thread. I suppose 'you learn something new everyday'. Never seen this happen before.

 

[timscronin]

If it is using NT/SQL load the client tools and make sure multi-protocol is an enabled protocol. While you are having fun, make sure you can ping the db server from the failing machines.

 

[ScottishFencer]

... make sure you can ping the db server from the failing machines.

No neeed - I already checked that.

 

[ScottishFencer]

Thanks guys. Looks to be a MDAC issue. Thanks for your help. I've never come across this issue before so it has been filed away as "useful knowledge".

Thanks again.