Thread
-
Cannot link to postgres 8.0.0 databases using ODBC from Access
laurie.burrow@powerconv.alstom.com — 2005-02-21T12:50:44Z
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]**** PGAPI_DriverConnect: fDriverCompletion=1, connStrIn ='DSN=rjl_pilot;' [6180]our_connect_string = 'DSN=rjl_pilot;' [6180]attribute = 'DSN', value = 'rjl_pilot' [6180]copyAttributes: DSN='rjl_pilot',server='',dbase='',user='',passwd ='xxxxx',port='',onlyread='',protocol='',conn_settings ='',disallow_premature=-1) [6180]globals.extra_systable_prefixes = 'dd_;' [6180]our_connect_string = 'DSN=rjl_pilot;' [6180]attribute = 'DSN', value = 'rjl_pilot' [6180]CopyCommonAttributes: 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]CC_connect: entering... [6180]CC_connect(): DSN = 'rjl_pilot', server = 'za29rugbr', port = '5433', database = 'rjl_pilot', username = 'ldb', password='xxxxx' [6180]connecting to the server socket... [6180]connection to the server socket succeeded. [6180]sizeof startup packet = 292 [6180]sent the authentication block. [6180]sent the authentication block successfully. [6180]gonna do authentication [6180]read 15, global_socket_buffersize=4096 [6180]auth got 'R' [6180]areq = 0 [6180]auth got 'K' [6180]auth got 'Z' [6180]sending an empty query... [6180]send_query(): conn=159252552, query=' ' [6180]send_query: done sending query [6180]in QR_Constructor [6180]exit QR_Constructor [6180]read 21, global_socket_buffersize=4096 [6180]send_query: got id = 'N' [6180]~~~ NOTICE: 'LOG: statement: ' [6180]read 30, global_socket_buffersize=4096 [6180]send_query: got id = 'I' [6180]send_query: got id = 'N' [6180]~~~ NOTICE: 'LOG: duration: 0.000 ms ' [6180]send_query: got id = 'Z' [6180]got no result from the empty query. (probably database does not exist) [6180]QResult: in DESTRUCTOR [6180]QResult: free memory in, fcount=0 [6180]QResult: free memory out [6180]QResult: exit DESTRUCTOR [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.' ... 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. -
Re: Cannot link to postgres 8.0.0 databases using ODBC from Access
Patrick Welche <prlw1@newn.cam.ac.uk> — 2005-02-23T12:33:38Z
On Mon, Feb 21, 2005 at 12:50:44PM +0000, laurie.burrow@powerconv.alstom.com wrote: > host all all 127.0.0.1/0 trust Should that not be 127.0.0.1/32 ? or even host all all 127.0.0.1 255.255.255.255 trust ? Patrick