Your program probably should be aware of multiple users using the same PC and the users using PC's on different sites or at home through a VPN or your clients will hate you.
I store the program and connection file as normal on the C drive and all users get these files as part of the install. The connection file is basically just an encrypted text file which stores several connection strings (for live systems, master systems, test system, development system, logging system, update server, other sites etc.. its amazing how many you end up with once your program/client gets big), I create a single connection file and encrypt it on my development pc and deploy it to all users pcs on install with the program. There are plenty of examples out there to do encryption/decryption of text strings.... best not to store the connection string in your exe!
on multi-site configurations I store which connection the user opens as default in a one byte file in the LOCALAPPDATA folder so it is user specific (if multiple users use this PC) - I use the
GetEnvironmentVariable('LOCALAPPDATA') function
to get the folder where it is stored - otherwise if it is single site I just connect to the first connection, if you use an INI file to store the users config and dont use the registry like me this folder would be the best place to store it, however like you deduced I generally store the configuration for the site/user on the database in readable text strings (like an ini) in tables in the database so I or the DBA can see, modify, delete them if required
We only have one user account on the database server and all authentication of users is done through a user table.
When the program starts I read the user table from the master server and match username in user table to the pc username. In the simplest case it could be as simple as the pascal/sql pseudo code below
SELECT UserName FROM account WHERE LoginID="'+GetEnvironmentvariable('username')+'"' then
raise exception.create('Access is denied to '+GetEnvironmentvariable('username'));
I then check any qualifications tables load configuration related to the user/PC to start the app. on multi-site configurations I store some config settings (such as label printers) on the site server.
I also generally have a central updates server and as part of the startup it compares the MD5 value of all the install files on local disk to the MD5 value and version on the server and begins a background update thread if they are out of date. Ditto I have a logging server so I can track what they are doing and what queries are getting executed.
Its worked well for the last 16 years (touch wood) though there are definitely better ways
Recent
