同事遇到的問題,做個筆記:

在某台機器上,同樣的連線字串,用System.Data.OracleClient.OracleConnection可以連上資料庫,用ODP.NET 9207 Oracle.DataAccess.Client.OracleConnection卻一直彈出"帳號/密碼不正確"的登入失敗訊息。
該機器上有安裝Oracle 9.2及Oracle 11g的Client,二者的TNSNAMES.ORA經比對後完全相同,到底問題出在哪裡?

原來,從Oracle 11g起,密碼才改為區分大小寫。而ODP.NET 10.2.0.3以前的版本,在送出密碼時,會一律將密碼字元強迫轉為大寫,若Oracle 11g的密碼包含小寫,Client與Server就註定無緣相會了。

解決方法有幾個:

  1. 換ODP.NET 10.2.0.4+
  2. 將密碼設成全大寫字母
  3. 將11g資料庫設定為"密碼不分大小寫"
  4. 在連線字串中用雙引號夾住小寫密碼,例如: string connStr = "Data Source=ORCL;User ID=scott;Password = \"tiger\"";

附上網路查到的官方說明: (來源)

Applies to:
Oracle Data Provider for .NET - Version: 9.2.0.8.0 to 10.2.0.3.0
Microsoft Windows (32-bit)
Symptoms
You are trying to connect to 11g database using 10g ODP.NET (or lower version) and you receive
ORA-1017 - invalid username /password
While using 11g ODP.NET or 10g sqlplus ,this problem does not occur.
Cause
Whenever we send the passwords using 10g ODP.NET ,it converts them into all upper cases.
Prior to 11g database , the passwords were case-insensitive.
So sending all-capital password would not cause problem there.
However, from 11g onwards , passwords are case sensitive.
Hence when ODP.NET sends all-capital password to a 11g database , password validation fails and results in the ORA-1017 error.
This issue is reported in an internal bug .The bug number is not revealed for confidentiality policy reasons. The backport request has been created for 10.2 and 10.1 but the patch was not available at the time of writing this note.

Solution
There are several solutions for this issue:
Configure the database to not support case-sensitivity in passwords.
Create all-capital passwords
Pass a lowercase password by wrapping the password field in quotes.
e.g.
("Data Source=ORCL;User ID=scott;Password = \"tiger\"");
The bug is also fixed in the 10.2.0.4.0 Oracle Database Patch 6810189.


Comments

Be the first to post a comment

Post a comment