Thread: Can't get ODBC from Windows to Linux/Postgres to work

Jeff Martin
704 Torrey Lane, Apartment D
Boalsburg, PA 16827
H814-466-7791
jeff@dgjc.org
www.dgjc.org

On Mon, 11 Mar 2002, Jeff Martin wrote:

> My clients need to access PostgreSQL data from their windows box.
> Trying to get ODBC to work from Windows to Linux/PostgreSQL.
>
> I have done the following:
>     1. configured and compiled PostgreSQL with the --enable-odbc flag
>     2. installed psqlodbc-07_01_0009.zip  on my windows box from
> odbc.postgresql.org
>     3. executed the command "psql -d template1 -f pgsql/share/odbc.sql
>     4. rebuilt my database.
>     5. created a new ODBC source on my windows bow pointing to my Linux
> machine and PostgreSQL database.
>
> I get the following error when trying to connect to the ODBC source from MS
> Access
>
> ODBC call failed:
> Could not connect to the server.
> Could not connect to the remote socket. (#101)[Microsoft][ODBC Driver
> Manager]Connection not open(#0)
>
>
> What step am I missing here?  Thanks for any help.

It won't hurt, but for the benefit of others: if you want to connect to PG
from Windows via ODBC, it's not neccessary to install with --enable-odbc
flag. That's to connect from Linux to Linux via ODBC.

Also, it's not strictly neccessary to add the odbc.sql stuff to the
database--these are helper functions commonly used by ODBC apps. Some apps
might not work without them, but your problem isn't related to that.

How, exactly did you set up your ODBC database source? What options?

Did you modify the postgresql.conf file to allow TCPIP connections (or,
alternatively run with the -i switch to do the same?)

Have you edited pg_hba.conf to allow connections from your Windows box's
IP?

Some help can be found on using Access with PostgreSQL at
http://joelburton.com/resources/pgaccess. Please consider adding to this
FAQ as you discover new things.

Thanks!


--

Joel BURTON  |  joel@joelburton.com  |  joelburton.com  |  aim: wjoelburton
Independent Knowledge Management Consultant

On Mon, 2002-03-11 at 23:03, Jeff Martin wrote:
> I get the following error when trying to connect to the ODBC source from MS
> Access
>
> ODBC call failed:
> Could not connect to the server.
> Could not connect to the remote socket. (#101)[Microsoft][ODBC Driver
> Manager]Connection not open(#0)
>
>
> What step am I missing here?  Thanks for any help.

Does pg_hba.conf allow TCP/IP access.  Is the postmaster listening for
TCP/IP connects.

Is there anything in the server's log?

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C

     "I am the vine, ye are the branches; He that abideth in
      me, and I in him, the same bringeth forth much fruit;
      for without me ye can do nothing."
                                           John 15:5

I've also had to use the ip address of the server instead of the name of
it.  can't figure out why though as all my other apps have no problems with
name service.
corey


-----Original Message-----
From:    Oliver Elphick [SMTP:olly@lfix.co.uk]
Sent:    Monday, March 11, 2002 12:34 PM
To:    jeff@dgjc.org
Cc:    pgsql-general@postgresql.org
Subject:    Re: [GENERAL] Can't get ODBC from Windows to Linux/Postgres to
work

On Mon, 2002-03-11 at 23:03, Jeff Martin wrote:
> I get the following error when trying to connect to the ODBC source from
MS
> Access
>
> ODBC call failed:
> Could not connect to the server.
> Could not connect to the remote socket. (#101)[Microsoft][ODBC Driver
> Manager]Connection not open(#0)
>
>
> What step am I missing here?  Thanks for any help.

Does pg_hba.conf allow TCP/IP access.  Is the postmaster listening for
TCP/IP connects.

Is there anything in the server's log?

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C

     "I am the vine, ye are the branches; He that abideth in
      me, and I in him, the same bringeth forth much fruit;
      for without me ye can do nothing."
                                           John 15:5


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

> Does pg_hba.conf allow TCP/IP access?
I think the problem is here.  The documentation within the pg_hba.conf
file suggests that all TCP/IP connections need to use IDENT authentication.
I just turned on the ident deamon on the Linux box hosting the database,
but still can't make the connection.  Anyone know more about ident
authentication with the postgres database?

Jeff

Jeff Martin
704 Torrey Lane, Apartment D
Boalsburg, PA 16827
H814-466-7791
jeff@dgjc.org <mailto:jeff@dgjc.org>
www.dgjc.org <http://www.dgjc.org>

At 3/11/02 4:17 PM, pgsql-general-owner@postgresql.org wrote:

>I think the problem is here.  The documentation within the pg_hba.conf
>file suggests that all TCP/IP connections need to use IDENT authentication.
>I just turned on the ident deamon on the Linux box hosting the database,
>but still can't make the connection.  Anyone know more about ident
>authentication with the postgres database?

With Red Hat Linux 7.2, I found that the default in /etc/identd.conf is
now:

result:encrypt = yes

I had to change that to:

result:encrypt = no

... and restart identd to get Postgres to work with IDENT.

Hope that helps.

--
Robert L Mathews, Tiger Technologies

"The trouble with doing something right the first time is that nobody
appreciates how difficult it was."

On Tue, 2002-03-12 at 01:51, Jeff Martin wrote:
> > Does pg_hba.conf allow TCP/IP access?
> I think the problem is here.  The documentation within the pg_hba.conf
> file suggests that all TCP/IP connections need to use IDENT authentication.
> I just turned on the ident deamon on the Linux box hosting the database,
> but still can't make the connection.  Anyone know more about ident
> authentication with the postgres database?

It is the remote (client) end of the connection that runs identd; in
this case, that is the Microsoft box.


    MS box ----->-- ODBC request ------> PostgreSQL server
                                                |
      --<--------- ident request -----<---------
      |
      -------->--- ident response ------>-------
                                                |
         <--- connection refused --- No <- validation OK?
                                                |
                                               Yes
                                                |
      <----------- ODBC connection -----<-------


identd responds to a request to know who is trying to make the
connection.  It is inherently insecure, since you are depending on the
client end to tell the truth.  In the case of a Microsft system, that
seems very unwise!

Quite likely, in this case, the connection problem is because the
Microsoft box is not running an ident server.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C

     "And now abideth faith, hope, love, these three; but
      the greatest of these is love."
                            I Corinthians 13:13

ODBC connectivity to my PostgreSQL/Linux box has been solved, thanks!

The problem was when I set the ODBC parameters on my windows box I
identified
my Linux box by name "www.myname.org".  However, since my ISP provides DNS
for
me the IP address returned was my external IP address rather than my
intranet address.
The solution was to simply put in my intranet address for the machine name.
Also related
was my permissions in the pg_hba.conf file.  I only want to allow access
from machines
on my intranet.  Presto it worked.  I did make one other change.  Windows
ODBC seems to
allow you to configure an ODBC as a user level or machine level.  I also
switched that
from the user level to the machine level ODBC.

Summary of other lessons learned from your responses.

1. If using IDENT authentication an identd deamon needs to run on the
Windows (client) box.
And yes there is a identd deamon available for windows.

2. The --enable-odbc compile flag for PostgreSQL is only needed when using
ODBC from
Linux to Linux.

Thanks for the help,

Jeff

Jeff Martin
704 Torrey Lane, Apartment D
Boalsburg, PA 16827
H814-466-7791
jeff@dgjc.org <mailto:jeff@dgjc.org>
www.dgjc.org <http://www.dgjc.org>

On 11 Mar 2002 at 15:50, Steve Lane wrote:

> It seems like this should be simple but I haven¹t found any obvious
> pointer in the documents.
>
> I need a timestamp field that¹s updated every time a record is touched,
> to show the last mod date.
>
> I have this working with a trigger just fine. But this means adding a
> new trigger for each table that needs this.
>
> Is there an easier way?

hi Steve,

i'm a complete newbie, so use this at your own risk... i ran into the
same question (+ i also want to keep track of the user touching the
record), and did it with inheritance:

---------------------------------------------------
-- this is a templatetable for the audit fields
create table au_col (
  mut_id varchar(100) not null default current_user,
  mut_timestamp timestamp not null default CURRENT_TIMESTAMP
);

-- function for updating the values
create function au_col()
returns opaque
as  'begin
      old.mut_id = current_user;
      old.mut_timestamp = CURRENT_TIMESTAMP;
      return old;
    end;'
language 'plpgsql';

-- trigger to call the function
create trigger au_col
  before update or delete
  on au_col
  for each row
  execute procedure au_col();

-- now, for every table you create, inherit it from au_col
create table land (
  id    serial primary key,
  code  char(2) unique not null,
  name  varchar(100) not null
) inherits (au_col);
---------------------------------------------------

HTH, HAND,

--
Jules