ORA-01017 when password contains euro sign

[gmis]

Hi.

I've two Oracle Databases: Prod1 and Test1 (both on 11G)

PROD1:
NLS_CHARACTERSET WE8ISO8859P15

Test1:
NLS_CHARACTERSET WE8MSWIN1252

On both databases i've a user called MYUSER with the
password "€" (simple euro-sign). I've set the password with Oracle SQLDeveloper like this
alter user MYUSER identified by "€";

ODAC-Version is 9.6.20 for RAD Studio 10 Seattle.

I'm trying to connect to...
Prod1 using ODAC: failed with ORA-01017
Test1 using ODAC: succeeded
Prod1 using SQLDeveloper: succeeded
Test1 using SQLDeveloper: succeded

TOraSession:
.Options.Charset = ''
.Options.Direct = true
.Options.UnicodeEnvironment = false
.Options.UseUnicode = false

Here is what i've tried so far:

1)
Changed TOraSession to:
.Options.UnicodeEnvironment = true
.Options.UseUnicode = true
=> failed with ORA-01017 on Prod1

2)
Changed TOraSession to:
.Options.Charset = 'WE8MSWIN1252'
=> failed with ORA-01017 on Prod1

3)
Changed TOraSession to:
.Options.Charset = 'WE8ISO8859P15'
=> failed with ORA-01017 on Prod1

4)
Changed Password on both Databases to a character
which is defined in 7-BIT Ascii e. g. "~" like this
alter user MYUSER identified by "~";
=> login with ODAC succeeded on Prod1 and Test1

It seems to be a problem regarding wrong character-sets on client and server.
What do i need to do to connect to Prod1? Specifying the character-set in the torasession seems not to work.

Thanks.

[gmis]

Hi.

I still can't connect to the oracle-db with NLS_CHARACTERSET=WE8ISO8859P15 when i use the password "€" (decimal 164 in WE8ISO8859P15) (i got an EOraError with ORA-01017 on every login).
A connection to the oracle-db with NLS_CHARACTERSET=WE8MSWIN1252 with the same password "€" (decimal 128 in WE8MSWIN1252) works.

The problem seems to occur when the password has a character which isn't defined in the 7-Bit-Range of the ascii-table. Hence no character which is greater than dec(127) like € or § are allowed.

The password has been set with Oracle SQL Developer using this statement:

Code: Select all

alter user MYUSER identified by "€";

Code: Select all

select ascii('€') from dual;
-- gives me "164" on the WE8ISO8859P15 DB
-- gives me "128" on the WE8MSWIN1252 DB

The login credentials seems to be transmitted in the wrong characterset.

Code: Select all

show parameter SEC_CASE_SENSITIVE_LOGON;
-- gives me TRUE on the WE8ISO8859P15 DB
-- gives me TRUE on the WE8MSWIN1252 DB 

Here is a sample to reproduce the issue (Delphi 10 Seattle with Devart ODAC 9.6.20 Professional and Oracle 11.2.0.4):

Code: Select all

unit oratest;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, DBAccess, OdacVcl, OraCall, Data.DB, Ora,
  Vcl.StdCtrls;

type
  TForm1 = class(TForm)
    OraSession1: TOraSession;
    ConnectDialog1: TConnectDialog;
    Button1: TButton;
    Label1: TLabel;
    procedure Button1Click(Sender: TObject);
    procedure OraSession1AfterConnect(Sender: TObject);
    procedure OraSession1Error(Sender: TObject; E: EDAError; var Fail: Boolean);
  private

  public

  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
begin
  OraSession1.Connect;
end;

procedure TForm1.OraSession1AfterConnect(Sender: TObject);
begin
  if OraSession1.Connected then
    Label1.Caption := 'connected';
end;

procedure TForm1.OraSession1Error(Sender: TObject; E: EDAError;
  var Fail: Boolean);
begin
  Label1.Caption := 'connection failed';
end;

end.

Code: Select all

object Form1: TForm1
  Left = 0
  Top = 0
  Caption = 'Form1'
  ClientHeight = 152
  ClientWidth = 578
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object Label1: TLabel
    Left = 208
    Top = 64
    Width = 31
    Height = 13
    Caption = 'Label1'
  end
  object Button1: TButton
    Left = 208
    Top = 8
    Width = 177
    Height = 25
    Caption = 'Connect to DB'
    TabOrder = 0
    OnClick = Button1Click
  end
  object OraSession1: TOraSession
    Options.Direct = True
    ConnectDialog = ConnectDialog1
    AfterConnect = OraSession1AfterConnect
    OnError = OraSession1Error
    Left = 32
    Top = 32
  end
  object ConnectDialog1: TConnectDialog
    Caption = 'Connect'
    ConnectButton = 'Connect'
    CancelButton = 'Cancel'
    Server.Caption = 'Server'
    Server.Visible = True
    Server.Order = 1
    UserName.Caption = 'User Name'
    UserName.Visible = True
    UserName.Order = 2
    Password.Caption = 'Password'
    Password.Visible = True
    Password.Order = 3
    Home.Caption = 'Home Name'
    Home.Visible = False
    Home.Order = 0
    Direct.Caption = 'Direct'
    Direct.Visible = False
    Direct.Order = 6
    Schema.Caption = 'Schema'
    Schema.Visible = False
    Schema.Order = 4
    Role.Caption = 'Connect Mode'
    Role.Visible = False
    Role.Order = 5
    Left = 136
    Top = 32
  end
end

Is this a bug? Or am i doing something wrong?
Any ideas? Do you need some further information?

Thank you very much.

[MaximG]

We have investigated the ODAC behavior when using Oracle 11.2.0.4 with NLS_CHARACTERSET WE8ISO8859P15 and found no issues: when using a '€' symbol as a user password, connection to database is established successfully. A small console application proves that:

Code: Select all

program EuroApp;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils, DB, Ora;

var
  OraSession: TOraSession;
begin
  OraSession := TOraSession.Create(nil);
  try
    OraSession.Options.Direct := True;
    OraSession.Server := '< Oracle Server with WE8ISO8859P15 >:1521:sn=< Oracle Serviсe Name >';
    OraSession.Username := '< Oracle user name >';
    OraSession.Password := '€';
    OraSession.Connect;
    if OraSession.Connected then
      WriteLn('Connected !');
    ReadLn;
    OraSession.Disconnect;
  finally
    OraSession.Free;
  end;
end.

Try to modify the application, so that the database connection error reproduces.

[gmis]

I've changed the following lines of your sample:

Code: Select all

...
OraSession.Server := 'localhost:1522:testdb';
OraSession.Username := 'myuser';
OraSession.Password := '€';
...

Unfortunately i still can't connect.

I' ve created another database:

Code: Select all

-- "select * from nls_database_parameters":
NLS_LANGUAGE	AMERICAN
NLS_TERRITORY	AMERICA
NLS_CURRENCY	$
NLS_ISO_CURRENCY	AMERICA
NLS_NUMERIC_CHARACTERS	.,
NLS_CHARACTERSET	WE8MSWIN1252
NLS_CALENDAR	GREGORIAN
NLS_DATE_FORMAT	DD-MON-RR
NLS_DATE_LANGUAGE	AMERICAN
NLS_SORT	BINARY
NLS_TIME_FORMAT	HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT	DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT	HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT	DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY	$
NLS_COMP	BINARY
NLS_LENGTH_SEMANTICS	BYTE
NLS_NCHAR_CONV_EXCP	FALSE
NLS_NCHAR_CHARACTERSET	AL16UTF16
NLS_RDBMS_VERSION	11.2.0.4.0

Code: Select all

-- "select * from nls_instance_parameters":
NLS_LANGUAGE	GERMAN
NLS_TERRITORY	GERMANY
NLS_SORT	
NLS_DATE_LANGUAGE	
NLS_DATE_FORMAT	
NLS_CURRENCY	
NLS_NUMERIC_CHARACTERS	
NLS_ISO_CURRENCY	
NLS_CALENDAR	
NLS_TIME_FORMAT	
NLS_TIMESTAMP_FORMAT	
NLS_TIME_TZ_FORMAT	
NLS_TIMESTAMP_TZ_FORMAT	
NLS_DUAL_CURRENCY	
NLS_COMP	BINARY
NLS_LENGTH_SEMANTICS	BYTE
NLS_NCHAR_CONV_EXCP	FALSE

As you can see it has the NLS_CHARACTERSET of WE8MSWIN1252.
I've tried to connect to this db and got the same (ORA-01017) error.
So it seems that it has nothing to do with the nls_characterset (sorry for that).

Code: Select all

-- Executed on the db i can't connect to:
-- "select name, value from v$parameter order by name;")

active_instance_count	
aq_tm_processes	1
archive_lag_target	0
asm_diskgroups	
asm_diskstring	
asm_power_limit	1
asm_preferred_read_failure_groups	
audit_sys_operations	FALSE
audit_trail	DB
awr_snapshot_time_offset	0
background_core_dump	partial
backup_tape_io_slaves	FALSE
bitmap_merge_area_size	1048576
blank_trimming	FALSE
buffer_pool_keep	
buffer_pool_recycle	
cell_offload_compaction	ADAPTIVE
cell_offload_decryption	TRUE
cell_offloadgroup_name	
cell_offload_parameters	
cell_offload_plan_display	AUTO
cell_offload_processing	TRUE
circuits	
client_result_cache_lag	3000
client_result_cache_size	0
clonedb	FALSE
cluster_database	FALSE
cluster_database_instances	1
cluster_interconnects	
commit_logging	
commit_point_strength	1
commit_wait	
commit_write	
compatible	11.2.0.4.0
control_file_record_keep_time	7
control_management_pack_access	DIAGNOSTIC+TUNING
cpu_count	8
create_bitmap_area_size	8388608
create_stored_outlines	
cursor_bind_capture_destination	memory+disk
cursor_sharing	EXACT
cursor_space_for_time	FALSE
db_block_buffers	0
db_block_checking	FALSE
db_block_checksum	TYPICAL
db_block_size	8192
db_cache_advice	ON
db_cache_size	0
db_create_file_dest	
db_domain	
db_file_multiblock_read_count	128
db_file_name_convert	
db_files	200
db_flashback_retention_target	1440
db_flash_cache_file	
db_flash_cache_size	0
db_keep_cache_size	0
db_lost_write_protect	NONE
db_recovery_file_dest_size	4385144832
db_recycle_cache_size	0
db_securefile	PERMITTED
db_ultra_safe	OFF
db_unrecoverable_scn_tracking	TRUE
dbwr_io_slaves	0
db_writer_processes	1
db_16k_cache_size	0
db_2k_cache_size	0
db_32k_cache_size	0
db_4k_cache_size	0
db_8k_cache_size	0
ddl_lock_timeout	0
deferred_segment_creation	TRUE
dg_broker_start	FALSE
disk_asynch_io	TRUE
distributed_lock_timeout	60
dml_locks	1088
dnfs_batch_size	4096
dst_upgrade_insert_conv	TRUE
enable_ddl_logging	FALSE
enable_goldengate_replication	FALSE
event	
fal_client	
fal_server	
fast_start_io_target	0
fast_start_mttr_target	0
fast_start_parallel_rollback	LOW
fileio_network_adapters	
file_mapping	FALSE
filesystemio_options	
fixed_date	
gcs_server_processes	0
global_context_pool_size	
global_names	FALSE
global_txn_processes	1
hash_area_size	131072
hi_shared_memory_address	0
hs_autoregister	TRUE
ifile	
instance_groups	
instance_number	0
instance_type	RDBMS
instant_restore	FALSE
java_jit_enabled	TRUE
java_max_sessionspace_size	0
java_pool_size	0
java_restrict	none
java_soft_sessionspace_limit	0
job_queue_processes	1000
large_pool_size	0
ldap_directory_access	NONE
ldap_directory_sysauth	no
license_max_sessions	0
license_max_users	0
license_sessions_warning	0
listener_networks	
lock_name_space	
lock_sga	FALSE
log_archive_duplex_dest	
log_archive_format	ARC%S_%R.%T
log_archive_local_first	TRUE
log_archive_max_processes	4
log_archive_min_succeed_dest	1
log_archive_start	FALSE
log_archive_trace	0
log_buffer	7979008
log_checkpoint_interval	0
log_checkpoints_to_alert	FALSE
log_checkpoint_timeout	1800
log_file_name_convert	
max_dispatchers	
max_dump_file_size	unlimited
max_enabled_roles	150
max_shared_servers	
memory_max_target	0
memory_target	0
nls_calendar	
nls_comp	BINARY
nls_currency	
nls_date_format	
nls_date_language	
nls_dual_currency	
nls_iso_currency	
nls_language	GERMAN
nls_length_semantics	BYTE
nls_nchar_conv_excp	FALSE
nls_numeric_characters	
nls_sort	
nls_territory	GERMANY
nls_time_format	
nls_timestamp_format	
nls_timestamp_tz_format	
nls_time_tz_format	
object_cache_max_size_percent	10
object_cache_optimal_size	102400
olap_page_pool_size	0
open_cursors	300
open_links	4
open_links_per_instance	4
optimizer_capture_sql_plan_baselines	FALSE
optimizer_dynamic_sampling	2
optimizer_features_enable	11.2.0.4
optimizer_index_caching	0
optimizer_index_cost_adj	100
optimizer_mode	ALL_ROWS
optimizer_secure_view_merging	TRUE
optimizer_use_invisible_indexes	FALSE
optimizer_use_pending_statistics	FALSE
optimizer_use_sql_plan_baselines	TRUE
os_authent_prefix	OPS$
os_roles	FALSE
O7_DICTIONARY_ACCESSIBILITY	FALSE
parallel_adaptive_multi_user	TRUE
parallel_automatic_tuning	FALSE
parallel_degree_limit	CPU
parallel_degree_policy	MANUAL
parallel_execution_message_size	16384
parallel_force_local	FALSE
parallel_instance_group	
parallel_io_cap_enabled	FALSE
parallel_max_servers	120
parallel_min_percent	0
parallel_min_servers	0
parallel_min_time_threshold	AUTO
parallel_server	FALSE
parallel_server_instances	1
parallel_servers_target	128
parallel_threads_per_cpu	2
permit_92_wrap_format	TRUE
pga_aggregate_target	1708130304
plscope_settings	identifiers:all
plsql_ccflags	
plsql_code_type	INTERPRETED
plsql_debug	TRUE
plsql_optimize_level	0
plsql_v2_compatibility	FALSE
plsql_warnings	DISABLE:ALL
pre_page_sga	FALSE
processes	150
processor_group_name	
query_rewrite_enabled	TRUE
query_rewrite_integrity	enforced
rdbms_server_dn	
read_only_open_delayed	FALSE
recovery_parallelism	0
recyclebin	on
redo_transport_user	
remote_dependencies_mode	TIMESTAMP
remote_listener	
remote_login_passwordfile	EXCLUSIVE
remote_os_authent	FALSE
remote_os_roles	FALSE
replication_dependency_tracking	TRUE
resource_limit	FALSE
resource_manager_cpu_allocation	8
resource_manager_plan	
result_cache_max_result	5
result_cache_max_size	25690112
result_cache_mode	MANUAL
result_cache_remote_expiration	0
resumable_timeout	0
rollback_segments	
sec_case_sensitive_logon	TRUE
sec_max_failed_login_attempts	10
sec_protocol_error_further_action	CONTINUE
sec_protocol_error_trace_action	TRACE
sec_return_server_release_banner	FALSE
serial_reuse	disable
session_cached_cursors	50
session_max_open_files	10
sessions	248
sga_max_size	5133828096
sga_target	5133828096
shadow_core_dump	none
shared_memory_address	0
shared_pool_reserved_size	44459622
shared_pool_size	0
shared_servers	1
shared_server_sessions	
skip_unusable_indexes	TRUE
smtp_out_server	
sort_area_retained_size	0
sort_area_size	65536
sql_trace	FALSE
sqltune_category	DEFAULT
sql92_security	FALSE
standby_archive_dest	%ORACLE_HOME%\RDBMS
standby_file_management	MANUAL
star_transformation_enabled	FALSE
statistics_level	TYPICAL
streams_pool_size	0
tape_asynch_io	TRUE
thread	0
timed_os_statistics	0
timed_statistics	TRUE
trace_enabled	TRUE
tracefile_identifier	
transactions	272
transactions_per_rollback_segment	5
undo_management	AUTO
undo_retention	900
undo_tablespace	UNDOTBS1
use_indirect_data_buffers	FALSE
use_large_pages	TRUE
utl_file_dir	
workarea_size_policy	AUTO
xml_db_events	enable

The database i can connect to runs on 11.2.0.3. I'm pretty sure there are more
differences between those databases than only the nls_characterset.

Which db-parameters influences the direct-mode connection?
Can you provide me a debug.exe which i can use to provide you more information
about my database connection. Maybe we can tackle the problem this way.

Thanks!

[MaximG]

Using the provided application, we connected successfully to databases with WE8ISO8859P15 and WE8MSWIN1252 charsets. Therefore it is quite difficult for us to determine which of the parameters of your database instance don't allow to use the euro symbol as a password. Try contacting your DB Oracle administrator to solve the issue.