db.connect — why is ODBC required if not used?

I want to use an ODBC system DSN to control access to my SQL Server database from all my channels. So, I have created the ODBC system DSN, and my db.connect sees it.

Why am I then required to specify the api, user, and password in the db.connect if it is already in the DSN?

Thanks

Chuck

I don’t pretend to know the exact answer, but ODBC call SQLConnect typically would take 3 arguments dsn name, username and password.
On the other hand, if for connection to database (in ODBC DSN settings) you can use same credentials as Iguana Service Logon account, then username and password can be left empty.

I forgot to mention – we’re using SQL Authentication, so I don’t think we would be able to use the credentials for the logon account.

There are a couple of reasons we want to make use of the DSN. First, management doesn’t want the user/password strings visible in the script. Second, if we have to change the credentials, we will have to edit every script that accesses the database.

Chuck

not quite. some users read user/password from database tables, on the fly. some read from external files. some read from ‘shared’ modules. so that user/password need to be adjusted in one place only. the advantage in reading user/password on the fly is that user/password never show in scripts.

I agree. I want to read on the fly and from one place only. That place is the DSN. Since it is required by the db.connect, why can’t I use the user/password from it?

Chuck

I don’t believe the password can be stored in the DSN. The ODBC Administrator prompts for it so you can do things like set the default database and other database-specific settings, but it doesn’t retain it.

If you don’t want passwords in the clear, I think the best (and easiest to administer) option is to set Server authentication to Windows, and run Iguana under an AD account with the appropriate access. The user and password parameters are still required when calling db.connect(), but they can be empty strings when using Windows authentication.

Jeff Drumm ◊ VP and COO ◊ HICG, LLC. ◊ http://www.hicgrp.com

Ah-ha, that’s the answer — passwords aren’t being saved by ODBC.

I didn’t know that.

Thanks Jeff for clearing things up!

Chuck

Hi Lev

I was trying to do this with a MySQL ODBC connection, but used the Oracle_ODBC api parameter (as I did see a specific one for MySQL) just as a test. I was able to connect to the ODBC instance with the connection name and blank strings for user and password! Awesome!

I think we may have found a way to “hide” the DB logins!

Cheers
Ugo

I believe Jeff is still right that SQL Server’s ODBC driver will not allow you to save the password and so you still can’t hide the DB logins. I tested earlier just to verify and wasn’t able to connect by leaving the username and password blank.

I also tested with Postgres and can confirm Postgres saves your credentials in your ODBC connection so it’s possible to use empty strings for user and password in Iguana with this DB as well.

Hi Wade

Good to know thanks!

Next thing I need to figure out… This will help me hide the login details (at least fro MySQL), but still does not stop a user from changing the password of the odbc connection to gain access to the DB that way.

Cheers
Ugo

Jeff,

We aren’t using AD, so I just created a separate Windows account for Iguana running with Administrator privs. The Iguana service is now running under that account. I was able to remove the user/password from my channel scripts and all is working well.

Does Iguana need to run with Administrator privs or can it be a standard account?

Thanks

Chuck

That’s a good question.

Iguana shouldn’t need Administrator privileges, but my attempts to use a non-administrator level account have met with failure; Iguana would not recognize its license key nor would it allow me to re-enter it. I could get to the License Entitlement page, but updating the key would fail. Once I granted the account Administrator privileges, though, the issue went away.

So, to make a long story short: Administrator privileges appear to be required 🙂

Jeff Drumm ◊ VP and COO ◊ HICG, LLC. ◊ http://www.hicgrp.com

I think Iguana will on windows needs to have access to registry reading privileges. It’s interesting – with virtualization become de rigour for how things are set up since each virtual machine becomes a single use appliance which itself sandboxes things more. Everything depends a little on context.

You can do a manual install for Iguana of course which should make it fairly transparent to try out the minimum of privileges you need.

In theory it makes sense to try and reduce the privileges of any service/daemon as much as you can. I know that for shared drive access it’s usually works better to have Iguana work on the specific user ID.

Chuck what is the production context you are running in? In your own company’s data center or in your hospital customer’s data centre? On the same machine or separate from the database and application?

Our production is firewalled inside our datacenter, running on a VM containing both Iguana and database, as well as our other applications (both desktop and web).

But, given that when installed, the Iguana service runs under Local System, what difference does it really make if it runs under another Admin account? This separate account was setup to allow Windows authentication to the database. The other accounts used for our applications make use of SQL authentication.

Chuck

Ah well – seems like you have what you need then.

Just to let you know, I ran into this issue just recently and I’ve discovered that the ODBC driver does retain the user/password in the System DSN setting.

The username and password in the script still needs to be in the script but you can leave them blank.