Thread: Intermittent ODBC problems and spurious usernames and tables
We're using a postgresql back end for a fairly complex Access database, with 10+ tables. We're running into a few oddities, and are trying to track some stuff down. We're using AccessXP, and mostly on WinXP (one Win2K client - but that doesn't seem to matter) Every time a connection to the DB is made, there are a couple of log entries that make no sense: 2003-10-20 14:08:10 LOG: connection received: host=66.xx.xx.xx port=2910 2003-10-20 14:08:10 FATAL: No pg_hba.conf entry for host 66.xx.xx.xx, user Admin, database foo There is no user "Admin" - the ODBC connection was set up with a different username. and: 2003-10-20 14:08:10 ERROR: Relation "msysconf" does not exist 2003-10-20 14:08:10 LOG: statement: SELECT Config, nValue FROM MSysConf Basically, the connection seems to work fine, although we've had some intermittent problems, and we're not sure if these might be the culprit. Thanks! -- .Michelle --------------------------- Michelle Murrain mmurrain@dbdes.com Database Designs Associates, Inc. Boston 617.889.0929 Amherst 413.253.2874 Cell 413.222.6350 www.dbdes.com AIM:pearlbear0 ICQ:129250575 "Our capacity to make peace with another person and with the world depends very much on our capacity to make peace with ourselves." -- Thich Nhat Hanh
Michelle Murrain wrote: > We're using a postgresql back end for a fairly complex Access database, > with 10+ tables. We're running into a few oddities, and are trying to > track some stuff down. We're using AccessXP, and mostly on WinXP (one > Win2K client - but that doesn't seem to matter) > > Every time a connection to the DB is made, there are a couple of log > entries that make no sense: > > 2003-10-20 14:08:10 LOG: connection received: host=66.xx.xx.xx > port=2910 > 2003-10-20 14:08:10 FATAL: No pg_hba.conf entry for host 66.xx.xx.xx, > user Admin, database foo > > There is no user "Admin" - the ODBC connection was set up with a > different username. Access is first trying to connect using the Access user id whose default is Admin. > > and: > > 2003-10-20 14:08:10 ERROR: Relation "msysconf" does not exist > 2003-10-20 14:08:10 LOG: statement: SELECT Config, nValue FROM MSysConf Access can be optimized a bit if you create a table on the server called MSysConf. It is attempting to determine your server settings: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/off2000/html/achowmsysconftableattachedsqldatabasess.asp Mike Mascari mascarm@mascari.com
On Mon, 2003-10-20 at 15:39, Mike Mascari wrote: > Access is first trying to connect using the Access user id whose > default is Admin. Is there a way to turn this behavior off? > Access can be optimized a bit if you create a table on the server > called MSysConf. It is attempting to determine your server settings: > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/off2000/html/achowmsysconftableattachedsqldatabasess.asp It looks like it might include some useful tweaks, but... Any advice on using this table - is it worth it? Does it create any problems I should be aware of? Do you use it? Thanks! -- .Michelle --------------------------- Michelle Murrain mmurrain@dbdes.com Database Designs Associates, Inc. Boston 617.889.0929 Amherst 413.253.2874 Cell 413.222.6350 www.dbdes.com AIM:pearlbear0 ICQ:129250575 "Our capacity to make peace with another person and with the world depends very much on our capacity to make peace with ourselves." -- Thich Nhat Hanh
Michelle Murrain wrote: > On Mon, 2003-10-20 at 15:39, Mike Mascari wrote: > >>Access is first trying to connect using the Access user id whose >>default is Admin. > > Is there a way to turn this behavior off? I think Access will only first attempt to connect using the current Access user id if there isn't a valid user id in the connect string associated with the ODBC link. I don't know of a way to turn that off specifically, although there might be. I do know you can: 1) Specify the userid in the ODBC driver before creating the link although this has an obvious security downside or 2) Use DSN-less ODBC access by prompting for the userid and password yourself in a Form and initialize Access with the userid and password via Visual Basic: http://archives.postgresql.org/pgsql-odbc/2003-07/msg00041.php > It looks like it might include some useful tweaks, but... > > Any advice on using this table - is it worth it? Does it create any > problems I should be aware of? I'm not sure. According to techdocs, PgAdmin has a tool to automatically create msysconf for you if you so choose: http://techdocs.postgresql.org/v2/Guides/Using%20Microsoft%20Access%20with%20PostgreSQL/view#31 > Do you use it? No. But I haven't deployed an Access-based application in a long time. Depending upon the nature of the application, the number of records fetched in a single Form instantiation, for example, taking the time to configure msysconf might make sense... Mike Mascari mascarm@mascari.com
You are on the right path -- to the point on initial login Jet might try to login to server database with its username and password (Admin and blank or whatever you are using with Access) unless you change this behavior. The trick is to manage the registry key called JetTryAuth (on the Access client machines). You can use regedit to get to your version of Jet (3.5 or 4.0) HKEY_LOCAL_MACHINE\Sofware\Microsoft\Jet\4.0\Engines\ODBC If you find JetTryAuth as 1, modify it to be 0. If you have multiple machines to set, you may want to use an INF file, or set it in an install. Using the MySysConf as a table on the server database is a completely optional performance tuning thing. Michelle Murrain wrote: > > On Mon, 2003-10-20 at 15:39, Mike Mascari wrote: > > Access is first trying to connect using the Access user id whose > > default is Admin. > > Is there a way to turn this behavior off? > > > Access can be optimized a bit if you create a table on the server > > called MSysConf. It is attempting to determine your server settings: > > > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/off2000/html/achowmsysconftableattachedsqldatabasess.asp > > It looks like it might include some useful tweaks, but... > > Any advice on using this table - is it worth it? Does it create any > problems I should be aware of? Do you use it? > > Thanks! > -- > .Michelle > --------------------------- > Michelle Murrain > mmurrain@dbdes.com > Database Designs Associates, Inc. > Boston 617.889.0929 > Amherst 413.253.2874 > Cell 413.222.6350 > www.dbdes.com > > AIM:pearlbear0 ICQ:129250575 > > "Our capacity to make peace with another person and with the world > depends very much on our capacity to make peace with ourselves." -- Thich Nhat Hanh > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org