ODBC DSN setup

Posted on 2006-04-19
Last Modified: 2012-05-05
I have a postgres database setup on a remote server.  The currently working DSN on my windows laptop was set up by someone else.  When I run PERL programs on my laptop which query the database (and work fine) the name of the DSN is 'tdsn'.   Here is what threw me and what I need help with.  On my windows laptop, under
>control panel >administrative tools >Data Sources(ODBC)
  There are three tabs - System DSN, User DSN, File DSN.

Originally, there was both a System DSN and a File DSN named 'tdsn', which I thought controlled the access to my Postgresql database.  However, I have since deleted both of those entries.  Presently, I have no entries under any of the DSN tabs with the name
'tdsn'.  I rebooted.  However, my PERL programs still work fine to query the Postgres database on the remote computer.  

 I knew nothing about regedit or the registry, so I did some reading and can now run regedit.  In it I checked:
HKEY_LOCAL_MACHINE >software > ODBC    ...and I see

  > ODBC.INI     ...which has all of my local System DSN's I use to connect to other Access database files.  Each of which looks like this:
   DSN_name > Engines > Jet

I did a search of my entire harddrive for pg_hba.conf and only found one version under c:\cygwin\usr\share\postgresql
  which I suspect has nothing to do with anything.

I could not find a pg_hba.conf file on the main server which is running the postgres database.

I have an icon on my laptop which connects to an Access database which is linked to the Postgres tables.  When I click on the properties of that file is shows:
 target: \\server\directory1\subdirectory\accessdb.mdb
Anyway, I would like to know how to set up an ODBC DSN to access the postgres database on the remote server.  Any insights would be appreciated.


Question by:gmanpert
    LVL 15

    Expert Comment

    1. Are you sure your Perl code was using DSN and not DSN-less connections?

    2. pg_hba.conf file should be on your server where Postgres is running in /var/lib/pgsql/data


    Author Comment

    No, I'm not sure... so I'll have to look into how PERl could be running in DSN-less mode.

    Still not finding the pg_hba.conf file althought there are about 7 pg_hba.conf.example files.

    How would you go about setting up a DSN for PG on a remote computer?

    LVL 15

    Accepted Solution

    Go to Control Panel -> Admin Tools -> Data Sources (ODBC),

    Select "File DSN" tab, Click Add

    Select "PostgreSQL" click Next

    Enter a DSN name (like MyNewDSN) click Next

    Click "Finish"

    A dialog "PostgreSQL Connection" will show up, fill in ALL fields, click "Connection"

    On page 1, uncheck "Bools as Char", click "Page 2"

    On Page 2, check "True is -1", OID/Show Column and Fake Index., click OK

    Back in "PG connection dialog", click OK

    Back in DSN List, click OK.


    In access, create new DB,

    Go to File/Get External Data/Link Tables

    in "Files of Type" choose ODBC.

    Select your DSN ("MyNewDSN") and click OK

    "PG connection" dialog will prompt you for the password again. Enter it and click again.

    Select all tables you need linked, check "Save password", click OK


    This is it. Just tried myself.

    LVL 15

    Expert Comment

    You don't need to create a DSN on the remote computer. The DSN is used ONCE when you link the tables, afterwards, you can just copy the mdb file and it should stay connected. Make sure that pg_hba.conf allows connecting from all computers you are going to be using mdb on and that authentication is set to "password", not "indent".

    Author Comment

    Dude - that totally rocks !  At least that solves one problem.

    Mucho thanks.
    LVL 15

    Expert Comment

    De nada ;) What's the other one?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Suggested Solutions

    Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: ( This requires some add-o…
    Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
    Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now