Problems connecting ASP pages to OraclePosted: October 12, 2007
It has been one of these days. I barely had time to connect my laptop this morning, when an operator appeared in my cube, announcing that an application started giving the following error: [[Microsoft]][[ODBC Driver Manager]] Driver’s SQLAllocHandle on SQL_HANDLE_ENV failed. It started this morning, yesterday it worked fine.
The application owner admitted that he restarted the IIS server on the machine this morning, but swore that he made absolutely no changes. Of course, they never make any changes. God knows what they do all day. Few minutes later it became clear that although every user in the system can start sqlplus, IIS thinks that Oracle drivers are missing, and therefore it cannot connect to our Oracle databases.
I searched the web for information and I found two useful documents:
- http://support.microsoft.com/kb/255084 - Official MS troubleshooting guide. Excellent document which covers every possible cause for the issue. Highly recommended.
- http://www.orafaq.com/forum/?t=msg&th=38079/0/ – This document contains a strange suggestion that worked for many users:If you navigate Oracle home you will find that the directory has different permissions then the root of the tree. In the case of 10G the path is C:/Oracle/product/10.1.0/Client_1. Open the properties for the Client_X directory, and you will see that “Authenticated Users” should have Read and Execute, List Folder Contents, and Read. To fix the problem, do the following….
- -Select Authenticated Users
- Uncheck Read and Execute
- Press Apply
- Check Read and Execute
- Press Apply
You will need to restart the IIS and maybe even reboot the machine after making these changes, and it should solve your problem.
Very strange, but looks like it works in some cases.
My problem looked like a permission issue, but doing what the MS document or the forum suggested did not help, and I didn’t want to reboot the machine.
So, I worked around the problem in a rather unsafe way – I changed the “Log on as” parameter of the IIS web server from “LocalSystem” to “Administrator”. Works like charm.
A fellow geek blogged about an extremely unreliable way he found to communicate information to himself. Not database related, but very amusing to all computer geeks.