Re: Microsoft SQL Server JDBC Driver and Authentication
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:316284 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Microsoft SQL Server JDBC Driver and Authentication
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
RE: Microsoft SQL Server JDBC Driver and Authentication
You can use Windows authentication with JDBC. In the Advanced Settings Connection String box, enter in: AuthenticationMethod=Type2 Then, clear any username/password from that DSN. (Just to be sure.) Confirm that your CF service account can access SQL Server's databases. Then, Bob's your uncle! (This was confirmed with Windows Server 2003, SQL Server 2005, CF 8 Standard/Enterprise and the JDBC drivers that came with CF.) Mike -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED] Sent: Thursday, December 04, 2008 12:47 PM To: cf-talk Subject: Re: Microsoft SQL Server JDBC Driver and Authentication 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:316287 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Microsoft SQL Server JDBC Driver and Authentication
Ooops. I missed a critical part of your message. You said you did get Windows Authentication to work, but you want to specify individual credentials, per query. Sorry for the confusion. However, one thing you may try is create a domain group, then grant database access to that group. In that group, add your users. Then, try to connect using your method. If domain\user doesn't work, try the userPrincipalName ([EMAIL PROTECTED]). Also, if you are saying the DSN is still using the CF service's credentials, you should break it so that it never works, such as specifying an incorrect username/password, in the DSN settings. Then, your CF code should override, if it works. Mike -Original Message- From: Dawson, Michael [mailto:[EMAIL PROTECTED] Sent: Thursday, December 04, 2008 1:03 PM To: cf-talk Subject: RE: Microsoft SQL Server JDBC Driver and Authentication You can use Windows authentication with JDBC. In the Advanced Settings Connection String box, enter in: AuthenticationMethod=Type2 Then, clear any username/password from that DSN. (Just to be sure.) Confirm that your CF service account can access SQL Server's databases. Then, Bob's your uncle! (This was confirmed with Windows Server 2003, SQL Server 2005, CF 8 Standard/Enterprise and the JDBC drivers that came with CF.) Mike -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED] Sent: Thursday, December 04, 2008 12:47 PM To: cf-talk Subject: Re: Microsoft SQL Server JDBC Driver and Authentication 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? ~| 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:316288 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Microsoft SQL Server JDBC Driver and Authentication
If you have not created each user's SQL login, it won't work. However, I wouldn't want to have to manage all those SQL logins (and database permissions), in addition to their AD accounts. As I mentioned in another message, you may try adding a domain group as a SQL login/DB user. That said... I think most people use a single DSN user account to connect to their databases. That is how I operate. The good news is since you are on an Active Directory domain, you can leverage the strengths of AD to control access to your CF application. Create/organize domain groups that equate to the secured areas of your CF application. Then, use CFLDAP to retrieve the group membership and allow, or deny, the user when they attempt to access a secured area. ColdFusion and Active Directory can make a very-powerful alliance! Mike -Original Message- From: Carl Von Stetten [mailto:[EMAIL PROTECTED] Sent: Thursday, December 04, 2008 12:54 PM To: cf-talk Subject: Re: Microsoft SQL Server JDBC Driver and Authentication 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 ~| 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:316289 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Microsoft SQL Server JDBC Driver and Authentication
Ooops. I missed a critical part of your message. You said you did get Windows Authentication to work, but you want to specify individual credentials, per query. Sorry for the confusion. However, one thing you may try is create a domain group, then grant database access to that group. In that group, add your users. Then, try to connect using your method. If domain\user doesn't work, try the userPrincipalName ([EMAIL PROTECTED]). Also, if you are saying the DSN is still using the CF service's credentials, you should break it so that it never works, such as specifying an incorrect username/password, in the DSN settings. Then, your CF code should override, if it works. Mike You can use Windows authentication with JDBC. In the Advanced Settings Connection String box, enter in: AuthenticationMethod=Type2 Then, clear any username/password from that DSN. (Just to be sure.) Confirm that your CF service account can access SQL Server's databases. Then, Bob's your uncle! (This was confirmed with Windows Server 2003, SQL Server 2005, CF 8 Standard/Enterprise and the JDBC drivers that came with CF.) Mike 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? Michael, The trick was passing the username as [EMAIL PROTECTED] rather than domain\user. I think, however, that your suggestion in the other post to use CF/LDAP within my application to enforce security will be a better option for me. Thanks for your help, Carl ~| 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:316291 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Microsoft SQL Server JDBC Driver and Authentication
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? Yes, because of the database connection pooling issues I mentioned earlier. 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:316299 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
