I tested a little bit further by going into SQL Server Management Studio and
removing permissions to the database for the ColdFusion service login account.
When I attempt to run the query, it does attempt to use the username I'm
passing, but then gives me an error:
"Cannot open database "GDI" requested by the login. The login failed."
The only reason I was even trying this was to ensure secure access to a
particular database that only certain users have permissions to write to. I
guess I'll have to manage the permissions through my CF application, rather
than at the SQL Server level. Is this how most developers deal with data
security?
Carl
> > I'm using Coldfusion MX7.0.2 on Windows 2003 SP1. I've installed
> and set up the Microsoft SQL Server 2005
> > JDBC driver, and have successfully set up a datasource using Windows
> Authentication (I have no user/password
> > assigned in the datasource configuration; it's passing the
> ColdFusion service login which is an Active Directory
> > account).
> >
> > I'm trying to run a cfquery passing a specific user's credentials to
> the query, like this (I've changed username and
> > password to protect the innocent ;>) ):
> >
> > <cfquery datasource="MyDSN" name="testQuery"
> username="MyDomain\UserName" password="MyPassword">
> >
> > I have the SQL Server Profiler open running a trace on the target
> database. When I run the CFM page and execute
> > the query, the trace shows that ColdFusion is still using the
> ColdFusion service login to run the query, instead of the
> > user I passed to CFQUERY.
> >
> > Is there something else I need to do to make this work?
>
> I don't think CF will pass the username and password attributes to
> the
> database if you're using Windows authentication via JDBC. I don't
> think you can specify a user account other than the service account
> when using Windows authentication via JDBC. I think that the only way
> you can do this is to set up an ODBC datasource that uses Windows
> authentication, then use CF to connect to that using the SequeLink
> JDBC-ODBC bridg ("ODBC Socket" in the CF Administrator).
>
> All that said, even if you can pass user credentials to the database,
> that may cause a serious degradation in performance, as database
> connections can no longer be shared across requests unless those
> requests come from the same user.
>
> Dave Watts, CTO, Fig Leaf Software
> http://www.figleaf.com/
>
> Fig Leaf Software provides the highest caliber vendor-authorized
> instruction at our training centers in Washington DC, Atlanta,
> Chicago, Baltimore, Northern Virginia, or on-site at your location.
> Visit http://training.figleaf.com/ for more
information!
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316286
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
