[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

ODBC DSN setup with password

Posted on 2006-04-17
11
Medium Priority
?
1,284 Views
Last Modified: 2008-03-10

I have an Access database consisting of tables linked to  Postgres database tables via an ODBC DSN.  However, everytime I open the Access database I have to enter a user and password to get to the tables.   I would like not to have to do this, as this is not a security risk for what I'm doing.  I supplied the user and password when I created the DSN but that didn't seem to amount to much.

In short, I would like to know how to setup an ODBC DSN such that I do not have to enter a password everytime I open the database.

-G
0
Comment
Question by:gmanpert
  • 7
  • 4
11 Comments
 
LVL 15

Expert Comment

by:m1tk4
ID: 16470999
You have to check "save password" option in Access when you are linking to these tables. Your password will get saved into the Access mdb file and it's not very secure but that's the only way to do it. You can't just store the password in the DSN - the DSN's are pretty much used only once when you link the table, they are read and the information from them is stored in the .mdb file. To re-read them you need to either remove the tables and reconnect them again or use Linked Tables Manager.
0
 
LVL 15

Expert Comment

by:m1tk4
ID: 16471046
This option - "Save password" appears at the last stage when you link the tables, when you actually select the available tables to be linked, it's a checkbox in the bottom right corner of the dialog.
0
 

Author Comment

by:gmanpert
ID: 16473890
Actually, that doesn't seem to work.  If I do what you suggest then reopen the database i get the following error:

'ODBC--connection to '{PostgreSQL}server.domain.edu' failed'

-g
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 15

Expert Comment

by:m1tk4
ID: 16473979
Did you enter the password in the PgSQL driver dialog (i.e. after you select the PostgreSQL as the driver)?

That's the way I have it working in about a dozen of Access databases.
0
 

Author Comment

by:gmanpert
ID: 16481763
Run that by me again?  I  entered PWD=password in the DSN file which gets used but that did not help.  I entered the password during the time I linked each of the tables in Access.

It has been so long ago, I can't even remember how I got the original Access DB linked to Postgres.

-g
0
 
LVL 15

Expert Comment

by:m1tk4
ID: 16482535
Don't enter the PW in the DSN, just type one in as you link them and don't forget to check "save password". Here is an example of DSN I used:

[ODBC]
DRIVER=PostgreSQL
UID=MyUserID
UseServerSidePrepare=0
ByteaAsLongVarBinary=0
BI=0
TrueIsMinus1=1
DisallowPremature=0
UpdatableCursors=1
LFConversion=1
ExtraSysTablePrefixes=dd_
CancelAsFreeStmt=0
Parse=0
BoolsAsChar=1
UnknownsAsLongVarchar=0
TextAsLongVarchar=1
UseDeclareFetch=0
Ksqo=1
Optimizer=1
CommLog=0
Debug=0
MaxLongVarcharSize=8190
MaxVarcharSize=254
UnknownSizes=0
Socket=4096
Fetch=100
ConnSettings=
ShowSystemTables=0
RowVersioning=0
ShowOidColumn=1
FakeOidIndex=1
Protocol=6.4
ReadOnly=0
PORT=5432
SERVER=myserver
DATABASE=mydb


You can edit them directly in Program FIles/Common Files/ODBC/DataSources.
0
 

Author Comment

by:gmanpert
ID: 16482951
unfortunately, when I do this I get the results I posted in my first response, namely the
"ODBC -- connection to ... " error.  Is there any documentation around on how to set up a Postgres DSN on a Windows machine?

-G
0
 
LVL 15

Expert Comment

by:m1tk4
ID: 16483100
What is the DSN type you are creating? File/System/Etc.
0
 

Author Comment

by:gmanpert
ID: 16489078
At this point I would be happy to create anything which works.  However, I've been trying to create a File DSN.

-g
0
 
LVL 15

Accepted Solution

by:
m1tk4 earned 1500 total points
ID: 16489567
Really weird, never had a problem with this. When you rest your mouse cursor over a linked table in Access, what shows up in the hint (Should start with ODBC;DRIVER={Postgres....)

BTW, what's your Access version?

Also, can you post your pg_hba.conf?
0
 
LVL 15

Expert Comment

by:m1tk4
ID: 16489962
What was the issue?
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
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.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Suggested Courses

865 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