.Net Web應用程式在Iss釋出後無法連線oracle資料庫

NO IMAGE
老問題,新狀況 ——.Net Web應用無法連線Oracle問

關於.Net 應用無法連線Oracle資料庫,從來不是什麼新問題,而是一個經常困擾程式部署人員的痼疾,從遙遠的過去到現在,它時不時地就蹦出來,煩你一陣。

該問題的具體症狀是資料庫連線字串的udl試驗沒問題,plsql developer 沒問題, Sql/Plus 沒問題,可是.Net應用卻總報無法連線Oracle資料庫的錯:呼叫的目標發生了異常!

好在有前人的的努力,解決這個無厘頭的問題也不再是多麼困難的事情。一位老大早在03年就搞定了這個問題,雖然搞定的過程也異常鬱悶。以下摘自該君的郵件。

——————————————-
發件人: gene
傳送時間: 2003年8月21日 10:15
主題: 莫名其妙的一夜
………………………… 由於公司網路問題研發許多主機沒線上,只好下載了oracle client9.2.0.1,結果此版本在IIS下的backend不能連線資料庫,但sms service確可正常連線,……………………….後查閱資料發現此為oracle一個bug,原因是NTFS的安全與IIS執行的使用者模式問題,解決方法如下,以共借鑑:
到Oracle/ora92 開啟Ora92的-》屬性-》安全,選Authenticated Users 看下面的屬性 將Read and Execute 的勾去掉,然後又勾上,確定重新啟動機器 ,ok 搞定,就是這麼簡單,當然要避免這種問題,您可以這麼做:不要安裝Oracle9.2 或者不要安裝在NTFS格式的分割槽。
附上
Oracle 公司的原版資料:
Problem Description ——————- When running an application that connects to Oracle and uses the Authenticated User privilege (such as Microsoft’s Internet Information Server (IIS)) via Oracle’s 9.2 client software and any of these programmatic interfaces 1. Oracle Provider for OLE DB 2. Microsoft OLE DB Provider for Oracle 3. Oracle ODBC Driver 4. Microsoft ODBC for Oracle 5. Oracle Objects for OLE (OO4O) you will receive one of the following errors: (1) Oracle Provider for OLE DB Error Type: Microsoft OLE DB Service Components (0x80070005) Access is denied. (2) Microsoft OLE DB Provider for Oracle Error Type: Microsoft OLE DB Provider for Oracle (0x80004005) Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation. Provider is unable to function until these components are installed. Or Error Type: Microsoft OLE DB Provider for Oracle (0x80004005) Oracle error occurred, but error message could not be retrieved from Oracle. (3) Oracle ODBC Driver Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80004005) Specified driver could not be loaded due to system error 5 (Oracle in OraHome92). (4) Microsoft ODBC for Oracle 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. You will be unable to use this driver until these components have been installed. (5) Oracle Objects for OLE (a) while using a GLOBAL.ASA file Error Type: Active Server Pages (0x0) An error occurred while creating object ‘OraSession’. (b) not using a GLOBAL.ASA file Error Type: Microsoft VBScript runtime (0x800A0046) Permission denied: ‘CreateObject’ (6) Other miscellaneous errors (a) The Specified Module Could Not Be Found Solution Description ——————– You need to give the Authenticated User privilege to the Oracle Home by following these steps: 1. Log on to Windows as a user with Administrator privileges. 2. Launch Windows Explorer from the Start Menu and and navigate to the ORACLE_HOME directory. 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 on the “Properties” window. 5. Click on “Authenticated Users” item in the “Name” list (on Windows XP the “Name” list is called “Group or user names”). 6. Uncheck the “Read and Execute” box in the “Permissions” list (on Windows XP the “Permissions” list is called “Permissions for Authenticated Users”). This box will be under the “Allow” column. 7. Check the “Read and Execute” box. This is the box you just unchecked. 8. Click the “Apply” button. 9. Click the “OK” button. 10. You may need to reboot your computer after these changes have been made. Re-execute the application and it should now work. Explanation ———– If you install Oracle9i Release 2 (9.2.0.1) on a computer running Windows with an NTFS partition, the contents of ORACLE_HOME directory will not be visible to users who are authenticated on that machine. These permissions were not set properly when the software was installed. Applications that were working fine with previous versions of Oracle software will stop working when they upgrade to Oracle 9.2. NOTE: The application will continue to work if the user has logged onto the machine as an Administrator. Any application that is using the Authenticated User privilege will not work. A notable example would be IIS which might service some of the requests based on the Authenticated User privileges. To demonstrate the problem in further detail, you can log on to the operating system as an authenticated machine user. You won’t be able browse the contents of the ORACLE_HOME directory demonstrating your inability to load any Oracle DLLs or make a connection. References ———- Bug:2498880 – Oracle 9I Release 2 Installation Issue on Windows 2000 NTFS File System Additional Search Words ———————– OLEDB
整個安裝過程一直處於苦思冥想的鬱悶狀態!
—————————————————————————

時值今天 ,我們也不得不感謝gene 的那晚苦思冥想的鬱悶,正是他一夜的鬱悶讓我們以後解決這類沒頭緒的問題變得異常輕鬆。

我們的.Net應用絕大多數都是部署在ntfs分割槽,我們的資料庫客戶端也大多是Oracle9.2 ,所以這樣的問題就屢見不鮮。按照郵件介紹的操作,大多數情況都能解決問題。如果還不行,把使用者Aspnet和Internet 來賓使用者IUSR_COMPUTERNAME加入Ora92的安全許可權裡,無法連線的問題就可以解決。從來就是這麼簡單,一直到上週五。

上週五某平臺測試機部署時同樣遇見這個問題,作業系統windows 2003,Oracle客戶端9201。使用者Aspnet和Interet 來賓使用者IUSR_COMPUTERNAME 已經加入Ora92的安全許可權裡,不行。匿名訪問使用者賦予管理員許可權,還不行。檢查system.config,web.config,沒發現任何異常。替換oracle本身提供的provider也不行。重啟IIS,重啟3w服務,重啟機器,怎麼折騰都不行。真是鬱悶古今同啊。最後,已經準備做換機器這樣無奈的打算了。還好,在絕望之前又仔細檢查了一些細節。

windows 2000和2003的ASP.NET 的體系結構其實是有結構上的差異的。在 Windows 2000 上,多個 Web 應用程式執行於 ASP.NET 工作程序 (Aspnet_wp.exe) 的單個例項中。每個應用程式駐留在其自己的應用程式域中,該域為託管節點提供一定程度的隔離。而在 Windows Server 2003 上,體系結構發生更改,因為 IIS 6 允許使用多個程序來駐留獨立的 Web 應用程式。與 Windows 2000 下的 ASP.NET 體系結構相比,Windows Server 2003 中的ASP.NET 體系結構的主要區別在於可以使用單獨的 IIS 工作程序例項 (W3wp.exe) 來駐留 Web 應用程式。預設情況下,所有的應用程式都在預設應用程式池中執行。

所以,對預設的應用程式池的配置又做了最後一次詳細的檢查,在預設的應用程式池屬性中標識頁,發現有一個預定義帳戶選項,列出了三個選項:網路服務 (NetworkService)、本地系統 (LocalSystem)、本地服務 (LocalService) 。當前選中的網路服務,而該帳戶是這三個帳戶許可權最低的,將選項改為本地系統,重啟IIS,久違的輸入框出現了,終於正常連線到資料庫。

小結:如果問題確認出現在環境上,那麼就仔細檢查每一個細節,以及每一個可能相關的配置項。

 總結:解決方案:第一步,在oracle安裝目錄(d:/oracle/ora92)上右鍵,屬性->安全,選中Authenticated Users將許可權的讀取和執行項的勾去掉再打上,然後點選應用,再點選高階確定該使用者應用於該資料夾,子資料夾及檔案;第二步在ISS中相應專案的應用程式池上右鍵屬性,點標識標籤,在預定義帳戶項選本地系統,然後重啟ISS,OK,登入正常。