Authenticating in SQL Server via MS Access 2002

[Dazza]

Here is the scenario:

1.Database is on SQL Server 2000
2.Client is MS Access 2002
3.SQL Server using mixed mode authentication
4.Normal user access is via the forms in Access that connects via ODBC and
SQL using Windows authentication.
5.Certain users, as well as normal access, also need to create ad-hoc
queries thus use another Access client with tables only (ODBC connection
using SQL Server authentication)

Here is the issue I need to get round. As I do not want users to be able to
add or change data directly in the tables, the authentication in point 5
adds the logins to a restricted role (read only). However, whenever the
database launches, it initially tries to authenticate with the default
Access account "Admin". The login failure is not flagged to the users but
does appear in the SQL Server logs. Only when the user opens the table, it
is then that they are asked for their SQL Server login.

What I want to do is, when Access is first launched, the user is asked
immediately for their SQL Server login name and password thus the user
"Admin" does not even attempt to connect. How can this be achieved?

Regards

Dazza

 

[Sylvain Lafontaine]

This is a feature of JET and can be disabled by a registry setting (I don't
remember which setting).

However, you can use this feature in your case by creating new user accounts
for your second Access database. Users will open this database using their
account and this account will be used by Access to authenticate with
SQL-Server.