Blog

10Jan
2006
ColdFusion And SQL Server 2005

Several users have wanted to know if ColdFusion supports SQL Server 2005. And the answer appears to be yes. I am using ColdFusion MX 7.01 with the default SQL Server driver, and am connected to SQL Server 2005, and so far so good. Microsoft does have a new SQL Server 2005 JDBC driver in beta, but thus far I have not installed it, and not needed to. All testing thus far has worked flawlessly, using basic statements as well as browsing tables and schemas via RDS.

The only two issues I ran into were minor configuration and security setting defaults that needed to be tweaked.

First of all, by default SQL Server 2005 has TCP/IP connections disabled. To enable TCP/IP support, use the SQL Server Configuration Manager tool, select SQL Server 2005 Network Configuration, select Protocols, double-click on TCP/IP, and turn on Enabled.

The next gotcha was the user account. I created a SQL Server user account for ColdFusion, but by default SQL Server 2005 only uses Windows Authentication (which is generally not how ColdFusion would authenticate). To enable support for SQL Server Authentication, right-click on the server in Microsoft SQL Server Management Studio, select Properties, Security, and set Server Authentication to SQL Server and Windows Authentication.

And that seems to do the trick.

Comments (44)



  • O?uz Demirkap?

    Hi Ben,

    I have a blog entry about CF & SQL Server 2005 Express.

    http://www.demirkapi.net/avblog/index.cfm?mode=vie...

    I think it would be helpful.

  • Ben Forta

    Great, thanks for sharing!

    --- Ben

    #2Posted by Ben Forta | Jan 10, 2006, 08:11 PM
  • Peter Tilbrook

    Thanks Ben!

    Noted at http://www.actcfug.com/index.cfm?fuseaction=TipVie...

    Am in the middle of two CF projects so sticking to 2000 for now (a well known quantity after all these years, eh?)

    Thanks for the advice however!

    PT

  • Mujeebur Rahmansaher

    http://www.robisen.com/index.cfm?mode=entry&en...

    I think this link would be helpful to the users as well

  • Garrett Wiedmeier

    We have tested our Intranet and public website since an early release of SQL 2005 using CF MX & MX 7. Other than the mentioned user gotchas, which were small once I read the book, everything seemed to work great -- and was fast. Didn't notice any problems with our main CFC or stored procedures. They're not hugely complex, but I think our full production roll-out should go very smoothly. Anyways, just our observations! Cheers!

  • Craig

    Has anyone tested the sql 2005 xml data type with coldfusion? I would like to know if CF can handle it, and if so if it is return to CF as an XML object. (I can dream can't I?)

    Thanks

    #6Posted by Craig | Jan 13, 2006, 04:40 PM
  • 6dust

    Just released from Microsoft, SQL Server 2005 JDBC Driver:

    http://www.microsoft.com/downloads/details.aspx?fa...

    #7Posted by 6dust | Jan 19, 2006, 04:46 PM
  • Michael R

    We were in production with sql 2000 and CFMX7 for no problems for the past year +.

    Ported to SQL 2005 and Cold Fusion (both load balanced) servers would have every 10-15 min, without any changes to the database, system settings etc. Just SQL 2005.

    After a painful day of troubleshooting while in production, it turns out one of our tables needed to have indexes added.
    After that the problems went away. Again, this was never an issue in 2000.

    It appears there is either a bug in 2005 or some alternate way it processes queries.

    Just want to make sure no body gets stung with this same issue.

    #8Posted by Michael R | Jan 24, 2006, 08:22 AM
  • Andrew Mercer

    If you were to download SQL Server 2005 JDBC Driver, what would you do with it to enable CFMX to use it?

    Drop the jar file in the cfmx lib directory?

    #9Posted by Andrew Mercer | Mar 13, 2006, 10:46 AM
  • Ed Welch

    I have installed the SQL Express..and CF 7.0.1 and cannot get the DSN to work. This is what I get:
    Connection verification failed for data source: Test
    java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][SQLServer]Login failed for user 'ed'.
    The root cause was that: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][SQLServer]Login failed for user 'ed'.

    Both the SQL server express and CF are installed on the same machine and I have created a user cfserver with pw cfserver to run the SQL server but I cannot connect to it and setup the dsn. Any ideas. I have set the TCP/IP and the authentication to windows/server.

    #10Posted by Ed Welch | Jun 26, 2006, 12:47 PM
  • robi

    the post is now here

    http://www.robisen.com/index.cfm/2006/6/2/ColdFusi...

    #11Posted by robi | Aug 18, 2006, 04:59 PM
  • Justin

    Have you tried MSSQL 2005 64-bit with CFMX7?

    I'm guessing JDBC can handle both 32 and 64 bit platforms, but I haven't found out for sure yet, this should be invisible to the client.

    #12Posted by Justin | Nov 8, 2006, 01:58 PM
  • ivan

    hi guys. I am trying to set up both Coldfusion mx 7 and SQL server 2005 to work, but still i am having troubles. I've enabled TCP\IP support in SQL server . but this is what i get when i am trying to add a new database to Coldfusion mx 7:
    "Connection verification failed for data source: users
    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" plz help.

    #13Posted by ivan | Jan 1, 2007, 02:26 PM
  • Len Nagy

    I'm having the same problem as Ed Welch. Ed have you found a solution for this? I've been working on this for two days and a couple hours with MS.

    Seems just the JRun is having a problem using CF 7.0.1. Wierd error to see Login failed for user.

    I'm downloading updater2 now to update to 7.0.2. But would still like to know if you've resolved this and how. I can connect to the SQL 2k5s running 32 bit OS's but not the 64 bit OS.

    #14Posted by Len Nagy | Jan 5, 2007, 11:31 AM
  • Len Nagy

    Unbelieveable...the problem was caused by a userid that was too long. The SQL userid had 17 characters. We shortened it and bang, worked like a charm.

    So ODBC, Mgmt Studio, even a java class could authenticate with the long userid, but CFMX 7.0.2 won't. JRE 1.4.2

    We even tried installing the newest Microsoft JDBC driver ( type 4 ) for SQL Server 2k5, no joy.

    Well, maybe this will save someone else two days of frustrating diagnosis, lol.

    #15Posted by Len Nagy | Jan 5, 2007, 03:47 PM
  • Tracey

    I was wondering if someone could help me. I am creating a website (for coursework) and I am using an SQL database and Coldfusion. I want to create an SQL query that will read in a users selection from a list(an <SELECT> <OPTION> format), match it to a database, then spit out the results into a marquee. I almost got it working then I fuffed it up. Can anyone help? Or reccommend any books to look at?

    #16Posted by Tracey | Mar 6, 2007, 12:46 PM
  • David Ball

    Does anybody know if it's possible to connect ColdFusion to SQL Server using a Windows Authentication?

  • Ben Forta

    David, CF is not running as a user, so there is no user to authenticate. I've not tried it, but, if you have CF running as a user account then in theory it should be able to authenticate as that user.

    --- Ben

    #18Posted by Ben Forta | May 2, 2007, 06:36 PM
  • David

    I am having trouble running ColdFusion Server 5.0 with SQL Server 2005.
    Any suggestion on how to get the data sources to work?
    The datasource verifies in CF Admin.

    #19Posted by David | Jul 5, 2007, 04:59 PM
  • Ben Forta

    David, CF5 came out 5-6 years before SQL Server 2005. We've never tested them together, and don't plan to. You really need to upgrade your CF.

    --- Ben

    #20Posted by Ben Forta | Jul 5, 2007, 05:03 PM
  • David

    Thanks for the feedback Ben.
    Another subject: I cannot seem to find any refernece to a MySQL connection in the CFAdmin
    ODBC configuration. What am I missing?
    Thanks.

    #21Posted by David | Jul 5, 2007, 07:22 PM
  • Ben Forta

    David, honestly, CF5 is so long ago that I don't even remember if we provided MySQL drivers then, sorry. :-(

    --- Ben

    #22Posted by Ben Forta | Jul 5, 2007, 07:27 PM
  • Dave Melton

    We've been searching the web over and over, again for this answer, but have found conflicting stories. We're running CF 7.0.2 on a windows 32 bit environment with SQL server 2000. It's worked fine for years. Well, we're about to upgrade that server to a Windows 2003-64 with a AMD64 processors. We'll also be running SQL 2005 64 bit. From what we've read sofar there shouldn't be any problems. I need to know if anyone has set CF up this way? If so, what were the problems and how did you fix them?

    Or is it simply as easy as I'm reading on this page? Nobody mentioned what version of SQL 2005 they were running.

    #23Posted by Dave Melton | Jul 6, 2007, 09:12 PM
  • Ben Forta

    Dave, yep, the comments here work with 32bit or 64bit SQL Server. Of course, CF8 (in beta now) officially supports SQL Serer 2005 and 7 does not.

    --- Ben

    #24Posted by Ben Forta | Jul 8, 2007, 09:46 AM
  • joe

    Realizing it may not be a wise decision for best performance to install CFMX 7 and SQL Server 2005 on the same machine, nevertheless, could it still work ok or is there some other reason why we should NOT attempt to get this to work?

    #25Posted by joe | Jul 18, 2007, 08:28 AM
  • Dave M

    Just an update to my prior comment. Sofar, all has installed and worked together just fine. Also, for those migrating from an old win2k server to a newer win2003 one, I'd highly recommend using microsoft's IIS migration tool.

    #26Posted by Dave M | Jul 18, 2007, 11:02 AM
  • Charles

    We've got ColdFusion 7.0.2 on a 32-bit Windows clustered server, and are planning to upgrade to SQL Server 2005 on a separate 64-bit Windows server. Should that work okay, or would we be advised to wait until we upgrade to CF 8?

    #27Posted by Charles | Jul 20, 2007, 01:00 PM
  • Ben Forta

    Charles, yep, should be fine.

    --- Ben

    #28Posted by Ben Forta | Jul 20, 2007, 01:16 PM
  • Jason

    OK I've been beating my head against the wall for a week trying to figure out why could fusion wont recoginze my datasource.

    I'm running vista, sql 2005 express and cf 8 devleopers edition.
    Heres the error:
    * Connection verification failed for data source: TestDB
    An exception occurred when executing method verifydatasource.
    The cause of this exception was that: coldfusion.sql.Executive$ConnectionVerificationFailedException: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][Macromedia][SQLServer JDBC Driver]The requested instance is either invalid or not running..
    SQLCritUpdPkg_ENU
    What does this mean?...

    Here's what I tried so far to resolve this:
    1. enabled tcp/ip in sql config manager, verfied that it's using port 1433 in IPALL
    2. telnet 127.0.0.1 1433 - that works ok
    3. dowloaded SQLCritUpdPkg_ENU.exe and installed it - I read on a fourm somewhere that this disabled some ports due to a worm.. but i dont think that was the problem anyway since i ping'd 1433 and it was ok
    4. And yes I've logged in directly to SQL server using the u/p..
    and I've changed the security settings in SQL to use both sql server and windows authenticaion

    Any help from anyone would be awesome..

    PS: I've also gotten this same error when I had CFMX 7 installed...

    #29Posted by Jason | Jul 28, 2007, 06:46 PM
  • Mel

    We are planning SQL 2005 x64, CFMX 7 on a 2003 Web Edition server. I aware that Web edition will not load SQL 2005 on second server. Also that is is restricted to web apps only, to the point of not even sharing a printer. Any other problems?

    #30Posted by Mel | Aug 23, 2007, 03:59 PM
  • Denise

    I too had just a few issues with configuration but aside from that Cold Fusion and SQL server 2005 seem like they where made for each other. I’ve been able to increase the amount of available resources for our business applications and thus have been delivering better service to both our clients and employees since we upgrade to 2005. We are now looking at performing several <a href=http://www.stratavia.com/datapalette.php>databa... upgrades</a> as the new SQL upgrade will allow us do these upgrades without service interruption to our clients.

    #31Posted by Denise | Dec 21, 2007, 03:16 AM
  • Andrew Barber

    We have been having problems with our DB connections as well. We have just upgraded our DB server and Web server to Windows 2003 by wiping them and and doing a fresh install. When setting up datasources, we got the "login failed for user 'xxxx'" message. We are using SQL Server 2000 and have tried CF 6.1 and 7 with the same result.

    Having read some of these posts, I tried configuring our DB server with a shorter password and eventually a blank password temporarily and this worked. After some more investigation, I discovered that using the pound sign was causing all our problems. It appears that this character gets translated along the line and the correct password fails to be sent to the SQL Server.

    #32Posted by Andrew Barber | May 19, 2008, 07:34 AM
  • DD

    Ben,

    I am using Coldfusion 7 with Microsoft SQL Server 2005 express on my laptop. Everything seems to be working fine except, when I try to do an update or insert, nothing happens. I do not get an error, but at the same time nothing gets inserted or updated in the database. When I place that same code (SQL and Coldfusion) on a production server everything works fine. I recently upgraded my ram on the laptop to 2 Gig, thinking it may be a memory problem, but nothing changed.

    Please help, I'm at my wits end here.

    #33Posted by DD | Jun 24, 2008, 10:18 AM
  • Ben Forta

    DD, you need to look at SQL Server logs or traces to see what is happening.

    --- Ben

    #34Posted by Ben Forta | Jun 24, 2008, 08:41 PM
  • Steve

    Hey all..

    We're on CFMX 7.02, windows 2003 web edition, apache 2.0.58. We've had endless problems with cpu saturation in Sql Server 2005 -- even after completely upgrading hardware and grabbing latest OS and SQL patches, the problem has returned. We've been fighting this from different angles, adjusting cfqueries and making sure DB config is optimal for our environment.

    I came across the kb article here:
    http://www.adobe.com/go/42dcb10a

    And thinking about applying this latest version of the datadirect drivers for cfmx7. What caught my eye was the statement:
    61161 - Insert trigger causes CPU to spike to 100% using the 3.4/3.5 drivers.

    Comments? What is the official answer on CFMX7 and SQL Server 2005.. is 2005 supported?
    We're already down that road so too late for us, curious if you guys agree with trying out these latest JDBC drivers first before trying Coldfusion 8. Thx. -Steve

    #35Posted by Steve | Sep 2, 2008, 08:00 PM
  • Timothy Lutz

    Here are some important points to add:

    1. If you are using a secondary MSSQL instance, the default listening port (1433) is not enabled. You must go to the SQL configuration utility to configure a static port. This port must be different from the listening port on the primary SQL instance. If the ports are the same, the second instance will not start.

    2. Chen configuring the CF server field, if you are using a secondary SQL instance, you will need to use the format <server>\<instance name>. If you are missing a hosts file entry for the SQL server on the unix side, you will have to use the IP.

    #36Posted by Timothy Lutz | Oct 24, 2008, 03:35 PM
  • Mike Robinson

    I found that Coldfusion (9) could connect with SQL Server Express (2005), using Windows authentication, if:
    (1) You define a "static port" in SQL Server Configuration. (A "dynamic port," it seems, will not be found by ColdFusion but DON'T quote me on that... I just used '127.0.0.1' as the IP. (Normally, SQL Express uses "dynamic ports" and relies on the locator-service to help apps find it.)
    (2) As Administrator, give the NT/SYSTEM account permission to "Connect SQL." This is the default account used by the ColdFusion service.

    #37Posted by Mike Robinson | Oct 20, 2009, 05:33 PM
  • Tracy Loi

    Hi Ben,

    We're getting the same error message with the ODBC connection from coldfudion 8.1 to SQL 2000
    SP4. Our SQL server's security setting is mix mode.

    How can I fix this problem?

    Thanks.

    #38Posted by Tracy Loi | May 20, 2010, 10:15 AM
  • Jane

    Thanks, Ben - that got CF9 connected to SQL Server 2008 for me.

    #39Posted by Jane | Jun 8, 2010, 05:21 AM
  • Robert

    I am having a similar issue ... brand new install in development prior to production.
    MacBook Pro in Boot Camp mode ... Windows 7 (64 bit) ... CF9 ... SQL Server 2005.
    Both CF9 and SQL installed fine independently.
    TCPIP and mixed authentication in SQL enabled ... but in CF admin panel cannot connect to SQL ... get an
    java.sql.SQLNonTransientConnectionException.
    SO what is it I am doing wrong? :)
    Many thanks in advance.

    #40Posted by Robert | Jul 26, 2010, 05:35 PM
  • Robert

    I am having a similar issue ... brand new install in development prior to production.
    MacBook Pro in Boot Camp mode ... Windows 7 (64 bit) ... CF9 ... SQL Server 2005.
    Both CF9 and SQL installed fine independently.
    TCPIP and mixed authentication in SQL enabled ... but in CF admin panel cannot connect to SQL ... get an
    java.sql.SQLNonTransientConnectionException.
    SO what is it I am doing wrong? :)
    Many thanks in advance.

    #41Posted by Robert | Jul 26, 2010, 05:36 PM
  • Mo

    I have SQl Server 2005 Express working with ColdFusion on my local laptop, thanks to you. Now I need to send the database to the remote hosting server. In the past I have used Access, and could just FTP the db to the server. Apparently I can not do this with MSSQL, I downloaded
    the tool kit but it did not show up in 90/DTS. I have no export option. I have no DTS file. Should I be using MySql instead?

    #42Posted by Mo | Dec 5, 2010, 02:43 AM
  • Ben Forta

    Mo, SQL Server Express has backup/restore options built right in. So you should be able to use that to create a backup set, and then restore it on the destination machine.

    --- Ben

    #43Posted by Ben Forta | Dec 5, 2010, 10:59 AM
  • Andrew

    An additional comment. After changing the authentication properties you need to add a local user that can be used.

    #44Posted by Andrew | Dec 29, 2010, 04:06 PM