We help IT Professionals succeed at work.

ODBC in ASP dont work after W98/PWS to W2000/IIS migrate

mbenish
mbenish asked
on
Medium Priority
428 Views
Last Modified: 2008-03-03
I created an intranet using Personal Web Server on a Windows 98 machine using ODBC connections to the databases, which are on other drives on the network.  Making Application variables of "DSN=xxx" is a lot easier than "Driver={Microsoft Access Driver (.mdb)}",dbq=xxx.mdb, ", etc.....

Now I have moved the code to a Windows 2000 server that is running IIS.  I put in the same references to the ODBC drivers.  At first, I put in User DSN's like on Windows 98, and it bombed.  Looked in the registry, and it seems that only System DSN's are registered.  So, I made them System DSN's.  Go to run an ASP, get an 8000005 error, something about SetSQLConnectAttr.....

Idecided to try to change the connections from DSN-xxx to a much longer string, and I can read the files, but, I can't update ANYTHING.  "Updatable query must be used."

Does anyone have an idea on how I can get the ODBC versions to work, read, and update?  Is there a problem with versions of MDAC, the ODBC driver, etc...

Comment
Watch Question

Commented:
Have you tried to install the last version of mdac?
Also look at this question, its answered might help
http://otnxchange.experts-exchange.com/jsp/qShow.jsp?qid=20110010
CERTIFIED EXPERT

Commented:
There's probably no problem with your ODBC. The possible most likely problem is that in the NTFS environment like NT 4.0 or 2000, you need to make sure of the following,

1. Make sure the mdb file is not readonly
2. If it is on ntfs partition like NT or 2000, then also make sure that write permission is given.


Right click on your access MDB file (browse to it physically)..
select propreties, select the security tab..
add the local webserver account IUSR_<yourwebservername>
..if it's added then select it and tick on Modify..
apply..ok..
This will give the mdb file NTFS Privelieges to update it..


Read this
http://support.microsoft.com/support/kb/articles/Q175/1/68.ASP

hongjun
CERTIFIED EXPERT

Commented:
For your information, you can download the latest MDAC at http://www.microsoft.com/data

hongjun

Commented:
You mentioned your db's are on other computers.  The IIS web server in W2K operates under the user account IUSR_computername, and that user account must be able to access the remote databases.

Commented:
dgorin is right. Sounds like your problem isn't the DSNs, rather it is the authentication of the anonymous user account that IIS runs under.

Either give file access to the anonymous IIS user (for the mdb files) or change the account that IIS impersonates under. It should work then.

Author

Commented:
Thanks for your responses.  Yes, after asking this, and resading through other Q's, I think the culprit is the IUSR account, I'll try this stuff Monday and let you all know.
CERTIFIED EXPERT

Commented:
Exactly. This has to do with permissions.

hongjun
Michel SakrRounded IT Consultant / Projects manager / Cloud Consultant / IT Planning
CERTIFIED EXPERT

Commented:
Some of the guys above got close..

It's NTFS permissions.. It's Th IUSR_Computername .. and you have to go to the access mdb file right click on it select security.. permissions add the IUSR_<webservername> account (it's a local account on the webserver and not a domain account) give change permissions and all should be ok..

Commented:
I think hongjun described setting permissions for the IUSR_computername user on the local computer already.  I brought up that you need a corresponding username with the same password on the db computer if the access db is on a different computer from the web server.

Microsoft has a KB article on using access via a UNC path to another computer here

http://support.microsoft.com/support/kb/articles/q207/5/25.asp

Author

Commented:
Here's what happened.....heh.heh..heh..

The ODBC drivers were configured as system DSN's.  For some reason, on Windows 2000, they needed to be User DSN's.  They then worked fine.  I did change the access for IUSR_Computername to global, but the IIS service worked even before I changed this.  

I appreciate the info I got here, especially from hongjan and dgorin.  Can I give them 150 pts apiece?
CERTIFIED EXPERT

Commented:
Yes for sure.

Post a question to the Community Support topic area at http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt with the following information.

Subject: Split points
Points: 0
Body: Please split points between hongjun and dgorin with 150 points each. Question link is http://www.experts-exchange.com/jsp/qShow.jsp?ta=asp&qid=20188222

hongjun
CERTIFIED EXPERT

Commented:
ALERT TO MODERATORS
===================
Please give me points for this question and open a new question for dgorin. I will not be active in EE until next week and thus will not be able to spot the new question posted by you. Hope to gain the understanding from everyone who is related.

Thanks
hongjun
A request has been made (http://www.experts-exchange.com/cleanup/Q_20320237.html) that this question be closed with the following resolution: split b/w hongjun and dgorin

EXPERTS: Please leave any comment regarding this request here; I will return in seven (7) days to close the question.

Netminder
CS Moderator

Explore More ContentExplore courses, solutions, and other research materials related to this topic.