Thread: ODBC DSN Connections

ODBC DSN Connections

From
mroach@ogclearinghouse.com (Mark)
Date:
Have about 30 System DSN's setup on one PC.  These were setup
manually.  Need to deploy them on about 100 other PC's.  Are there any
nifty utilities that will let me copy them from one system to another
without manually entering them?

Thanks, Mark

Re: ODBC DSN Connections

From
"Philippe Lang"
Date:
Hello,

I have made some tests with .REG files, and it looks like what I need. After installing the driver on each PC, you
simplyrun the .REG file, with all the keys of your ODBC connection. It allows you to configure a different username for
eachuser, which is fine if you want to manage permissions server-side. 

I guess this .REG file can be run from a domain login script.

Does anyone have a better solution for distributing DSN (and drivers?) on a large network?

Philippe Lang


-----Message d'origine-----
De : Mark [mailto:mroach@ogclearinghouse.com]
Envoyé : samedi, 1. novembre 2003 23:31
À : pgsql-odbc@postgresql.org
Objet : [ODBC] ODBC DSN Connections


Have about 30 System DSN's setup on one PC.  These were setup
manually.  Need to deploy them on about 100 other PC's.  Are there any
nifty utilities that will let me copy them from one system to another
without manually entering them?

Thanks, Mark

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: ODBC DSN Connections

From
Andrew Ayers
Date:
Philippe Lang wrote:
> Hello,
>
> I have made some tests with .REG files, and it looks like what I need. After installing the driver on each PC, you
simplyrun the .REG file, with all the keys of your ODBC connection. It allows you to configure a different username for
eachuser, which is fine if you want to manage permissions server-side. 
>
> I guess this .REG file can be run from a domain login script.
>
> Does anyone have a better solution for distributing DSN (and drivers?) on a large network?

The solution is to not use DSNs - instead, write your application to use
a DSN-less connection. This way, the application, when it runs, sets up
its own "temporary" DSN(s), and you don't have a slew of them all over
the place, easy for the user to get at and (possibly) screw up. Note
that this doesn't eliminate the need for installing the driver on each
machine. I am not sure what to do to get around this issue.

The only time you should use a system DSN is if it is on a single
machine, and the DSN is likely to be used by many different applications
on that machine (like perhaps a web or application server of some sort).
That way, if the DB location changes, you update the one DSN that all
apps use, and they are all happy. If you had each app set up on one
machine using its own DSN-less connection, then you would likely need to
update each app to tell it where the new location was.

Now, perhaps the DSNs being put onto each machine are used by multiple
apps on the machine. This can be a thorny issue. DSNs are tough to
maintain, and DSN-less connections are also tough to maintain across
multiple apps.

I would probably make an app that is run at startup that has a DSN-less
connection to a simple database set up on a small system that will
likely never change. This database would hold information telling the
location and DSN name for a bunch of databases. The app reading the
database would be put on each of the PC's. Now, from here, one of two
things can occur. Either each app can query this one app, to get the DSN
name and location for the DSN-less connection, or at startup, the app
can run, read the small database, and *overwrite* old system DSN
entries. So that, the user always has the latest up-to-date system DSNs
set up (you manage the DSN database yourself, likely through a small web
application or something). If they change them, the next reboot resolves
any issues. I suppose a service could also be created that runs on
startup that auto-checks the DSNs against the DB periodically and
updates as well, as needed. I suppose a third option could be that each
application could use the single DSN to first read the small DB to know
how to set up the DSN-less connection itself, instead of creating the
system DSNs each time.

As far as the drivers are concerned, I would say you need to do the same
as you currently do for installs of any other software on the systems.
Of course, this can be time consumming. I will tell you what I did for
an application I take "care" of:

This application is a VB executable that is distributed across many
machines in the company I work for. For a long time, we were compiling
the app, rebuilding the install, and telling users to download and
install the application. After a while, this became a big deal, because
sometime people wouldn't install the new application, and continue to
use the old app version, or we had to send people to each machine to
update it.

I ended up writing a little application that would act as an "update"
utility. Each user of the main application being distributed has to log
into the application. I set up on the database a flag that says "update
this user". When the flag is checked (handled by admins on the main
application), and the user nexts logs in, it checks the flag and if set,
only allows the user to update or log out - nothing else. They *cannot*
log back into the system until they update the application. When they
decide to update, the system runs the updater application, and kills the
main application. The updater application reads an update area on the
network, and copies over any updates (it is actually more complex than
this - the updater utility is actually a custom scripting engine that
allows it to do many different things to the user machine, under control
of an update script that administrators can change to suit the update
needed). After it is done, it "unflags" the user's update flag, and
restarts the main application to allow them to re-login.

There is other logic that allows the updater (and the main application)
to update the updater (the issue involved is that you can't overwrite a
running EXE under Windows - so you have to "juggle" the applications,
shutting one down while running another that does the update - updating
the updater is a pain, but the functionality is there). So - we then
only had to make one last "manual" update to everybody's system - and
then we could do all future updates remotely. Since its inception, it
has worked as designed, and has virtually eliminated our need to
manually update users systems, at least for the application which it
services (and technically, it could probably be used for other updates,
such as external software and such, but we haven't had a need to explore
that path yet).

Something like this could be what you need to implement, to distribute
the driver (and any other future updates) to users. If you write such a
system, try to anticipate *all* possible uses, and design the scripting
system to allow for this (for example, my updater allows for moving,
copying, and deleting of files on the client machines - there are other
commands in the scripting language for displaying message boxes,
yes/no/ok/cancel boxes, etc - as well as branching of the script so the
user can do certain things). I know when I first created the updater, I
didn't originally design it to update the updater - then I found I
needed to do this. However, because I had designed in a ton of stuff
into the updater on the first round, creative scripting for the updater
(and some extra coding in the application) allowed me to get around this
  issue, and update the updater. Had I not designed a lot of extra
functionality into the system in the beginning, I likely would have had
a problem when it came time to update the updater.

I hope this makes sense...

Andrew Ayers
Phoenix, Arizona

-- CONFIDENTIALITY NOTICE --

This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain
informationthat is privileged, confidential and exempt from disclosure under applicable law. If you are not the
intendedaddressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use,
copy,disclose or distribute to anyone the message or any information contained in the message. If you have received
thismessage in error, please immediately advise the sender by reply email, and delete the message. Thank you. 

Re: ODBC DSN Connections

From
"Philippe Lang"
Date:
Oh yes, this makes sense! I think your "update utility" can be something really useful. I really have to implement
somethinglike this. 

Just one quick note, though: My client will be implemented with MS Access 2000, and I had a lot of problems with
permissionsand DSN-less connection. Permissions simply do not work, at least for me, unless I use a good old DSN, with
ausername and a password inside. I use a lot of "Pass-through queries" in Access, which are generated dynamically, in
orderto pass parameters to Postgresql, and process the results inside a report. I use linked tables as well, and
pass-throughqueries run "on the fly", in order to retreive the result of a stored procedure from VBA. The only way to
makethings work perfectly was to use a DSN. It should't be the case, I agree, but that's what I noticed... 
That's why I use a .REG file, that updates the username and password at login.

I hope this makes sense as well!


-----Message d'origine-----
De : Andrew Ayers [mailto:aayers@eldocomp.com]
Envoyé : lundi, 3. novembre 2003 18:38
À : Philippe Lang
Cc : Mark; pgsql-odbc@postgresql.org
Objet : Re: [ODBC] ODBC DSN Connections


Philippe Lang wrote:
> Hello,
>
> I have made some tests with .REG files, and it looks like what I need. After installing the driver on each PC, you
simplyrun the .REG file, with all the keys of your ODBC connection. It allows you to configure a different username for
eachuser, which is fine if you want to manage permissions server-side. 
>
> I guess this .REG file can be run from a domain login script.
>
> Does anyone have a better solution for distributing DSN (and drivers?) on a large network?

The solution is to not use DSNs - instead, write your application to use
a DSN-less connection. This way, the application, when it runs, sets up
its own "temporary" DSN(s), and you don't have a slew of them all over
the place, easy for the user to get at and (possibly) screw up. Note
that this doesn't eliminate the need for installing the driver on each
machine. I am not sure what to do to get around this issue.

The only time you should use a system DSN is if it is on a single
machine, and the DSN is likely to be used by many different applications
on that machine (like perhaps a web or application server of some sort).
That way, if the DB location changes, you update the one DSN that all
apps use, and they are all happy. If you had each app set up on one
machine using its own DSN-less connection, then you would likely need to
update each app to tell it where the new location was.

Now, perhaps the DSNs being put onto each machine are used by multiple
apps on the machine. This can be a thorny issue. DSNs are tough to
maintain, and DSN-less connections are also tough to maintain across
multiple apps.

I would probably make an app that is run at startup that has a DSN-less
connection to a simple database set up on a small system that will
likely never change. This database would hold information telling the
location and DSN name for a bunch of databases. The app reading the
database would be put on each of the PC's. Now, from here, one of two
things can occur. Either each app can query this one app, to get the DSN
name and location for the DSN-less connection, or at startup, the app
can run, read the small database, and *overwrite* old system DSN
entries. So that, the user always has the latest up-to-date system DSNs
set up (you manage the DSN database yourself, likely through a small web
application or something). If they change them, the next reboot resolves
any issues. I suppose a service could also be created that runs on
startup that auto-checks the DSNs against the DB periodically and
updates as well, as needed. I suppose a third option could be that each
application could use the single DSN to first read the small DB to know
how to set up the DSN-less connection itself, instead of creating the
system DSNs each time.

As far as the drivers are concerned, I would say you need to do the same
as you currently do for installs of any other software on the systems.
Of course, this can be time consumming. I will tell you what I did for
an application I take "care" of:

This application is a VB executable that is distributed across many
machines in the company I work for. For a long time, we were compiling
the app, rebuilding the install, and telling users to download and
install the application. After a while, this became a big deal, because
sometime people wouldn't install the new application, and continue to
use the old app version, or we had to send people to each machine to
update it.

I ended up writing a little application that would act as an "update"
utility. Each user of the main application being distributed has to log
into the application. I set up on the database a flag that says "update
this user". When the flag is checked (handled by admins on the main
application), and the user nexts logs in, it checks the flag and if set,
only allows the user to update or log out - nothing else. They *cannot*
log back into the system until they update the application. When they
decide to update, the system runs the updater application, and kills the
main application. The updater application reads an update area on the
network, and copies over any updates (it is actually more complex than
this - the updater utility is actually a custom scripting engine that
allows it to do many different things to the user machine, under control
of an update script that administrators can change to suit the update
needed). After it is done, it "unflags" the user's update flag, and
restarts the main application to allow them to re-login.

There is other logic that allows the updater (and the main application)
to update the updater (the issue involved is that you can't overwrite a
running EXE under Windows - so you have to "juggle" the applications,
shutting one down while running another that does the update - updating
the updater is a pain, but the functionality is there). So - we then
only had to make one last "manual" update to everybody's system - and
then we could do all future updates remotely. Since its inception, it
has worked as designed, and has virtually eliminated our need to
manually update users systems, at least for the application which it
services (and technically, it could probably be used for other updates,
such as external software and such, but we haven't had a need to explore
that path yet).

Something like this could be what you need to implement, to distribute
the driver (and any other future updates) to users. If you write such a
system, try to anticipate *all* possible uses, and design the scripting
system to allow for this (for example, my updater allows for moving,
copying, and deleting of files on the client machines - there are other
commands in the scripting language for displaying message boxes,
yes/no/ok/cancel boxes, etc - as well as branching of the script so the
user can do certain things). I know when I first created the updater, I
didn't originally design it to update the updater - then I found I
needed to do this. However, because I had designed in a ton of stuff
into the updater on the first round, creative scripting for the updater
(and some extra coding in the application) allowed me to get around this
  issue, and update the updater. Had I not designed a lot of extra
functionality into the system in the beginning, I likely would have had
a problem when it came time to update the updater.

I hope this makes sense...

Andrew Ayers
Phoenix, Arizona

-- CONFIDENTIALITY NOTICE --

This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain
informationthat is privileged, confidential and exempt from disclosure under applicable law. If you are not the
intendedaddressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use,
copy,disclose or distribute to anyone the message or any information contained in the message. If you have received
thismessage in error, please immediately advise the sender by reply email, and delete the message. Thank you. 

Re: ODBC DSN Connections

From
Jeff Eckermann
Date:
--- Philippe Lang <philippe.lang@attiksystem.ch>
wrote:

> Just one quick note, though: My client will be
> implemented with MS Access 2000, and I had a lot of
> problems with permissions and DSN-less connection.
> Permissions simply do not work, at least for me,
> unless I use a good old DSN, with a username and a
> password inside. I use a lot of "Pass-through
> queries" in Access, which are generated dynamically,
> in order to pass parameters to Postgresql, and
> process the results inside a report.

There's nothing to stop you from making a connection
at the time you do your pass-through query, in the
same code.  But you would need to hard-code your
connection parameters, or prompt the user for them;
either approach has its drawbacks.  Or you could read
the parameters from another location, but that brings
you right back to your DSN idea...

But I agree with you in general, I like the
simplification that DSNs allow.  Although I am not
supporting any large number of client installations.


__________________________________
Do you Yahoo!?
Exclusive Video Premiere - Britney Spears
http://launch.yahoo.com/promos/britneyspears/

Re: ODBC DSN Connections

From
"Philippe Lang"
Date:
In Ms-Access, DSN-less connections for pass-through queries, or linked tables work, but I noticed strange problems
regardingpermissions. The only way to make them work - perfectly! - was to use a DSN. Maybe I'm doing something wrong? 

-----Message d'origine-----
De : Jeff Eckermann [mailto:jeff_eckermann@yahoo.com]
Envoyé : lundi, 3. novembre 2003 20:57
À : Philippe Lang
Cc : pgsql-odbc@postgresql.org
Objet : Re: [ODBC] ODBC DSN Connections


--- Philippe Lang <philippe.lang@attiksystem.ch>
wrote:

> Just one quick note, though: My client will be
> implemented with MS Access 2000, and I had a lot of
> problems with permissions and DSN-less connection.
> Permissions simply do not work, at least for me,
> unless I use a good old DSN, with a username and a
> password inside. I use a lot of "Pass-through
> queries" in Access, which are generated dynamically,
> in order to pass parameters to Postgresql, and
> process the results inside a report.

There's nothing to stop you from making a connection
at the time you do your pass-through query, in the
same code.  But you would need to hard-code your
connection parameters, or prompt the user for them;
either approach has its drawbacks.  Or you could read
the parameters from another location, but that brings
you right back to your DSN idea...

But I agree with you in general, I like the
simplification that DSNs allow.  Although I am not
supporting any large number of client installations.


__________________________________
Do you Yahoo!?
Exclusive Video Premiere - Britney Spears
http://launch.yahoo.com/promos/britneyspears/

Re: ODBC DSN Connections

From
Jeff Eckermann
Date:
--- Philippe Lang <philippe.lang@attiksystem.ch>
wrote:
> In Ms-Access, DSN-less connections for pass-through
> queries, or linked tables work, but I noticed
> strange problems regarding permissions. The only way
> to make them work - perfectly! - was to use a DSN.
> Maybe I'm doing something wrong?

Hard to say, without seeing code, and the exact error
messages.  But this is really an issue with Access,
not PostgreSQL.  You would be better off researching
this in Access newsgroups, or in the Microsoft
knowledge base.

__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

Re: ODBC DSN Connections

From
"Philippe Lang"
Date:
This is not really an error I had. Simply, as soon as I made a query with a username that had full access on the
tables,I kept these permissions for the next user, even if it had a more restricted access. Access seems to "remember"
somethingabout  the connections. But I totally agree, this is an Access issue, not a Postgresql one. 

-----Message d'origine-----
De : Jeff Eckermann [mailto:jeff_eckermann@yahoo.com]
Envoyé : mardi, 4. novembre 2003 16:30
À : Philippe Lang
Cc : pgsql-odbc@postgresql.org
Objet : RE: [ODBC] ODBC DSN Connections


--- Philippe Lang <philippe.lang@attiksystem.ch>
wrote:
> In Ms-Access, DSN-less connections for pass-through
> queries, or linked tables work, but I noticed
> strange problems regarding permissions. The only way
> to make them work - perfectly! - was to use a DSN.
> Maybe I'm doing something wrong?

Hard to say, without seeing code, and the exact error
messages.  But this is really an issue with Access,
not PostgreSQL.  You would be better off researching
this in Access newsgroups, or in the Microsoft
knowledge base.

__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree