ODBC Connection – How to not hard-code username and password

[Lingmei Pan]

Does anyone know how to not hard-code username and password but get them on the fly when connecting to a data source using this command?  –>  odbc SQLConnect $hdbc $dsn SQL_NTS $usr SQL_NTS $passwd SQL_NTS

The following text is copied and pasted from odbcGuide.pdf.  It seems to imply that SQLConnect supports dialog boxes?

         SQLDriverConnect. Connects to data sources which require more information than the arguments supported by SQLConnect. Dialog boxes are not supported for this function.

Thanks for any help!

Topic

[Kevin Kinnell]

It looks to me like it is saying SQLDriverConnect doesn’t support dialog

boxes; I don’t think it implies anything about SQLConnect other than it

won’t take as many parameters as SQLDriverConnect.

I assume you want to do this with a GUI?  A quicker way might be to use

the Readline module and just use it to set usr and pwd from

a command line (if you don’t need the GUI).

Can you supply a little more information about what you want to do?

[Lingmei Pan]

Kevin, thanks for your reply.

Since the same site will be used by different customers who have different username and password to access their databases, I am trying to find a way to not having the customer go into the tcl script to change the hard-coded username and password.

A colleague’s past experience with another interface engine was to have the user configure their username and password (which would then be encrypted) upon set up.

I am just wondering if there is a way in Cloverleaf to get around hard-coded username and password, and how people usually implement it.

Thank you!

[Kevin Kinnell]

Lingmei,

I’ve been thinking about this, and I’m not sure I understand how Cloverleaf

is involved?  If they are logging into Cloverleaf directly (That Would Be Bad)

you’d have this issue, but you have this issue anytime you have a user

logging into an application across The Net.  I’m not sure how a logging into a

DB directly would have anything to do with Cloverleaf.

There are a lot of solutions, and setting up a configuration file with encrypted

passwords is a common one.  Otherwise you can have them go through a

web interface, or a command line interface (like sftp or ssh)…

Are you writing a script for the clients, so they can use it to transfer message

files?

[Bill Bertera]

I have a script that grabs the name/pass from odbc.ini for whatever datasource you’re connecting to. Is it possible to use multiple usernames for the same datasource?

[Lingmei Pan]

The Cloverleaf site that I am developing receives HL7 messages from one system, processes them, then sends the messages to another system.  During processing, I need to query the SQL database to retrieve additional data based on the patient and provider IDs received in the message.  To establish the connection to the database, the script calls SQLConnect $hdbc $dsn SQL_NTS $usr SQL_NTS $passwd SQL_NTS, which requires database login.  There should only be one login for the same datasource.

It looks like the “simplest” solution to avoid hard-coding username and password in the script is to read them from odbc.ini or an encrypted configuration file.  

Thanks for your suggestions.

[John Hamilton]

Ok, this seems to be new territory at lest for me. I have been using cloverleaf from it first version as HCI-link.  I have always used it as a backend processing application with no user having direct access to the system or even knowing any more that it is between our systems to handle protocol and mapping issues.

So I’m having a hard time trying to figure out what you are doing. I have done a bunch of ODBC stuff with cloverleaf to pull extra data out of systems and to poke stuff into databases.

But this is the first time I have heard of someone doing this. I would be real interested in what you are doing and I am sure if I understood better that. I could offer a better solution.

I know this may be asking a lot . But could you give maybe a process flow of what you are expecting of the engine and user.

What interface is the user going to get where they are prompted for the user-id and password.  Do they need to supply any other information.  

Are you doing some kind of HTTP front end stuff on the engine ?

[Lingmei Pan]

There will not be direct interaction between the general users and Cloverleaf site.  The process will run transparently behind the scene.  I guess the word “user” in my previous posts was misleading. It actually meant the person (IT personnel) who sets up and starts the Cloverleaf threads/process.  

The site I am developing is doing the regular stuff:  reading messages from a file, mapping and cleaning up a bit, forwarding and writing the messages to a file.  I should probably clarify that the site will not be used internally.  It will be distributed to different clinics/customers.

Supplying username and password for database login should only be one-time deal (unless the login changes), during the installation/set up of the site.  The script is working with my hard-coded username and password.  When the customers receive the site, I just don’t think it is a good idea for them to enter their username and password in the script.  It is not very secure to have them enter them in a flat file.  So, the encrypted configuration file seems to be the way to go.

If the site is for internal use, hard-coding the login is not an issue at all.  John, how do you usually implement this?

Thank you!

[Kevin Kinnell]

Oho!

We get our Cloverleaf via ,

but it’s not a “streamlined, user-friendly value-added” Cloverleaf.  I think

they tried that, and it burned them.  (I know they dropped doing Cloverleaf

tech support in favor of letting Cloverleaf do it.  I would not care to speculate

about why, but I am not sorry they did.)

I think you’re in Terra Nova, unless you talk to someone at that level.

For what it’s worth, it sounds like you are going to need a completely scripted

setup anyway, so you may as well just gather the information during setup

and generate the script on-the-fly into an access protected file.

(I know you were asking John, [Hi, John!] but what the heck.)

–kk

[Lingmei Pan]

Humm… I wonder what that is.   😉   Incorporating information gathering in the installer is a very good option.

Thanks for chipping in, Kevin.  All thoughts help.

[John Hamilton]

I pass the DSN, UserID and password as param’s to the tcl proc I’m using.    If you have different threads for each clinic you could do the same.  But I guess this all depends on how you have the connection and process setup.

[Lingmei Pan]

That’s what I am trying to do, but how do you pass the parameters?  By reading from a file?

[John Hamilton]

I have tcl proc that I call to process the data. Most of the time as a pre-xlate upoc.  When you add the tcl proc to the “Pre Procs:” list  there is a box to pass the paramaters as arguments to the procedure you are calling.

[Lingmei Pan]

Entering the parameters in the box will not be a feasible solution in our case, as the Cloverleaf site will be administered by our customers’ IT personnel.  Thanks though for your input.

[Steve Pringle]

I was wondering the same thing.

Is there a way (using some odbc function?) to get the username and password from the odbc.ini file rather than hardcoding them in the tcl proc, or passing them in as parameters?  Our odbc.ini file has the username and password listed, so why not get them from there?  That way the username and password are only in one place, the odbc.ini file.  Easier to manage, no code changes whenever a username/passwd changes.

Hardcoding these values is obviously not the best solution…

thanks,

Steve

[Lingmei Pan]

Thanks for your suggestions.  I too, ended up putting the DB login info in an .ini file under the site root directory.

[Jim Kosloskey]

Steve,

What we are doing here is to use the Connect String when connecting.

That allows us to use the odbc.ini file if we wish and define DSNs therein that can contain the UserId and Password if desired.

It is our feeling at this time that we won’t use the odbc.ini file for more than the place where all of the parameters for connection, etc. for the various supported DBs live and we will override those default settings at connect time via the Connect string. The rationale here is there is only one odbc.ini file and if it gets mangled due to an update, existing ODBC activity may be impacted.

We tried to figure out how we can have a separate odbc.ini file for each site or process or (even better) thread but could see the engine only loading one odbc.ini file for all.

By specifying those arguments that are different than the default at connect time (via the connect string) we can use the same dsn many times and yet not worry about hosing the odbc.ini.

We get our arguments for our generic proc (our intent is to use one proc for all ODBC activity – or at least most) via a lookup table (this can even work with Xlates).

Thus a DSN in the odbc.ini file is the basis for any Oracle DB (for example – could be any supported DB) and the lookup table is the override arguments which are plugged into the Connect String.

Each specific Use of an Oracle DB would have those arguments specified to adapt the dsn basis to point to the appropriate DB.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Steve Pringle]

JIm,

That all sounds very reasonable.  I like using a lookup table too.

But you lost me on the “Connect String”.  Do you mean the SQLConnect call:

“odbc SQLConnect $hdbc $dsn SQL_NTS $uid SQL_NTS $pwd SQL_NTS”

or?  Can you post an example?

thanks,

Steve

[Jim Kosloskey]

Steve,

Like this:

set err [odbc sqldriverconnect $hdbc $dsn $conn_string SQL_NTS “” 128 “” SQL_DRIVER_NOPROMPT]

Where $conn_string would contain the arguments (including the dsn name that would differ from the basis found in the referenced dsn) that the DataDirect drivers need.

By the way the $dsn field referenced in the above command is NOT the dsn to which I am referring (that field actually has a null value) the dsn is actually an argument in the connection string.

So a connection string might look like this: “Dsn=SQL Server Wire Protocol 5.2;Address=nnn.nnn.nnn.nnn,ppppp;Database=MyDB;UID=Me;PWD=ImOK”

Obviously the above is for Sql Server but it could just as well be for Oracle, or DB2, or any other supported DB.

The rules for a connection string, etc. are contained in the Healthvision provided documentation or from the DataDirect web site.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Steve Pringle]

I see.  Interesting.  At our site we find that the SQLConnect call is sufficient for our needs.

thanks very much!

–Steve

[Steve Pringle]

bump

[Author]

Replies