Today when a try to build a web page which gathers some data from a Oracle Database, I meet the following error:
Provider cannot be found. It may not be properly installed
i had oracle 9i client installed on my machine, the connection is made through oracle oledb provider, and i can successfully connect to the database in wscript host with the same connection string. so it seems there is something wrong with iis and oracle
after some search, i had a try to use microsoft oledb provider for oracle instead of oracle’s, then i get this error:
The Oracle(tm) client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3 (or greater) client software installation
there are information saying that one should edit registry to let microsoft provider knows the correct oracle dll file name for oracle client version above 9i. this is probably right and required for microsoft oledb provider, further infomation can be found on http://support.microsoft.com/kb/193893. but this still can not solve my problem.
more search result point to iis user privage on oracle home folder. the most popular related solution is like:
Oracle 9.2 Client software requires that you give the Authenticated User privilege to the Oracle Home by following these steps:
1. Log on to Windows as a user with Administration privileges.
2. Launch Windows Explorer from the Start Menu and navigate to the ORACLE_HOME folder. This is typically the “Ora92″ folder under the “Oracle” folder (i.e. D:\Oracle\Ora92).
3. Right-click on the ORACLE_HOME folder and choose the “Properties” option from the drop down list. A “Properties” window should appear.
4. Click on the “Security” tab of the “Properties” window.
5. Click on the “Authenticated Users” item in the “Name” or “Group or user names” list.
6. Uncheck the “Read and Execute” box in the “Permissions” or “Permissions for Authenticated Users” list under the “Allow” column.
7. Re-check the “Read and Execute” box under the “Allow” column (this is the box you just unchecked).
8. Click the “Advanced” button and in the “Permission Entries” list make sure you see the “Authenticated Users” listed there with:
Permission = Read & Execute
Apply To = This folder, subfolders and files
If this is NOT the case, edit that line and make sure the “Apply onto” drop-down box is set to “This folder, subfolders and files”. This should already be set properly but it is important that you verify this.
9. I checked the “Replace permission entries on all child objects with entries shown here that apply to child objects” checkbox and clicked the “Apply” button. (This was not specified in the original instructions, but it worked.)
10. Click the “Ok” button until you close out all of the security property windows. The cursor may present the hourglass for a few seconds as it applies the permissions you just changed to all subfolders and files.
11. Reboot your computer to assure that these changes have taken effect.
12. Re-execute the application and it should now work.
i did as it said. after add read&exeute permission to %ORACLE_HOME% folder for Authenticated Users, IUSR_ and IWAN_, the microsoft provider pops:
Oracle error occurred, but error message could not be retrieved from Oracle
now i can’t find more information on internat, i thought maybe there is some more lib files outside oracle home folder requires permission set, so at last i start filemon to watch which file access is blocked by window privage system.
then i found that w3wp is tring to access bin/oraclient9.dll under oracle home folder but ended with an “access denied” error
now the truth is out. though i set the permission, and confirmed that the “apply to” option in advance page is set to “This folder, subfolders and files”, the subfolders and files still have their own privage setting. so the correct method should be:
*after granted read&exeute permission of oracle home folder to Authenticated User, and confirmed the “apply to” option in advance page is set to “This folder, subfolders and files”, one should check the “replace permission entires on all child objects with entries shown here that apply to child objects” option to overwrite subfolders’ privage setting*
after this, both oracle provider and microsoft provider works correctly
