Access 2002 FE with SQL Server 2k BE - Login failed for user Admin

[fuzzyocelot]

Hey everyone!

I have a bit of a mystery on my hands, and I hope someone may be able to
help me figure it out. This particular mystery involves an Access 2002 SP3
front end with a SQL Server 2000 SP3a back end. The operating system is
Windows XP Professional 2002 SP2. I wasn’t sure if I should post this in an
Office / Access forum or SQL Server. So I apologize if this isn’t the right
forum.

Anyway, I am a former programmer recently turned SQL Server DBA. Every day I
check the SQL Server error logs. For the last couple of days we’ve noticed
that a particular SQL login keeps failing. Some days it fails 20 to 30 times.
That’s how we noticed it. It’s called “admin†and it’s only used for one SQL
database. I’ll call it DatabaseA. The person who owns this database said only
two people know the password for that particular login and they haven’t been
using it lately. So I started a SQL Trace in order to try and figure out who
is using this login or at least what their computer name or IP address is. In
the trace, we noticed the hostname was either the generic/default computer
name we use at my work place or it was the web portal server name. Which
means we can’t track the SQL login failure to any particular computer or
person.

Today when I ran the trace, I noticed an actual computer name appeared with
the admin login failure. So I did some look-ups and was able to determine
that computer name belonged to one of my co-workers! So I “interrogated†her
about it. She’s a great person and a friend so I had to give her a hard time
about it, jokingly of course. It turns out that there was a certain MS Access
mdb that one of her clients was having a problem with and she ran it this
morning. It wasn’t returning the correct data. The back end is the same SQL
Server but a different database. I’ll call it DatabaseB. I obtained a copy of
the mdb and ran it myself. It prompted me for a database login and password,
which I entered. This login isn’t remotely related to the admin login, by the
way. The ODBC connection for this mdb is set up correctly on my computer. I
know because I’ve used it many times without problem. In this instance, it
defaulted to the correct login so I typed in the correct password. It was
accepted. No errors. So then I checked the SQL trace. Sure enough! My
friend’s computer name showed up! She wasn’t even at her computer so I know
it was because I ran it. I figured out why it shows her computer name and not
mine. I think that’s a separate issue which I won’t cover here.

Anyway, for giggles I created a new blank Access mdb. Then I linked to a
table in the same SQL database (DatabaseB) my friend was using with the same
ODBC connection. I linked to a table and clicked the OK button. I did not
show up in the trace. Then I closed Access completely, opened up the new mdb
I had created, and opened the linked table. It prompted me for the correct
login information. I entered the password, clicked okay, and it opened the
table. Guess what? I showed up in the trace results (my computer name showed
up)! How weird is that?

Does anyone have any ideas why this is happening and how to fix it?

Thanks in advance cuz it’s driving me nuts!

 

[Tom Miller]

. For the last couple of days we've noticed

that a particular SQL login keeps failing. Some days it fails 20 to 30
times.
That's how we noticed it. It's called "admin" and it's only used for one
SQL
database.

I hope you get an answer. The only thing I know of even remotely that might
apply is that what your describing is a very common (and certainly bad) way
(using an "admin" login) to provide access to a DB that is being used by
multiple people. You can see the security issue I am certain. So you might
want to poke around and see if a "stored procedure" of some kind is
generating these symptoms.

Thanks,
Tom Miller

 

[fuzzyocelot]

Thanks for your reply!

I see where you’re going with the stored procedure idea. However, here is
the really weird thing. I created a blank Access mdb with nothing in it. I
then linked to a table in DatabaseB on the SQL Server using an ODBC
connection. Let’s say the user name I’m using is db_user and the password is
something like user51!%. I link to table employee. I open the table. All this
time I’m watching the SQL trace I have running to see if that login failure
shows up. Nothing shows up so far. I close Access entirely and open it up
again. Then I try to open the table. I’m prompted for the database login and
password. I enter them. I can see data in the table fine. I check the SQL
trace and that login failure for “Admin†shows up with MY computer name! I
tried it several times and the same thing happens every time. Weird, huh? I
am stumped!

I also understand what you’re saying about having an “admin†account to a
database. Right now only two people use that particular login and they are IT
personnel. Plus, I was not the one who originally set this whole thing up. I
do know the DBA who did set it up would not allow a login like that unless it
was deemed absolutely necessary. DatabaseA was created by a vendor along
with it’s application. I can’t change the login to something else or the app
would break. Besides, the more I think about this the more I feel it has
absolutely nothing to do with DatabaseA and it’s Admin login. If this login
“went away†or was changed, I think we’d still be seeing this login failure
for Admin. Maybe somehow Access is internally using an Admin Id to get
rights to the data or something along those lines. If that’s true, then why
would it do this and how would we stop it?

Thanks!