SQL 2005 database authentication Problem

OK, so in 7.01, you need to upgrade the drivers to get Windows Authentication to work. The drivers that were built-in in 7 were the 3.4 version from Merant/DataDirect. They weren't updated (as a default in the install of CF) until 8,when they were updated to 3.6.

The 3.5 drivers added Windows authentication, and you can apply them to a CF 7/7.01/7.02 install. Check out my blog entry:

http://carehart.org/blog/client/index.cfm/2006/8/8/jdbc3.5_update

If you wonder what version of the drivers you may already be running (in case someone may already have updated the drivers), see this entry:

http://www.carehart.org/blog/client/index.cfm/2006/8/8/checking_jdbc_driver_version

Or more simply, just try removing the username/password values in the setup for the SQL Server DSN, to see if it works. If not (and assuming it's not complaining about the CF account username not being an authorized user of the DB), then it seems an update of the drivers is in order. See my first blog entry above for more on what the drivers offer, and why Adobe didn't just update the drivers in the 7 updaters and cumulative hot fixes, so that you must apply the update manually.

Bob, to your question about windows authentication, yes, CF supports it now. May depend on what version you're using, but try just leaving off the username and password entirely when creating a DSN pointing to the SQL Server db. Of course, this means that the account under which CF is running (defaults to System) must be defined in SQL Server to access the database in question. You may find that the System account can, by default. If you've changed the account under which CF runs (typically done in the Windows Services control panel), then you'd need to make sure that account is defined (in SQL Server) to access the database in question.

Also, your second comment refers to ODBC. Just to be clear, are you talking here about setting up an "ODBC socket" DSN in CF, or are you choosing "Microsoft SQL Server" as the database type in the the Administrator?

Error Message when testing the sql 2005 database data source in Coldfusion Administrator. The windows server ODBC connection to the database tests fine. However, when I attempt to created the CF data source the error below occurs.

Connection verification failed for data source: Heat
java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][SQLServer]Login failed for user 'na\svcColdfusion'.
The root cause was that: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][SQLServer]Login failed for user 'na\svcColdfusion'.