ASP - ODBC - IIS6 2

[redsand23]

I am running a 2003 server, running iis6 and having difficulty with opening ASP pages that connect to either an sql database or Access via odbc. I have browsed the forum and have performed the following tasks
1) Made sure iusr had permissions to the db folder
2) Set execute scripts and executibles
3) Checked and validated system dsn's
4) Made sure all passwords were in sync

I get the following error when running this code
<%@ Language=VBScript %>
<%
option explicit

Dim conOP, rsOP, rsOP1,
Dim StartDate, EndDate, stopDate, wrongDate, enddate1
Dim sqlOP,
Dim Totall, Totallper, Unitotdop, PartTotop, UnitTotop, StartDateop, EndDateop, LtdPartop, LtdUnitop, Partotdop
Dim norec
Dim dataOPAvail

'--------------CREATE DATA CONNECTION AND RECORDSET OBJECTS

Set conOP = Server.CreateObject("ADODB.Connection")
Set rsOP = Server.CreateObject("ADODB.Recordset")

'------------SET UP SQL QUERY

sqlOP = "SELECT Parts1, Units1 FROM OTD"

'------------OPEN UP DATABASE AND RUN QUERY

conOP.open "dsn=Dashboard","sa","sa"


error '8007007f'
/index.asp, line 73
Line 73 being the conOp.open.

This is happening with any asp page accessing either an sql db or an access db on the 2003 server.

I copied the wwroot directory from an NT server running iis4

Any help would be greatly appreciated.

 

[Sheco]

It is giving you an error trying to create the ADODB.Connection object even after you reinstalled the MDAC ???

 

[a6m1n0]

If the above code generates an error, then recheck your IUSR permissions for the mdb file itself (don't worry about the directory, set the perms directly on the file.) The only perms IUSR needs is READ/WRITE on the actual mdb file. If the permissions are indeed set properly, force a 'replace persmissions on this object and all child objects' operation on the f:\databases directory with READ/WRITE permissions.

The point of this excercise is to determine if this is indeed an ODBC problem, perm problem, or whatever. Looks like baby steps from here on out.

Let's tackle the SQL connection after trying to connect to your phonedata.mdb.

 

[a6m1n0]

@Sheco

I received the exact same error on my machine testing the vbs script you posted with a test DSN for Access. Just FYI. [I'm no shell guru so you tell me.]

 

[redsand23]

Running testing123.asp I received the following error
Microsoft VBScript runtime error '800a01b6'

Object doesn't support this property or method: 'server.create'

/testing123.asp, line 5

I checked permissions on the actual db and I have everyone & iusr with full permissions.

Does the database folder need to be located inside of

http://wwwroot?

 

[a6m1n0]

There was a problem with that code.

Change:

set conn = server.create("adodb.connection")

to

set conn = server.createobject("adodb.connection")

and run again.

No, don't move the database.

 

[Sheco]

a6m1n0 ,

Yeah same here vis-a-vis the error... I'm guilty of assuming that the connection string was ok.

 

[redsand23]

I changed the code, here is the latest error

error '8007007f'
/testing123.asp, line 6

 

[a6m1n0]

@redsand23
Post the complete error. Word for word, line for line. Also, if you open phonedata.mdb locally (or on a workstation) with Microsoft Access, are you prompted for a user and/or password?

@Sheco
So if the DSN is fine that code should work? This isn't an accusation, I just don't know jack about shell scripting, and wanted to ask.

 

[redsand23]

This is the complete error, this is all that shows on the page when it runs. I do not have office loaded on the iis server so I can not check to see if it will run. I will install office on the server and get back to you on the opening of the db. Again keep in mind that this is happening with both any local db's and sql databses on our sql server.

Thanks for all of your help and patience

 

[a6m1n0]

Glad to help.

It's like finding a needle in a haystack on error '8007007f' from everything I've read. But the most consistent solution I've found so far is reinstalling SP3a, MDAC, and to re-register oledb32.dll.

click Start-->Run.

Copy and paste in the Run dialog:

regsvr32 "C:\Program Files\Common Files\System\Ole DB\oledb32.dll"

Click Ok, Ok.

Restart server. (Not required, but I would if it were me.)

I still think there is something wrong with the connection string, but try that since it's much simpler (some might say smarter) than installing Office on the server.

 

[redsand23]

Are you talking about sp3a on the sql server? I will try reregistering ole when I get in Monday. I don't think it is the connection string only because it is not working with access and sql as well as a couple of purchased intranet polling software we also use. (Code I did not create)

Thanks for all of your help.

 

[a6m1n0]

Yes I am talking about SP3a for the SQL server, but for Access and/or SQL, reinstall MDAC (which you said you tried already with no luck.)

So, if SQL server, reinstall SP3a, MDAC, and re-register oledb32.dll.

For the server just hosting Access mdb's and IIS, reinstall MDAC and reregister oledb32.dll.

I am assuming thast you have several servers invloved, at least one with Access mdb's and IIS and then a SQL server?

 

[redsand23]

I have an iis5 server that is running great and connecting to both sql & access dbs, so I do not think it has anything to do with sp3, that is unless iis6 is looking for something on sql that iis 5 does not need. I have the access db's stored locally on the iis server and the big databases I have pointing to SQL server. It just stumps me that it is with any database trying to connect regardless of what type and who wrote the connection string. I am really leaning towards a permissions issue. Although at this point I have set iusr user and everyone having admin rights and rights to all dirs on the iis server.

Thanks again.

 

[a6m1n0]

Try:

Open IIS mmc and expand Internet Information Services.

Right-click the site that hosts the ASP that connects to phonedata.mdb. Click Open.

A new explorer window opens at your site's webroot. Create a new directory called '_database' without the quotes. ALT+TAB back to the IIS mmc.

Right-click the _database directory you just created. Click on properties.(You might have to refresh the site contents to see the newly created _database directory.)

A new dialog window opens titled '_database Properties'. The default tab is 'Directory'. Uncheck the 'Read' checkbox. Apply, Ok. (This keeps the contents of _database secured from being downloaded even though it is in your webroot.)

Restart IIS. ALT+TAB back to the explorer window.

Copy phonedata.mdb to the _database directory. Right-click the MDB file. Click Security.

Click Add, Advanced, Find Now. Select the IUSR account. Click OK, OK.

The IUSR account is selected by default, so the permissions properties window displays the permissions for the IUSR account on the _database directory. Uncheck 'Read & Execute', and uncheck 'List Folder Contents'. Check 'Write'. Click Apply, but don't click OK yet.

Click on Advanced. Check 'Replace permission entries on all child objects...'. Click Apply, Yes, OK, OK.

Effectively what this does is give you a directory inside your webroot that is inaccesible clientside via IIS to store an Access DB, but at the same time gives the client server-side privleges for processing things like executable ASP code for connecting to a database. The database can't be downloaded from the _database directory if you follow the instructions.

You might also want to add a new host header for that site for Port 80, 127.0.0.1 so you can test execution locally. If it works locally it should work publicly.

This is the way that I setup sites for my various clients. I only host projects while under developement, but once approved for production I move the site to a hosting provider. Most hosting providers will not give you access to anything other than your webroot, so this is a common way to store a DB in the webroot without compromising to much security.

The point of this exercise (the book long post) is to set a level playing field for troubleshooting permissions on your server. Every admin sets things up differently and using a forum to troubleshoot NT/IIS permissions can turn into a nightmare quickly.

Other alternatives include a lenghty and expensive call to MS tech support, possibly reinstalling IIS, maybe even a repair install, and last but not least a clean install of 2003 server.