I am installing an MS Access database on an Intrant, and
am wondering if it is possible to give users access based
on their Windows userID and password, - so they don't have
to authenticate themselves when starting the database.
Access doesn't have any built-in way to do this, but I've come up with
a way to fake it.
In my case, I'm authenticating users against our mainframe system
through an IBM DB2 connection using ODBC. However, my idea can be
adopted to use any sort of authentication method you choose, as long
as you're able to write a VBA procedure in Access to accomplish it.
I've expanded upon an idea that was expressed a few times in this
newsgroup already by a gentleman named Jeff Conrad. In Jeff's case,
all he was doing was creating a custom login screen to log in to
Access security.
The basic idea is to create a front-end unsecured database with a
single form of your own design that prompts for the user ID and
password, then uses the Shell command to launch the real database and
automatically log in as the user ID and password provided in the front
end. Compile the front end as an .MDE file (so users can't view the
code behind the scenes) and disable the ability to
use the Shift Key to bypass the startup.
I took that general idea and added an intermediate step in the front
end that verifies the user ID and password against another system
first, then launches the real database and logs into the Access
user-level security. You still create user ID's in Access for all of
your users, but there are 2 ways to handle the passwords (so you don't
have to change them in the Access security every time the external
password changes, my company forces passwords to change every 30
days). Either hard code the passwords in the Access security (every
user has the same password in Access security), or what
I did was use a lookup table to match the System user ID that you
authenticate with through your method (LDAP, Active Directory,
whatever) to an Access security user ID and password. This table is
stored in your real database (which is secured) and read by the front
end, so not just anybody can read the back end Access security user
ID's and passwords.
I've created a simple template .mdb file that can easily be adopted to
any authentication purpose, as mentioned above as long as you can
write a VBA procedure in Access to perform your authentication. If
you'd like a copy of it, e-mail me directly or post a reply here and I
can e-mail you my .mdb file as an attachment. It's only about 250k.
(Oh, it's in Access 2000, but should easily convert to whatever
version you need.)
On a side note, if somebody does have or writes VBA code to perform
LDAP or Active Directory authentication checks, I'd sure love to get
it. Right now, as I mentioned above, I'm authenticating against our
mainframe because that was the easiest thing I could figure out to do,
but I'd love to authenticate against Active Directory instead.
