Password security [where is the password]

------=_Part_91350_431340.1169318613464
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi list,

I would like to know where is the password setted on the connection Dialog.
If it remains after the client shutdown it must be in some place in the har=
d
disk. I am afread about it. Can anyone tell me if someone can catch it
(hacker) ?


Regards
--=20
=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D- =3D-=3D-=3D-=3D=
-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D
Atenciosamente (Sincerely)
Ezequias Rodrigues da Rocha
=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D- =3D-=3D-=3D-=3D=
-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D -=3D-=3D-
A pior das democracias ainda =E9 melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/

------=_Part_91350_431340.1169318613464
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi list,

I would like to know where is the password setted on the co=
nnection Dialog. If it remains after the client shutdown it must be in some=
place in the hard disk. I am afread about it. Can anyone tell me if someon=
e can catch it (hacker) ?



Regards
--
=3D-=3D-=3D-=3D-=3D-=3D-=3D-=
=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D- =3D-=3D-=3D-=3D=
-=3D-=3D-=3D-=3D-=3D-=3D-=3D
       &=
nbsp;           &nbs=
p;            &=
nbsp; Atenciosamente (Sincerely)
     &nbs=
p;            &=
nbsp;     Ezequias Rodrigues da Rocha

=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D- =3D-=3D-=
=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D- =3D-=3D-=3D-=3D=
-
A pior das democracias ainda =E9 melhor do que a melhor das ditaduras<=
br>The worst of democracies is still better than the better of dictatorship=
s

http://ezequiasrocha.blo=
gspot.com/

------=_Part_91350_431340.1169318613464--

Re: Password security [where is the password]

> I would like to know where is the password setted on the connection Dialog.
> If it remains after the client shutdown it must be in some place in the hard
> disk. I am afread about it. Can anyone tell me if someone can catch it
> (hacker) ?

It's stored in registry:
System DSN:
HKLM\Software\ODBC\ODBC.INI\ in string value Password.
All the users with access to the computer can read it (don't forgot
the network registry access).

User DSN:
HKCU\Software\ODBC\ODBC.INI\ in string value Password.
If everything is properly only the user and Admin can read it.

File DSN:
in file
All the users with access to the file can read it.

Regards,

Luf

P.S. The admin could change the default ACL on registry tree.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: Password security [where is the password]

Mon, Jan 22, 2007 at 09:39:17AM -0200, Ezequias Rodrigues da Rocha napsal(a):
> The latest item (FILE) where is it specifically?

Hmmm, what OS are you using?
I suppose it's Windows. Have you already used "ODBC Data Source
Administrator"? If you aren't let's try it. It's located in Administrative
tools (in Control panel). There are some tabpages:
1) User DSN (stored in HKCU)
2) System DSN (stored in HKLM - you can specify the ACL with regedt32)
3) File DSN - you specify the file when you adding the DSN

> I must garantee that only admin users can see this password by now. Any
> other help

You can do it with 2) System DSN with correct registry ACL on the DSN or
with 3) File DSN with correct File ACL.

You have to try it yourself. I never need this.

Regards,

Luf

P.S. Please use group reply so users on the list could read it too.

> 2007/1/22, Ludek Finstrle :
> >
> >> I would like to know where is the password setted on the connection
> >Dialog.
> >> If it remains after the client shutdown it must be in some place in the
> >hard
> >> disk. I am afread about it. Can anyone tell me if someone can catch it
> >> (hacker) ?
> >
> >It's stored in registry:
> >System DSN:
> >HKLM\Software\ODBC\ODBC.INI\ in string value Password.
> >All the users with access to the computer can read it (don't forgot
> >the network registry access).
> >
> >User DSN:
> >HKCU\Software\ODBC\ODBC.INI\ in string value Password.
> >If everything is properly only the user and Admin can read it.
> >
> >File DSN:
> >in file
> >All the users with access to the file can read it.
> >
> >Regards,
> >
> >Luf
> >
> >P.S. The admin could change the default ACL on registry tree.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Re: Password security [where is the password]

------=_Part_109095_15942014.1169470095056
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

2007/1/22, Ludek Finstrle :
>
> Mon, Jan 22, 2007 at 09:39:17AM -0200, Ezequias Rodrigues da Rocha
> napsal(a):
> > The latest item (FILE) where is it specifically?
>
> Hmmm, what OS are you using?
> I suppose it's Windows. Have you already used "ODBC Data Source
> Administrator"? If you aren't let's try it. It's located in
> Administrative
> tools (in Control panel). There are some tabpages:
> 1) User DSN (stored in HKCU)
> 2) System DSN (stored in HKLM - you can specify the ACL with regedt32)
> 3) File DSN - you specify the file when you adding the DSN
>
> > I must garantee that only admin users can see this password by now. Any
> > other help
>
> You can do it with 2) System DSN with correct registry ACL on the DSN or
> with 3) File DSN with correct File ACL.


Many acronyms. My clients are Windows. I really don't know how to make this
work. What is ACL ?

I am not using dns only ip numbers in my network by now.

Regards
Ezequias

You have to try it yourself. I never need this.
>
> Regards,
>
> Luf
>
> P.S. Please use group reply so users on the list could read it too.
>
> > 2007/1/22, Ludek Finstrle :
> > >
> > >> I would like to know where is the password setted on the connection
> > >Dialog.
> > >> If it remains after the client shutdown it must be in some place in
> the
> > >hard
> > >> disk. I am afread about it. Can anyone tell me if someone can catch
> it
> > >> (hacker) ?
> > >
> > >It's stored in registry:
> > >System DSN:
> > >HKLM\Software\ODBC\ODBC.INI\ in string value Password.
> > >All the users with access to the computer can read it (don't forgot
> > >the network registry access).
> > >
> > >User DSN:
> > >HKCU\Software\ODBC\ODBC.INI\ in string value Password.
> > >If everything is properly only the user and Admin can read it.
> > >
> > >File DSN:
> > >in file
> > >All the users with access to the file can read it.
> > >
> > >Regards,
> > >
> > >Luf
> > >
> > >P.S. The admin could change the default ACL on registry tree.
>



--=20
=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D- =3D-=3D-=3D-=3D=
-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D
Atenciosamente (Sincerely)
Ezequias Rodrigues da Rocha
=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D- =3D-=3D-=3D-=3D=
-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D -=3D-=3D-
A pior das democracias ainda =E9 melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/

------=_Part_109095_15942014.1169470095056
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

2007/1/22, Ludek Finstrle <luf@pzkagis=
..cz>:

Re: Password security [where is the password]

Mon, Jan 22, 2007 at 10:48:15AM -0200, Ezequias Rodrigues da Rocha napsal(a):
> 2007/1/22, Ludek Finstrle :
> >Mon, Jan 22, 2007 at 09:39:17AM -0200, Ezequias Rodrigues da Rocha
> >napsal(a):
> >> The latest item (FILE) where is it specifically?
> >
> >Hmmm, what OS are you using?
> >I suppose it's Windows. Have you already used "ODBC Data Source
> >Administrator"? If you aren't let's try it. It's located in
> >Administrative
> >tools (in Control panel). There are some tabpages:
> >1) User DSN (stored in HKCU)
> >2) System DSN (stored in HKLM - you can specify the ACL with regedt32)
> >3) File DSN - you specify the file when you adding the DSN
> >
> >> I must garantee that only admin users can see this password by now. Any
> >> other help
> >
> >You can do it with 2) System DSN with correct registry ACL on the DSN or
> >with 3) File DSN with correct File ACL.
>
> Many acronyms. My clients are Windows. I really don't know how to make this
> work. What is ACL ?

ACL = access control list
file ACL (in explorer mouse right click on file -> Properties -> tab Security)
registry ACL (in regedt32 choose the key and in menu Security -> Permissions)
DSN = ODBC DataSource

Let's run "DataSources (ODBC)" or how is the manager named in Control Panel,
define some DSN (User x System x File) and then let's try change the
ACL for it in registry or in filesystem. Then you can verify it as admin
and normal user.

Feel free to ask more if something doesn't work as you expect.
I hope I give you all informations what you need.

Regards,

Luf

> >> 2007/1/22, Ludek Finstrle :
> >> >
> >> >> I would like to know where is the password setted on the connection
> >> >Dialog.
> >> >> If it remains after the client shutdown it must be in some place in
> >the
> >> >hard
> >> >> disk. I am afread about it. Can anyone tell me if someone can catch
> >it
> >> >> (hacker) ?
> >> >
> >> >It's stored in registry:
> >> >System DSN:
> >> >HKLM\Software\ODBC\ODBC.INI\ in string value Password.
> >> >All the users with access to the computer can read it (don't forgot
> >> >the network registry access).
> >> >
> >> >User DSN:
> >> >HKCU\Software\ODBC\ODBC.INI\ in string value Password.
> >> >If everything is properly only the user and Admin can read it.
> >> >
> >> >File DSN:
> >> >in file
> >> >All the users with access to the file can read it.
> >> >
> >> >Regards,
> >> >
> >> >Luf
> >> >
> >> >P.S. The admin could change the default ACL on registry tree.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: Password security [where is the password]

------=_Part_111313_8699748.1169477297210
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

I know that the correct odbc usage (on windows) is with a "Application
Server" on only one machine, but now we only have the capability to use
direct connection.

Further more in the future we will implement a server application. Now I
have another question:

My clients are Fat32 and I don't meant to change all clients to NTFS so my
Security TAB doesn't appears (I consider it occurs becouse the Filesystem).

Did I correct ?

Thank you so much for the explanations.

Regards

2007/1/22, Ludek Finstrle :
>
> Mon, Jan 22, 2007 at 10:48:15AM -0200, Ezequias Rodrigues da Rocha
> napsal(a):
> > 2007/1/22, Ludek Finstrle :
> > >Mon, Jan 22, 2007 at 09:39:17AM -0200, Ezequias Rodrigues da Rocha
> > >napsal(a):
> > >> The latest item (FILE) where is it specifically?
> > >
> > >Hmmm, what OS are you using?
> > >I suppose it's Windows. Have you already used "ODBC Data Source
> > >Administrator"? If you aren't let's try it. It's located in
> > >Administrative
> > >tools (in Control panel). There are some tabpages:
> > >1) User DSN (stored in HKCU)
> > >2) System DSN (stored in HKLM - you can specify the ACL with regedt32)
> > >3) File DSN - you specify the file when you adding the DSN
> > >
> > >> I must garantee that only admin users can see this password by now.
> Any
> > >> other help
> > >
> > >You can do it with 2) System DSN with correct registry ACL on the DSN
> or
> > >with 3) File DSN with correct File ACL.
> >
> > Many acronyms. My clients are Windows. I really don't know how to make
> this
> > work. What is ACL ?
>
> ACL =3D access control list
> file ACL (in explorer mouse right click on file -> Properties -> tab
> Security)
> registry ACL (in regedt32 choose the key and in menu Security ->
> Permissions)
> DSN =3D ODBC DataSource
>
> Let's run "DataSources (ODBC)" or how is the manager named in Control
> Panel,
> define some DSN (User x System x File) and then let's try change the
> ACL for it in registry or in filesystem. Then you can verify it as admin
> and normal user.
>
> Feel free to ask more if something doesn't work as you expect.
> I hope I give you all informations what you need.
>
> Regards,
>
> Luf
>
> > >> 2007/1/22, Ludek Finstrle :
> > >> >
> > >> >> I would like to know where is the password setted on the
> connection
> > >> >Dialog.
> > >> >> If it remains after the client shutdown it must be in some place
> in
> > >the
> > >> >hard
> > >> >> disk. I am afread about it. Can anyone tell me if someone can
> catch
> > >it
> > >> >> (hacker) ?
> > >> >
> > >> >It's stored in registry:
> > >> >System DSN:
> > >> >HKLM\Software\ODBC\ODBC.INI\ in string value Password.
> > >> >All the users with access to the computer can read it (don't forgot
> > >> >the network registry access).
> > >> >
> > >> >User DSN:
> > >> >HKCU\Software\ODBC\ODBC.INI\ in string value Password.
> > >> >If everything is properly only the user and Admin can read it.
> > >> >
> > >> >File DSN:
> > >> >in file
> > >> >All the users with access to the file can read it.
> > >> >
> > >> >Regards,
> > >> >
> > >> >Luf
> > >> >
> > >> >P.S. The admin could change the default ACL on registry tree.
>



--=20
=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D- =3D-=3D-=3D-=3D=
-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D
Atenciosamente (Sincerely)
Ezequias Rodrigues da Rocha
=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D- =3D-=3D-=3D-=3D=
-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D -=3D-=3D-
A pior das democracias ainda =E9 melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/

------=_Part_111313_8699748.1169477297210
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

I know that the correct odbc usage (on windows) is with a  "Appli=
cation Server" on only one machine, but now we only have the capabilit=
y to use direct connection.

Further more in the future we will imple=
ment a server application. Now I have another question:


My clients are Fat32 and I don't meant to change all clients to=
NTFS so my Security TAB doesn't appears (I consider it occurs becouse =
the Filesystem).

Did I correct ?

Thank you so much for the ex=
planations.


Regards

Re: Password security [where is the password]

Mon, Jan 22, 2007 at 12:48:17PM -0200, Ezequias Rodrigues da Rocha napsal=
(a):
> Further more in the future we will implement a server application. Now =
I
> have another question:
>=20
> My clients are Fat32 and I don't meant to change all clients to NTFS so=
my
> Security TAB doesn't appears (I consider it occurs becouse the Filesyst=
em).

Yes. You're right. All users are admins when you use Fat32. So you're
not able to store the password in the secure way on local machines.
You have to specify the password all the time or maybe some network=20
share from server should work?

Regards,

Luf

> 2007/1/22, Ludek Finstrle :
> >
> >Mon, Jan 22, 2007 at 10:48:15AM -0200, Ezequias Rodrigues da Rocha
> >napsal(a):
> >> 2007/1/22, Ludek Finstrle :
> >> >Mon, Jan 22, 2007 at 09:39:17AM -0200, Ezequias Rodrigues da Rocha
> >> >napsal(a):
> >> >> The latest item (FILE) where is it specifically?
> >> >
> >> >Hmmm, what OS are you using?
> >> >I suppose it's Windows. Have you already used "ODBC Data Source
> >> >Administrator"? If you aren't let's try it. It's located in
> >> >Administrative
> >> >tools (in Control panel). There are some tabpages:
> >> >1) User DSN (stored in HKCU)
> >> >2) System DSN (stored in HKLM - you can specify the ACL with regedt=
32)
> >> >3) File DSN - you specify the file when you adding the DSN
> >> >
> >> >> I must garantee that only admin users can see this password by no=
w.
> >Any
> >> >> other help
> >> >
> >> >You can do it with 2) System DSN with correct registry ACL on the D=
SN
> >or
> >> >with 3) File DSN with correct File ACL.
> >>
> >> Many acronyms. My clients are Windows. I really don't know how to ma=
ke
> >this
> >> work. What is ACL ?
> >
> >ACL =3D access control list
> >file ACL (in explorer mouse right click on file -> Properties -> tab
> >Security)
> >registry ACL (in regedt32 choose the key and in menu Security ->
> >Permissions)
> >DSN =3D ODBC DataSource
> >
> >Let's run "DataSources (ODBC)" or how is the manager named in Control
> >Panel,
> >define some DSN (User x System x File) and then let's try change the
> >ACL for it in registry or in filesystem. Then you can verify it as adm=
in
> >and normal user.
> >
> >Feel free to ask more if something doesn't work as you expect.
> >I hope I give you all informations what you need.
> >
> >Regards,
> >
> >Luf
> >
> >> >> 2007/1/22, Ludek Finstrle :
> >> >> >
> >> >> >> I would like to know where is the password setted on the
> >connection
> >> >> >Dialog.
> >> >> >> If it remains after the client shutdown it must be in some pla=
ce
> >in
> >> >the
> >> >> >hard
> >> >> >> disk. I am afread about it. Can anyone tell me if someone can
> >catch
> >> >it
> >> >> >> (hacker) ?
> >> >> >
> >> >> >It's stored in registry:
> >> >> >System DSN:
> >> >> >HKLM\Software\ODBC\ODBC.INI\ in string value Password.
> >> >> >All the users with access to the computer can read it (don't for=
got
> >> >> >the network registry access).
> >> >> >
> >> >> >User DSN:
> >> >> >HKCU\Software\ODBC\ODBC.INI\ in string value Password.
> >> >> >If everything is properly only the user and Admin can read it.
> >> >> >
> >> >> >File DSN:
> >> >> >in file
> >> >> >All the users with access to the file can read it.
> >> >> >
> >> >> >Regards,
> >> >> >
> >> >> >Luf
> >> >> >
> >> >> >P.S. The admin could change the default ACL on registry tree.
> >
>=20
>=20
>=20
> --=20
> =3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D- =3D-=3D-=3D=
-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D
> Atenciosamente (Sincerely)
> Ezequias Rodrigues da Rocha
> =3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D- =3D-=3D-=3D=
-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D -=3D-=3D-=3D-
> A pior das democracias ainda =E9 melhor do que a melhor das ditaduras
> The worst of democracies is still better than the better of dictatorshi=
ps
> http://ezequiasrocha.blogspot.com/

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

linked tables in MS Access

Hello,

I've read a post [1] about how MS Access is enjoyable and efficient when tables are connected to MS SQL.

But I have MS Access front end containing linked tables to Postgres via ODBC. And I'm struggling with slow performance. When I want to open a table which contains about 8000 records it takes more then 20 sec. When I want to move cursor at the last record it takes more than 60 extra seconds.
I'm not able to bind comboboxes directly to larger (more than 60 records) linked table because unrolling takes 20 sec.
All tables has defined primary key, of course.
I was trying to change indexes without any result.

I've already checked Postgres server log and MS Access queries are executed quickly (<500 ms).

Is this normal behavior? Does MS Access cooperates with MS SQL such better than with other DBMS via odbc?

Does anybody have better experience?

I was trying to communicate from ASP.NET with Postgres via OLE DB driver and it was without any performance problems.

Thank you

Jirka

[1]
http://www.utteraccess.com/forums/showflat.php?Cat=&Board=53 &Number=1321886&Zf=&Zw=odbc%20myth&Zg=0&Zl=a&Main=1321886&Se arch=true&where=&Zu=&Zd=g&Zn=&Zt=1&Zs=a&Zy=#Post1321886&Zp=

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: linked tables in MS Access

Jiří Nouza wrote:

> Hello,
> I've read a post [1] about how MS Access is enjoyable and efficient whe=
n tables are connected to MS SQL.
>
> But I have MS Access front end containing linked tables to Postgres via=
ODBC. And I'm struggling with slow performance. When I want to open a ta=
ble which contains about 8000 records it takes more then 20 sec. When I w=
ant to move cursor at the last record it takes more than 60 extra seconds=
..
> I'm not able to bind comboboxes directly to larger (more than 60 record=
s) linked table because unrolling takes 20 sec

Aren't you turning on ODBC trace ?

regards,
Hiroshi Inoue



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Re: linked tables in MS Access

> But I have MS Access front end containing linked tables to Postgres
> via ODBC. And I'm struggling with slow performance. When I want to
> open a table which contains about 8000 records it takes more then
> 20 sec. When I want to move cursor at the last record it takes more
> than 60 extra seconds.
>
> I've already checked Postgres server log and MS Access queries are
> executed quickly (<500 ms).
>
> Is this normal behavior? Does MS Access cooperates with MS SQL such
> better than with other DBMS via odbc?

I see no description of your configuration.
What's your ODBC settings? What ODBC driver do you use? What OS?
I hope you haven't enabled the mylog output.
How long takes it the psql to show 8000 records?

BTW let's try play with "use declare/fetch" and "cache size" at first.
And mylog output could help you to see where's the problem.

Regards,

Luf

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: linked tables in MS Access

pgsql-odbc-owner@postgresql.org wrote:

> Hello,
>=20
> I've read a post [1] about how MS Access is enjoyable and efficient
> when tables are connected to MS SQL.=20
>=20
> But I have MS Access front end containing linked tables to Postgres
> via ODBC. And I'm struggling with slow performance. When I want to
> open a table which contains about 8000 records it takes more then 20
> sec. When I want to move cursor at the last record it takes more than
> 60 extra seconds. I'm not able to bind comboboxes directly to larger
> (more than 60 records) linked table because unrolling takes 20 sec.
> All tables has defined primary key, of course.
> I was trying to change indexes without any result.
>=20
> I've already checked Postgres server log and MS Access queries are
> executed quickly (<500 ms).=20
>=20
> Is this normal behavior? Does MS Access cooperates with MS SQL such
> better than with other DBMS via odbc?=20
>=20
> Does anybody have better experience?
>=20
> I was trying to communicate from ASP.NET with Postgres via OLE DB
> driver and it was without any performance problems.=20

Hi,

I'm using MS Access, ODBC and Postgresql in several places, with big tables=
as well, and I don't have any performance problem.

I have always been using the default driver parameters, and it always worke=
d for me. But maybe you should play with that a little bit. And I think a f=
ew performance tuning tips have been discussed in this list in the past.

One thing you have to check, is wether you are using the MS Access 2000 for=
mat, under MS Access 2003. There is huge performance loss in this configura=
tion. Convert your database into the 2003 format, and problems will disappe=
ar.

Second thing: have you the opportunity to run tcpdump somewhere between the=
server and the client? You can activate the log of the server, too. This s=
hould give you useful "timing" informations.

Cheers,

Philippe

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Re: linked tables in MS Access

Philippe Lang a =E9crit :
> I'm using MS Access, ODBC and Postgresql in several places, with big ta=
bles as well, and I don't have any performance problem.
> I have always been using the default driver parameters, and it always w=
orked for me. But maybe you should play with that a little bit. And I thi=
nk a few performance tuning tips have been discussed in this list in the =
past.

I also use Access as a FrontEnd to PostgreSQL (we are migrating the backe=
nd).
The main problem is that all queries have to be written in PostgreSQL.
When joining linked tables in Access, performance is terrible ! I think I=
understand why, but some of my power-users would like to be able to crea=
te queries in Access and have no knowledge of SQL (so creating them in Po=
stgreSQL is not an option).

> One thing you have to check, is wether you are using the MS Access 2000=
format, under MS Access 2003. There is huge performance loss in this con=
figuration. Convert your database into the 2003 format, and problems will=
disappear.

Good to know !
I might give MSAccess2003 a try then !


--
Arnaud

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Re: [ODBC] linked tables in MS Access

Oh, I'm still feeling like a BFU :(

I'm using the last one version of odbc driver (08_02_0200) on Windows XP, 2=
000. Access is 2000 and 2003.

Of course I had commlog and mylog turned on. But I've never thought that wr=
iting some kb (ok, few megabytes) of text could have such big affect :(
When I was trying common windows odbc trace the disk worked very hard but p=
ostgres log didn't make full use of hard disk.

It seems that turning off helps very well. Access work by now commonly fas=
t.

8000 rows query is prosessed in 0.8 s, see log:
2007-01-23 00:01:42 odbcuser SELECT LOG: 00000: duration: 813.000 ms stat=
ement: select * from osoby
It could be adequate I suppose. Postgres is on laptop.

I will try to change "use declare/fetch" and "cache size" but it is hard to=
debug via mylog because it is unbelievable slow.

And try to convert Access file to 2003 version further.

Thank you very much I haven't to forget to check logging settings.

Jirka

> ------------ P=F9vodn=ED zpr=E1va ------------
> Od: Ludek Finstrle
> P=F8edm=ECt: Re: [ODBC] linked tables in MS Access
> Datum: 23.1.2007 10:26:52
> ----------------------------------------
> > But I have MS Access front end containing linked tables to Postgres
> > via ODBC. And I'm struggling with slow performance. When I want to
> > open a table which contains about 8000 records it takes more then
> > 20 sec. When I want to move cursor at the last record it takes more
> > than 60 extra seconds.
> >
> > I've already checked Postgres server log and MS Access queries are
> > executed quickly (<500 ms).
> >
> > Is this normal behavior? Does MS Access cooperates with MS SQL such
> > better than with other DBMS via odbc?
>
> I see no description of your configuration.
> What's your ODBC settings? What ODBC driver do you use? What OS?
> I hope you haven't enabled the mylog output.
> How long takes it the psql to show 8000 records?
>
> BTW let's try play with "use declare/fetch" and "cache size" at first.
> And mylog output could help you to see where's the problem.
>
> Regards,
>
> Luf

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: Re: [ODBC] linked tables in MS Access

Tue, Jan 23, 2007 at 11:21:58AM +0100, Jiøí Nouza napsal(a):
> Of course I had commlog and mylog turned on. But I've never thought
> that writing some kb (ok, few megabytes) of text could have such big
> affect :(

mylog is very detailed log (all SQL statement all getted values, ...)
and it's called very often in the code.
psqlodbc.log should be ok (there are few informations).

> It seems that turning off helps very well. Access work by now commonly=
fast.
>=20
> I will try to change "use declare/fetch" and "cache size" but it is
> hard to debug via mylog because it is unbelievable slow.

You don't need it if it's ok now for you. Use declare/fetch convert
SELECT statements to cursor so the result is fetched in "cache size"
blocks. Sometime it helps sometime it kills the performance.

Regards,

Luf

> > ------------ P=F9vodn=ED zpr=E1va ------------
> > Od: Ludek Finstrle
> > P=F8edm=ECt: Re: [ODBC] linked tables in MS Access
> > Datum: 23.1.2007 10:26:52
> > ----------------------------------------
> > > But I have MS Access front end containing linked tables to Postgres
> > > via ODBC. And I'm struggling with slow performance. When I want to
> > > open a table which contains about 8000 records it takes more then
> > > 20 sec. When I want to move cursor at the last record it takes more
> > > than 60 extra seconds.
> > >
> > > I've already checked Postgres server log and MS Access queries are
> > > executed quickly (<500 ms).
> > >
> > > Is this normal behavior? Does MS Access cooperates with MS SQL such
> > > better than with other DBMS via odbc?
> >
> > I see no description of your configuration.
> > What's your ODBC settings? What ODBC driver do you use? What OS?
> > I hope you haven't enabled the mylog output.
> > How long takes it the psql to show 8000 records?
> >
> > BTW let's try play with "use declare/fetch" and "cache size" at first=
..
> > And mylog output could help you to see where's the problem.
> >
> > Regards,
> >
> > Luf
>=20
> ---------------------------(end of broadcast)--------------------------=
-
> TIP 3: Have you checked our extensive FAQ?
>=20
> http://www.postgresql.org/docs/faq

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match