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
