Thread: 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
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)
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.
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.
--- 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/
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/
--- 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
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