Thanks for the continued support, Charlie. Comments below: From: ad...@acfug.org [mailto:ad...@acfug.org] On Behalf Of Charlie Arehart Sent: Tuesday, February 22, 2011 10:57 AM To: discussion@acfug.org Subject: RE: [ACFUG Discuss] ColdFusion system account access to database
Well, I wouldn't have tried the "other" type first, but I see that in that technote I shared it does say you may need to do it because of some other bug. Did you at least try it as a normal SQL Server DSN? I did try that Error: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Could not establish a connection using integrated security: No LoginModules configured for JDBC_DRIVER_01 I see you say later, "I also tried the value of the JDBC URL in the Connection String value under advanced settings". Right, that is all I was meaning in my suggestion. So do you get the exact same error message then? That's important for us to know. If the JDBC URL is blank and Connection string is: jdbc:macromedia:sqlserver://[server_name]:1433;databaseName=[db_name]; SelectMethod=direct;sendStringParametersAsUnicode=false;MaxPooledStatements=0;AuthenticationMethod=Type2; Error: java.sql.SQLException: No suitable driver If both are populated the same: Error: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Could not establish a connection using integrated security: No LoginModules configured for JDBC_DRIVER_01 You then say "I can create the same data connection using the Windows SQL Server ODBC driver on the web server successfully, so DB permissions and configuration should not be a factor". Well, again let's be sure we understand what you're saying. In that ODBC datasource, did you also get to leave the username/pw blank? And even if so, don't you see that the login then would not be that with which you started CF. I don't know what it would be, except perhaps the System account. I haven't done much with ODBC drivers in years. But we want to be sure you're comparing apples to apples before making any conclusions. I logged in to the CF server as the domain account that the CF service runs under and defined an ODBC connection to the same DB server and database using Windows Authentication (no password needed). It pulled the list of DBs on that server and connected successfully. Again, the point of the trusted auth in CF is that if you leave the username/pw off, it then uses whatever account CF is started with. And I'll note that in googling the error you got, I found one page at least (http://forums.devshed.com/coldfusion-development-84/cfmx-7-02-and-ms-sql-2005t-469693.html) where one of the commenters said that the solution for him was adding a SQL Server login (using Management Studio) to define a user for whatever account CF was using, then grant it access to the particular database. So are you sure that's been done on the SQL Server? Yes, I connected to the DB server as that domain account and as a DBO and confirmed that there is a login for the account on the server and it has sufficient access to the correct DB. Just trying to help you think through things. I realize you may come back that all those are done and you still have the problem. Looks like that is where I am, but I REALLY appreciate your help. Sadly, nearly ever reference to this problem on the web offers no solution (other than "use sql authentication"). But the whole point of the hotfix (or the change in 8 and 9) was to allow specifically this feature, so I will look forward to us finding and resolving the problem for you. Perhaps it's just a minor issue of not having made the needed change in SQL Server. Looking forward to your answer. :-) My research found the same thing. /charlie From: ad...@acfug.org [mailto:ad...@acfug.org] On Behalf Of Rob Saxon Sent: Tuesday, February 22, 2011 4:03 AM To: discussion@acfug.org Subject: RE: [ACFUG Discuss] ColdFusion system account access to database Charlie, I found the same article earlier this afternoon. Your guidance added confirmation that this may lead to the solution. Since we are running MX7 and SQL Server 2008 Express, I updated the drivers. I restarted the server and configured an "Other" type data source where I specified: JDBC URL: jdbc:macromedia:sqlserver://[server_name]:1433;databaseName=[db_name]; SelectMethod=direct;sendStringParametersAsUnicode=false;MaxPooledStatements=0;AuthenticationMethod=Type2; Driver class: macromedia.jdbc.MacromediaDriver Driver Name: User Name: Password: Description: All blank Advanced settings: Defaults Throws this error: Connection verification failed for data source: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Could not establish a connection using integrated security: No LoginModules configured for JDBC_DRIVER_01 The root cause was that: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Could not establish a connection using integrated security: No LoginModules configured for JDBC_DRIVER_01 Other notes: * I also tried the value of the JDBC URL in the Connection String value under advanced settings * I have confirmed that TCP/IP is enabled on the * I can create the same data connection using the Windows SQL Server ODBC driver on the web server successfully, so DB permissions and configuration should not be a factor. Any other thoughts of things to try? Thank so much for your help! Rob From: ad...@acfug.org [mailto:ad...@acfug.org] On Behalf Of Charlie Arehart Sent: Monday, February 21, 2011 5:12 PM To: discussion@acfug.org Subject: RE: [ACFUG Discuss] ColdFusion system account access to database Rob, what you're trying to do is referred to as "trusted"or "type 2" or "windows" authentication. The good news is that if you're using CF 8 or 9, all you need to do is leave the username blank. CF will use that to infer that you want to use trusted authentication with, as you note, whatever account has been used to start CF. This is discussed in the docs, but it's pretty easy to miss, even for those who ever do note that there are docs on CF Administration. :-) CF9: http://help.adobe.com/en_US/ColdFusion/9.0/Admin/WSc3ff6d0ea77859461172e0811cbf364104-7fe5.html CF8: http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=datasources_ADV_MJS_11.html If you're on CF7, though, such trusted authentication was enabled only by updating the JDBC drivers, a discussed in this technote: http://kb2.adobe.com/cps/000/42dcb10a.html And further, you needed to modify the connection parameters. See the section there near the bottom on "SQL Server NT authentication users". Let us know if any of that works for you, Rob. /charlie arehart char...@carehart.org Providing on-demand troubleshooting services for CF and CFBuilder at http://www.carehart.org/consulting From: ad...@acfug.org [mailto:ad...@acfug.org] On Behalf Of Rob Saxon Sent: Monday, February 21, 2011 2:55 PM To: discussion@acfug.org Subject: RE: [ACFUG Discuss] ColdFusion system account access to database Thanks for the suggestion Steve. I also tried that and it failed. Interestingly enough, if I don't specify a user name or password, I get: "Could not establish a connection using integrated security: No LoginModules configured for JDBC_DRIVER_01" Thanks, Rob From: ad...@acfug.org [mailto:ad...@acfug.org] On Behalf Of Steve Ross Sent: Monday, February 21, 2011 2:44 PM To: discussion@acfug.org Subject: Re: [ACFUG Discuss] ColdFusion system account access to database You might have to use "user@domain" as your user name... On Mon, Feb 21, 2011 at 2:02 PM, Rob Saxon <saxon...@mercer.edu<mailto:saxon...@mercer.edu>> wrote: Our DBA is wanting use to use an Active Directory domain account to access an internal database. He has given our CF service account access to the DB, but I am having difficulty getting a data source to connect thru CF Admin. Is it possible to do this? So far, I'm using domain\system account name as the data source user name and corresponding password. Any suggestions? Thanks, Rob ------------------------------------------------------------- ------------------------------------------------------------- To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink<http://www.fusionlink.com> ------------------------------------------------------------- ------------------------------------------------------------- To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink<http://www.fusionlink.com> ------------------------------------------------------------- ------------------------------------------------------------- To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink<http://www.fusionlink.com> ------------------------------------------------------------- ------------------------------------------------------------- To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -------------------------------------------------------------
