weird security issue connecting to db

[shaferbus]

This is making my brain hurt - I hope someone has seen this behavior before.

I'm developing an intranet asp page form that retrieves info from an Access 2000 database, and depending on user input the response page either creates a new record or updates an existing one. Since I'm just in the development stage I put no security on the Access db. I composed the page on my Win2k workstation and tested it on a Win2k server running IIS 5.0.

I used Notepad to create a data link to get my connection string as follows (which is stored on a SSI page that both the form and response pages include):

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\{servername}\Inetpub\

http://wwwroot\_private\fake

schedule.mdb;Persist Security Info=False

After the usual hiccups I got the page working the way I wanted. So I gave the URL to a couple of users so they could check it out and see what they thought. After that, the response page no longer worked! It failed with the following error:

Microsoft JET Database Engine (0x80040E4D)
Cannot start your application. The workgroup information file is missing or opened exclusively by another user.
/scheduleresponse.asp, line 22 NOTE: Line 22 contains "conn.open ConnectionString,3,3"

Again, I had NOT yet set any security on the Access database! The page WAS working for me (a member of the Administrators group on our domain). Now it didn't work for anyone including me. Access is NOT installed on the IIS server, so there was never a system.mdw file there, yet it DID work! I can only assume that since the db was created on my workstation, it was referencing THAT system.mdw file even after it was moved to the web server, and the problem must have started when one of my demo users (who don't have rights to access my workstation files) tried to use it, causing authentication to fail...?

Obviously the database was looking for a .mdw file for security now, so I copied the one from my workstation to the same folder as the database file - but no joy. Again I used Notepad, created a .udl file, and established a data link that referenced the new security file. This yielded the following connection string:

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\{servername}\Inetpub\

http://wwwroot\_private\fake

schedule.mdb;Persist Security Info=False;Jet OLEDB:System database=\\{servername}\Inetpub\wwwroot\_private\system.mdw"

OK, that got rid of the missing file message, but now it was replaced with:

Microsoft JET Database Engine (0x80040E4D)
Not a valid account name or password.
/scheduleresponse.asp, line 22

Now remember, I STILL haven't invoked any Access security. So, I explicitly included the default user ID (Admin) and password (blank password) as follows:

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\{servername}\Inetpub\

http://wwwroot\_private\fake

schedule.mdb;Persist Security Info=False;Jet OLEDB:System database=\\{servername}\Inetpub\

http://wwwroot\_private\system.mdw"

& _
";user id=Admin;password="

I still get the same error message.

If I use the Notepad .udl file that generated the connection string, it connects successfully. If I open the db in Access, it does NOT prompt for user id and password, so obviously no security has been turned on in Access itself. It doesn't seem like it could be a problem with the connection string, because the form page uses the same SSI file to connect, and that works! It's the response page that won't (almost like the Admin user has read but not write priviledges, but I checked in "Security/User and Group Permissions" and everything is selected).

Does anyone have any suggestions or ideas???
Thanks for reading...

 

[MikeBronner]

I've seen this problem pop up here a few times now, and it hasn't been resolved in any of those threads.

My suggestions:

1) Make sure the database is not being touched by anyone when you're trying to connect (no-one should have it open through Access in design mode).
2) Make sure you have the latest ODBC drivers.
3) Make sure you have the latest Access JET drivers from the Windows Update website.

Other than that I don't really know what to say. Good luck!

Take Care,
Mike

 

[GaryC123]

I'm slightly confused by the whole scenario. Are users trying to connect to a db on your machine thru asp pages on the main server?

Take this scenario you have your asp page in a folder and the db in the same folder. Your connection string would be like this
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=fake schedule.mdb"
(btw spaces in db names and file names when used on the internet are bad protocol and can lead to problems)

And your users would access the page like this

http://servername/asppagename.asp

http://www.intouchmedialtd.com

 

[shaferbus]

Well, this IS an odd one, if someone on Tek-Tips doesn't have the solution LOL.

Mike - I downloaded the latest Jet drivers with no effect. I'm checking out the ODBC drivers. No one has the page open in any form at the moment, and the behavior is the same. In the process of developing the page, I've accidentally tried to load the page while it was open in Access a couple of times, but all went back to normal each time after I closed it, so I don't think that's it.

From some of the things I've read in the past day or two, this is a DAO error message, not ADO. I'm still a bit fuzzy on ADO vs DAO (the similar acronyms don't help ;-))... could it be the result of using a connection object instead of a command object? I had to change the form page from using a connection object to using a command object to get my query to work... Is the connection object a DAO thing and the command object ADO? Sorry if that's a dumb question... this is my first swing at asp and connecting to a db outside of a dbm program. Should I be using a command object?

Gary - the user is supposed to open the asp form page, which fetches a recordset from the db and displays it along with a checkbox for each record. The records are schedule items and the user checks the box to verify that he/she is notified of the schedule. The form page sends the checkbox info to a response page (the page in question), which modifies a table containing a record of when the user checked their schedule (either creating a new record if it's a new "timestamp", or updating the existing one if the user has checked it before). The asp pages and the db are NOT in the same folder, but is on the same server as IIS, and the path is correct. The first connection string I listed above worked fine until (as near as I can determine) a user WITHOUT administrator priviledges tested the page. Since then, the form page (which uses the same connection string) works fine, but the response page errors out... no matter who is trying to access it!

Obviously the connection string is hitting the database and the security file OK. The problem seems to be specifying WRITE access - but I've gone so far as to give the "everyone" group (and the IUSR account for good measure) full control of the db file and the folder it is in, and the "Admin" user has full rights in Access security!

I even tried making IUSR a member of the "Administrators" group on the domain to test whether it was a domain security issue, but to no avail. It's apparent that somehow the connection is failing Access authentication, but I have no idea how.

Also, just in case it was a corrupt db or (as Gary mentions) a problem with the space in the filename, I recreated the db from scratch with a new name and modified the connection string accordingly, but got the same error.

... and I was so proud of my first asp creation...

 

[onpnt]

one I'm slightyl confused abou is
Now remember, I STILL haven't invoked any Access security. So, I explicitly included the default user ID (Admin) and password (blank password) as follows:

if you have not set security right (permissions) on access then all you need to do to connect is

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\{servername}\Inetpub\

http://wwwroot\_private\fake

schedule.mdb

the state of adding the need for the security check may be the one killing the connection

_____________________________________________________________________

_{Hakuna matata!!}

 

[shaferbus]

Yep, you're right onpnt - and you probably noticed that the first connection string I posted was the same as yours except for the "Persist Security Info=False" part... and it DID indeed work until I let other users try it out.

The "Persist Security Info=False" is there because that's the connection string I got from the .udl wizard... I have to admit I didn't know what that statement was for, but since the connection worked fine I didn't question it. (In fact, it still works, since the form page is connecting and reading the info from the db)

As mentioned in my original post, I tried including the userID and password only after I started getting this authentication problem.

Just to be thorough, I copied and pasted your suggested string, and as I expected I got the "workgroup information file missing" error I listed in the first post. Again, the connection string (sans security) WAS fine until other users attempted to use the page.

Perhaps that's the source of everyone's confusion about my post - the fact that the whole thing WORKED, and then it didn't. I tested it myself over a period of a couple of days, and it WORKED. After that, I made no modifications whatsoever to the db, the asp code, or anything else! It was a matter of minutes between the time that I was satisfied with the page and the time the users started testing. That's what is so maddening about the whole thing! Your connection string SHOULD work. My original connection string DID work.

... and yet they don't now :-(

I'm sure you probably all think I'm nuts, or that I somehow unknowingly invoked Access security, or that I got "click happy" with some setting, but I swear it's not true. I've used Access security before and didn't really see the need to use it here, since I could control access using domain security. I had never even opened the Access security menu items on this workstation until I started having this issue.

You can bet I'll be documenting THIS one when I root out the cause LOL.

 

[MikeBronner]

One thing I just read about this:

http://www.attention-to-details.com/newslog/38n-temporary-volatile-jet-dsn-for-process.asp

I got some access problems as well, and it may have to do with this. Give it a whirl... can't hurt.

Good luck!

Take Care,
Mike

 

[shaferbus]

Thanks Mike

I've already checked most of that stuff. I did have an issue earlier on in this project having to do with IUSR_<servername> write permissions...

I am a bit puzzled with item 5 in the article you mentioned - &quot;Give the same account write permission in MS Access itself. These permissions can be reset by MS Access if you compact and repair the database so keep in mind to refresh this permission as necessary.&quot;

Is the author talking about the IUSR_<machinename> account? I was under the impression that Access security was oblivious to Windows user accounts and used it's own userID and passwords (hence the .mdw file).

Am I wrong?

 

[shaferbus]

Well, I figured out what was causing THIS problem!

ME!

In trying to solve one problem (see thread thread333-733439 ) I changed my connection.open line, thinking that I could specify a locktype there just like in a recordset.open statement.

Well, you can't! It was interpreting the locktype info as a username, and of course it didn't exist. So ADO did it's job and returned an error. It's obvious to me now, in retrospect - got confused with opening a recordset of course! Now I just have to figure out how to fix my original problem LOL.

Thanks for trying guys - especially onpnt and Mike who have been holding my hand all through this. Hope this helps someone else who THINKS their syntax is right.