authentication

[JohnE]

Hello. I have a situation in which I am working on an A2003 app (front end)
that is connected to a SQL 2005 backend. This app is also being looked at
going over to A2007, but still remain (if possible) a mdb file.

What I am running into is the notice that keeps asking for the sql ID and
password. The database application is linked to data tables that actually
reside on a different server, for example on one of our SQL servers. Once the
initial message box is closed, the reason the login failed is then displayed.
This is the dialog box that has the checkbox for trusted connection, ID,
Password, etc. Access is trying to log in to the SQL server using the name
used to log in to the computer (windows authentication). Most of our SQL
Server databases require their own login names and passwords. The checkbox
next to "Use Trusted Connection" is cleared and then you can enter the
correct name and password in the corresponding boxes.

This is not how this inherited db app has worked. In A2003, everything
worked just fine on the connections, etc.

The A2003 app has been around for 10+ years. It was A97 before A2003. Now
A2007 is being moved out to the company in Q4 and this app needs to be ready
to run on it.

Has anyone also had this dilemma and if so how did you resolve it? I am at
a loss here and have no idea how to proceed.

Thanks.

.... John

 

[Arvin Meyer [MVP]]

Check the permissions levels and make sure that each person is running
their own copy from their own workstation. Access front-ends should never be
shared.

 

[JohnE]

Thanks for the reply. The permissions are fine within Access as well as SQL
Server 2005. Each person does have their own front end. This is becoming
mind numbing trying to figure why it has run in A2003 all this time without a
problem and then try running it in A2007, we start getting errors. The A2007
is being tested only with the app. Everyone using the app is still using the
functioning A2003 on MS Access 2003.
Puzzling it is.
.... John

 

[Arvin Meyer [MVP]]

That's not a combination that I normally deal with. Perhaps the following
article will help you:

http://www.aspfree.com/c/a/Microsoft-Access/Linking-SQL-Express-2005-Tables-to-MS-Access-2007/
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com