ODBC help needed.

[Guest]

I have an Access program that is linked to our SQL DB, and pulls information
from there. About 2 weeks ago we updated from SQL Server 7 to SQL Server
2000. Before the upgrade everytime they used the Access program it
automatically pulled the information from the SQL DB without asking for a
username / password. Now though since the SQL Server 2000 upgrade it asks
everytime they start up the program and it runs a query on the SQL DB. I am
at a loss as to why it is doing this since the upgrade, and didn't in the
past. I built the Access front end about 3 years ago. Is there a setting I am
missing in the Access program? Is it something to do the the ODBC connection
setup of the Windows operating system? Could it be something to do with
settings in the SQL Server 2000 settings that I don't know about. If anyone
can help me to figure this issue out, and get it fixed it would be greatly
appreciated.

Thanks,
C_Ascheman

 

[Arvin Meyer [MVP]]

I don't have that exact configuration here, but if I remember correctly, you
set up the ODBC driver in the Control Panel, under Administration Tools,
when you set it up you use Windows authentication and SQL-Server will read
the login from Windows.

 

[Guest]

Alvin,

I set it up through the ODBC Data Source Administrator of the Administrator
tools section so the guy who uses it doesn't have to worrie about inserting a
username / password as he doesn't have one for the SQL DB. When we were
running SQL Server 7 it all ran as intended, and never asked for any of it.
Now though since the upgrade to SQL Server 2000 it does everytime. I have
gone into the ODBC settings under the System DSN tab, and tried resetting the
username / password several times, and even removed / remade the SQL
connection several times. It still asks for a username password every time
the Access program runs its Select statement to pull the data from the SQL DB
through the linked tables in the Access DB. I am at a complete loss as too
why it is doing this now when it didn't in the past. Any other ideas would be
greatly appreciated.

Thanks,
C_Ascheman

 

[Guest]

Sorry Arvin for mistyping your name. In too much of a hurry.

C_Ascheman

 

[Arvin Meyer [MVP]]

If you use integrated NT authentication, it shouldn't ask for a password.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Guest]

Arvin,

Tried that and it gives a message saying its unable to make a connection.
Every user here has a seperate Windows username / login from the ones they
have for the SQL server. The only reason I can surmise as too why it rejects
it through the NT authorization is that the passwords are different.
Unfortunately that is the way my boss wants it so I can't change it. All I
can do is set it for SQL authentication and supply the username / password.
Yet it's almost like its not retaining the password at all. I have tried
running the program from every system we have, and its the same thing. If I
run it under my account (Administrator) then the NT authorization works, but
under a normal user account if fails to connect unless we connect through the
SQL authorization and give it the username / password. Before the SQL Server
2000 upgrade it didn't ever ask for them. Now it does, and I cannot figure
out why. Thanks for your help so far, and look forward to anymore suggestions
from anyone.

C_Ascheman