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.
ColdFusion And SQL Server 2005
44 responses to “ColdFusion And SQL Server 2005”
-
Hi Ben,
I have a blog entry about CF & SQL Server 2005 Express.
http://www.demirkapi.net/avblog/index.cfm?mode=viewEntry&id=CF028569-CCD2-2132-381C9287F81E1E96
I think it would be helpful. -
Great, thanks for sharing!
— Ben -
Thanks Ben!
Noted at http://www.actcfug.com/index.cfm?fuseaction=TipView&TipID=118
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 -
http://www.robisen.com/index.cfm?mode=entry&entry=BE1AB594-55DC-F2B1-F392552E0A41542A
I think this link would be helpful to the users as well -
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!
-
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 -
Just released from Microsoft, SQL Server 2005 JDBC Driver:
http://www.microsoft.com/downloads/details.aspx?familyid=e22bc83b-32ff-4474-a44a-22b6ae2c4e17&displaylang=en -
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. -
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? -
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. -
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. -
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 TCPIP 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. -
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. -
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. -
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?
-
Does anybody know if it’s possible to connect ColdFusion to SQL Server using a Windows Authentication?
-
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 -
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. -
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 -
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. -
David, honestly, CF5 is so long ago that I don’t even remember if we provided MySQL drivers then, sorry. 🙁
— Ben -
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. -
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 -
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.
-
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?
-
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?
-
Charles, yep, should be fine.
— Ben -
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… -
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?
-
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>database upgrades</a> as the new SQL upgrade will allow us do these upgrades without service interruption to our clients.
-
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. -
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. -
DD, you need to look at SQL Server logs or traces to see what is happening.
— Ben -
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 -
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. -
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. -
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. -
Thanks, Ben – that got CF9 connected to SQL Server 2008 for me.
-
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. -
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. -
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? -
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 -
An additional comment. After changing the authentication properties you need to add a local user that can be used.
Leave a Reply