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.