First, keep in mind that this is going to be JDBC, not ODBC (2 different
animals).
Second, you should be able to set up a datasource without any credentials (no
username and password). In which case your stored proc code will require a
username password to work. If you are going to set up separate DSNs (which you
might want to do to consolidate all the settings into one file) I would simply
add them with the appropriate credentials in the CF admin and the use the
correct datasource in your code. This would keep you from storing database
level permissions in the session (which seems like maybe a bad idea :).
-Mark
Mark A. Kruger, CFG, MCSE
(402) 408-3733 ext 105
www.cfwebtools.com
www.coldfusionmuse.com
www.necfug.com
-Original Message-
From: [EMAIL PROTECTED] [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 26, 2008 10:34 AM
To: CF-Talk
Subject: ODBC Connection to Microsoft SQL DB Stored Proc
My Microsoft SQL database contains multiple stored procedures with access to
these stored procedures secured by SQL Authenticated Login Accounts (i.e.
Manager, User, Auditor).
I am setting two session variables, session.UserName and session.Password, to
the SQL Authenticated Login account for the level the person is authenticated
(i.e.
Manager) . I then pass this information through the ââ¬Åcfstoredprocââ¬Â
tag by username=Session.UserName and password=Session.Password.
My question is when setting up an ODBC connection to this database, do I have
to setup a separate odbc connection for each one (User, Manager, Auditor) and
set the username and password in the ODBC connection to each of their user name
and password (In my case causing me to have 3 ODBC connections)? Because it
seems as though my current ODBC connection via the CF Administrator overwrites
any of the cfstoredproc settings. In other words, if I were to create my DSN in
ColdFusion with the sa username and password, then it doesnââ¬â¢t matter
what username and password the cfstoredproc passes in ââ¬â it will run
because it is using SA ââ¬â the DSN account, to run it.
Do you know of any documents available that explains how the ODBC connections
and SQL Security tie together? Thank you in advance for all of your help.
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:311585
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4