Cannot link to postgres 8.0.0 databases using ODBC from Access
I have cross posted this issue here following suggestion on the pg_odbc
list
I'd try turning off log_discconnections, and see if you get any joy. If
that works and the log still displays problems
related to BackendRun, I'd email the pg-hackers list.
We have an Access 97 database front end connected via ODBC (V8.00.00.04)
Windows 2000 Desktop to a Postgres
8.0.0 backend running on Windows 2000 Server. A long running complex
transaction failed possibly because of a timeout. After the failure the
server logging was changed to identify the application fault. We then could
not unable relink to any database on the server from any client. The error
returned is "The database does not exist on the server or user
authentication failed. (#105)".
The advice in other similar posts is to check the hba.conf file and
database user access (snips below). I cannot see anything wrong with the
set up although I see that the postgres server log shows the first two
lines with UNKNOWN user and client IP address. The remaining
lines are as I would expect.
Further investigation has identified a fault that I suppose is applicable
to the combination of
Postgres 8.0.0 ODBC driver running on Windows 2000 server and desktop.
I have two postgresql.conf files, one is the default out-of-the-box
postgresql.conf file, the other has many of the logging options turned on
(I have put the diff between the two files at the end)
The default config works fine, the modified config fails in that the
postgres ODBC does not work (tested with Access 97,2000 and Perl ODBC
connections) giving the error "The database does not exist on the server or
user authentication failed. (#105)".
Only ODBC seems to be affected. All other connections from php, pgadmin
work fine.
I have demonstrated this problem consistently on two completely different
and brand new installations of P8.0.0 (W2K server and desktop). I have
tried many (but not all) combinations but have not been able to pin down
exactly what combination of settings generates the
error. Turning off log_disconnections does not in itself cure the problem.
Regards
Laurie
The diff of the two config files is
207a208
client_min_messages = log
212a214
log_min_messages=info
214a217
log_error_verbosity = verbose
218a222
log_min_error_statement = info
230a235
log_connections = true
231a237
log_disconnections = true
232a239
log_duration = true
240a248,249
log_line_prefix = '%u %d %r %t %c %x'
241a251
log_statement = 'all'
hba.conf
host all all 127.0.0.1/0 trust
host all all 10.35.10.248 255.255.240.0 trust
and the sample database used in the ODBC link.
CREATE DATABASE rjl_pilot
WITH OWNER = ldb
ENCODING = 'UNICODE'
TABLESPACE = pg_default;
GRANT ALL ON DATABASE rjl_pilot TO public;
GRANT ALL ON DATABASE rjl_pilot TO ldb;
The psqllog file extract:
conn=159252552, PGAPI_DriverConnect( in)='DSN=rjl_pilot;',
fDriverCompletion=1
DSN info: DSN='rjl_pilot',server='za29rugbr',port='5433',dbase
='rjl_pilot',user='ldb',passwd='xxxxx'
onlyread='0',protocol='6.4',showoid='0',fakeoidindex
='0',showsystable='0'
conn_settings='',conn_encoding='OTHER'
translation_dll='',translation_option=''
Global Options: Version='08.00.0002', fetch=100, socket=4096,
unknown_sizes=0, max_varchar_size=254, max_longvarchar_size=8190
disable_optimizer=1, ksqo=1, unique_index=1,
use_declarefetch=0
text_as_longvarchar=1, unknowns_as_longvarchar=0,
bools_as_char=1 NAMEDATALEN=64
extra_systable_prefixes='dd_;', conn_settings=''
conn_encoding='OTHER'
conn=159252552, query=' '
NOTICE from backend during send_query: 'LOG: statement:
'
NOTICE from backend during send_query: 'LOG: duration: 0.000 ms
'
CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect',
errnum=105, errmsg='The database does not exist on the server
or user authentication failed.'
------------------------------------------------------------
henv=158138320, conn=159252552, status=0, num_stmts=16
sock=158129576, stmts=158109160, lobj_type=-999
---------------- Socket Info -------------------------------
socket=908, reverse=0, errornumber=0, errormsg='(NULL)'
buffer_in=159281008, buffer_out=158112112
buffer_filled_in=30, buffer_filled_out=0, buffer_read_in=30
The mylog file extract:
[6180]: CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
='DSN=rjl_pilot;'
[6180]: CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
[6180]: CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
[6180]: CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
='xxxxx',port='',onlyread='',protocol='',conn_settings
='',disallow_premature=-1)
[6180]: CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
[6180]: CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
[6180]: CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
[6180]: CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
A7=100;A8=4096;A9=0;B0=254;B1=8190;B2=1;B3=1;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;[6180]CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'CC_connect:
entering...
[6180]: CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
database = 'rjl_pilot', username = 'ldb', password='xxxxx'
[6180]: CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
[6180]: CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
[6180]: CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
[6180]: CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
[6180]: CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
[6180]: CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
[6180]: CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
[6180]: CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
[6180]: CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
[6180]: CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
[6180]: CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
[6180]: CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
[6180]: CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
[6180]: CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
[6180]: CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
[6180]: CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
[6180]: CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
[6180]: CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
[6180]: CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
'
[6180]: CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
[6180]: CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
[6180]: CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
[6180]: CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
'
[6180]: CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
[6180]: CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
exist)
[6180]: CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
[6180]: CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
[6180]: CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
[6180]: CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
[6180]: CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.'
errnum=105, errmsg='The database does not exist on the server
or user authentication failed.'
...
The postgres server log has the following
[unknown] [unknown] 2005-02-17 18:55:59 4214e8bf.9dc 0LOG: 00000:
connection received: host=10.35.10.248 port=4630
[unknown] [unknown] 2005-02-17 18:55:59 4214e8bf.9dc 0LOCATION:
BackendRun, postmaster.c:2656
ldb rjl_pilot 10.35.10.248(4630) 2005-02-17 18:55:59 4214e8bf.9dc 0LOG:
00000: connection authorized: user=ldb database=rjl_pilot
ldb rjl_pilot 10.35.10.248(4630) 2005-02-17 18:55:59 4214e8bf.9dc
0LOCATION: BackendRun, postmaster.c:2728
ldb rjl_pilot 10.35.10.248(4630) 2005-02-17 18:55:59 4214e8bf.9dc
1076305LOG: 00000: statement:
ldb rjl_pilot 10.35.10.248(4630) 2005-02-17 18:55:59 4214e8bf.9dc
1076305LOCATION: pg_parse_query, postgres.c:473
ldb rjl_pilot 10.35.10.248(4630) 2005-02-17 18:55:59 4214e8bf.9dc 0LOG:
00000: duration: 16.000 ms
ldb rjl_pilot 10.35.10.248(4630) 2005-02-17 18:55:59 4214e8bf.9dc
0LOCATION: exec_simple_query, postgres.c:1021
ldb rjl_pilot 10.35.10.248(4630) 2005-02-17 18:55:59 4214e8bf.9dc 0LOG:
00000: disconnection: session time: 0:00:00.21 user=ldb database=rjl_pilot
host=10.35.10.248 port=4630
ldb rjl_pilot 10.35.10.248(4630) 2005-02-17 18:55:59 4214e8bf.9dc
0LOCATION: log_disconnections, postgres.c:3403
:.________________
CONFIDENTIALITY : This e-mail and any attachments are confidential and
may be privileged. If you are not a named recipient, please notify the
sender immediately and do not disclose the contents to another person, use
it for any purpose or store or copy the information in any medium.
