ColdFusion + SQL Server Express 2005
I finally got around to installing SQL Server Express 2005 this weekend and thus far I'm quite impressed. I did run into a couple of things I thought I'd pass along if some of you CFers out there haven't taken the plunge.
First and foremost, be aware that you have to download the management tool separately. The equivalent of the old Enterprise Manager (at least as near as I can tell) is SQL Server Management Studio Express CTP, and it's really pretty slick from what I've done with it thus far.
The other very nice thing I found is that if you just backup your SQL 2000 databases they can be restored into SQL 2005 with no problems whatsoever. This makes the migration pretty darn easy.
As for getting CF to talk to SQL 2005, this didn't just work for me right out of the box but after I figured out what the issues were it's not a big deal. Hopefully what I pass along here will speed things along for some of the rest of you.
By default SQL 2005 does not have TCP/IP connectivity enabled after the installation. To enable it go to Start -> All Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Configuration Manager. Under SQL Server 2005 Network Configuration, click on Protocols for SQLEXPRESS, click on TCP/IP, and enable it. You'll then have to restart the instance, which you can do from the Configuration Manager under the SQL Server 2005 Services area.
Next, I'm running XP Pro SP2, and I do have the firewall enabled, so I had to add SQL Server as an exception to the firewall rules. To do this go to Control Panel -> Windows Firewall and click on the Exceptions tab. Click on Add Program, then Browse, and locate the SQL Server executable, which on my machine was under C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe.
After I did this it still didn't work, so one more thing to note is that in my case at least, SQL Express doesn't run on port 1433. On my machine it runs on port 1771. I didn't do anything special during the installation (meaning I just accepted all the defaults) so I'm not sure if 1771 is the default port for SQL 2005 now or if for some reason on my setup it just chose this port. The port is obviously crucial when you're setting up your datasources for CF. To check your port after installation, go to the SQL Server Configuration Manager (the same one you went to above to enable TCP/IP connectivity), go to Network Configuration/Protocols again, right click on TCP/IP, choose Properties, then click on the IP Addresses tab. Under IPAll in the TCP Dynamic Ports section, in my install this was automatically set to port 1771.
With all this set up correctly you should have no problems connecting to SQL Server Express 2005 from ColdFusion, because the built-in SQL Server drivers seem to work fine. When I was getting this all figured out I also downloaded and tried the jTDS JDBC Driver for SQL Server, which is also compatible with SQL Server 2005. From what I understand this is an extremely high performance driver so it's not a bad choice, but for development purposes or if you've had good luck with the built-in driver that ships with CF, you can use that driver and it works just fine.
I'll post more as I start to use SQL Server Express 2005 a bit more but it's VERY nice to see MS offering a free database that from what I've read has fewer limitations than MSDE and has many of the new features of SQL Server 2005.
First and foremost, be aware that you have to download the management tool separately. The equivalent of the old Enterprise Manager (at least as near as I can tell) is SQL Server Management Studio Express CTP, and it's really pretty slick from what I've done with it thus far.
The other very nice thing I found is that if you just backup your SQL 2000 databases they can be restored into SQL 2005 with no problems whatsoever. This makes the migration pretty darn easy.
As for getting CF to talk to SQL 2005, this didn't just work for me right out of the box but after I figured out what the issues were it's not a big deal. Hopefully what I pass along here will speed things along for some of the rest of you.
By default SQL 2005 does not have TCP/IP connectivity enabled after the installation. To enable it go to Start -> All Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Configuration Manager. Under SQL Server 2005 Network Configuration, click on Protocols for SQLEXPRESS, click on TCP/IP, and enable it. You'll then have to restart the instance, which you can do from the Configuration Manager under the SQL Server 2005 Services area.
Next, I'm running XP Pro SP2, and I do have the firewall enabled, so I had to add SQL Server as an exception to the firewall rules. To do this go to Control Panel -> Windows Firewall and click on the Exceptions tab. Click on Add Program, then Browse, and locate the SQL Server executable, which on my machine was under C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe.
After I did this it still didn't work, so one more thing to note is that in my case at least, SQL Express doesn't run on port 1433. On my machine it runs on port 1771. I didn't do anything special during the installation (meaning I just accepted all the defaults) so I'm not sure if 1771 is the default port for SQL 2005 now or if for some reason on my setup it just chose this port. The port is obviously crucial when you're setting up your datasources for CF. To check your port after installation, go to the SQL Server Configuration Manager (the same one you went to above to enable TCP/IP connectivity), go to Network Configuration/Protocols again, right click on TCP/IP, choose Properties, then click on the IP Addresses tab. Under IPAll in the TCP Dynamic Ports section, in my install this was automatically set to port 1771.
With all this set up correctly you should have no problems connecting to SQL Server Express 2005 from ColdFusion, because the built-in SQL Server drivers seem to work fine. When I was getting this all figured out I also downloaded and tried the jTDS JDBC Driver for SQL Server, which is also compatible with SQL Server 2005. From what I understand this is an extremely high performance driver so it's not a bad choice, but for development purposes or if you've had good luck with the built-in driver that ships with CF, you can use that driver and it works just fine.
I'll post more as I start to use SQL Server Express 2005 a bit more but it's VERY nice to see MS offering a free database that from what I've read has fewer limitations than MSDE and has many of the new features of SQL Server 2005.
Thanks.
:)
Another way to check is try to telnet to the SQL Server port. Open up a DOS prompt and type "telnet localhost 1433" (without the quotes) and hit enter. If you get a connection failed error then SQL Server either isn't running on port 1433 (which is the default) or you don't have TCP/IP connectivity enabled. If you get a blank screen with no error message then you've connected (hit Ctrl-] to disconnect), which means the connectivity over TCP/IP is OK. If this is the case and you still can't connect from CF then something's amiss with the settings in the CF administrator.
The port and firewall info helped me out. I have it all working on my laptop with CFMX 7 Dev Edition.
-AD
I've tried everything in this and every other blog entry I could find... but I still can't get my datasource to connect. I'm as up to date as I can get. CFMX 7.1, SQL Server 2005 Express, WinXP SP2, .Net Framework 2, and all my patches from MS are installed.
Is there something else? Matt could you revisit this entry?
Thanks to anyone who can help.
I have just installed SQL Server 2005 Express on the same machine as ColdFusion 7 and am working on getting them to talk; so far, nothing. I continue to get a connection refused error in the CF administrator.
I have both installed on the same machine. SQL Server is in mixed authentication with TCP enabled. My IP(ALL) dynamic TCP/IP is set to 4171. 4171 is opened on my firewall, as is 1433 (just in case). I can telnet into 4171, but not 1433. I have tried configuring the CF datasource with both 4171 and 1433, with and without a user name and password, but still have had no success.
Any ideas?
Nice one mate. Helpful even years later...
I have tried everything mentioned in the article, still nothing. Anyone else still has a problem connecting??
I get this error in CF:
Connection verification failed for data source: MAXHI
java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][Macromedia][SQLServer JDBC Driver]The requested instance is either invalid or not running.
The root cause was that: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][Macromedia][SQLServer JDBC Driver]The requested instance is either invalid or not running.
@Andre:
I would think it has to do with the port number. Mine was totally different than Matt's..
Hey,
Thanks for the post. Well I didn't search for ColdFusion it self, but for SQL Server Express 2005.
Well I just was playing with the ports in the TCP/IP properties. Since it is dynamically enabled the port will change every time you restart the SQL server. To avoid that, blank the every TCP dynamic Ports and TCP port (Don't enter enter 0) then simply go to TCP port in IPALL category enter the TCP port you prefer. I did enter TCP port 1433.
I am not sure that I had IPALL configuration since I had more than 1 IP defined to my PC.
Thanks.
M. Mayoorathen
Matt, nice one. I've been using SQL Server for too many year and was trying everything to connect on port 1433. I read your article and it turns out my SQL Server 2005 Express is on port 2720. Phew!
I've still not been able to get CFADMIN to recognize a SQL connection. "The requested instance is either invalid or not running."
The SQL User is valid, I can log in with them directly with no problem.
I've telnet'd to localhost 1433 and it seems to be running.
Now when I installed sql server 2005 and went into the config manager under IPALL there was a 5 digit # under IPALL TCP Dynamic Port.. but I cannot remember what it was. I've since changed it to 1433 for the TCPIP port, and blanked out all the dynamic ports.
I'm at my wits end.. I cannot freakin figure this out. I'm on Vista too. Should I just go back to XP? Don't know what else to try at this point.
Any advice would be awesome! Please help!
Well, are you having SQL Server 2005? OR SQL Server 2005 Express?
Because in SQL 2005 you don't have to change port settings and in only SQL Server Express 2005 you have to change it, because acts as dynamic port and you can disable it.
Other thing is if you are using Windows Authentication in the SQL Server it self, you will have problems to setup ODBC connection in the CFADMIN itself.
So in the Object Explorer(SQL Server Management Studio Express) choose the server and right click to get the properties and then choose Security option in the list. After that under Server Authentication choose Windows and SQL Server Authentication and press ok. After that you have to restart the SQL server by right clicking the server in the Object Explorer in SQL Server interface itself choose restart.
You can use a SQL Server user such as the sa user by changing the password or adding a new user.
Hope it works!..
Thanks.
M. Mayoorathen
Mayo - I'm using SQL Server 2005 Express. I tried changing this under security as well, but still no luck getting coldfusion to verify.
Still getting "Connection verification failed for data source: main
java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][Macromedia][SQLServer JDBC Driver]The requested instance is either invalid or not running.
The root cause was that: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][Macromedia][SQLServer JDBC Driver]The requested instance is either invalid or not running."
It must have SOMETHING to do w/ Vista, because I got this working on XP with no problem.
God I hate Vista. I wish I had my XP Pro back!
Made my day! Thanks a lot ;-)
It’s very good article. Great site with very good look and perfect information. I like it too.
http://www.hk-mini-storage.com/mini.html
Thanks a lot!
Enabling the TCP/IP on my SQL Server did the trick.
Can somebody tell me that if connection from ColdFusion to Sql server can be pooled? If the account is set up in the CFADMIN, how the coldfusion server distinguish the connections between user A and user B? I really have no clue.
Thanks
CF handles the pooling for you. You don't need to worry about it.
Glad I found your page, it saved my bacon. Thanks!
For those of you using these tips but are not getting it to work and are on Vista. I feel your pain. I followed all the instructions and I still could not get it to work. Finally I decided to delete the original data source that I was working with, inside the CF admin, and create a new data source and presto!! It worked. I hope this helps you Vista folks out there. These instructions worked for me, remember to start fresh once you're done with Matt's instructions. I also wanted to say Matt, you rock! Thanks for sharing your tips.
Chill
I dont understand...
Interesting thing!
This is my first time comment at your blog.
Good recommended website.
Our site provides Rolex replica, replica Rolex Watches,
My girlfriend received the Ugg Bailey Button I bought her for her birthday, she adores ugg boots, So I’ll be back before Christmas buy more Black Ugg from you.
UGG Locarno Boots don't come cheaper either the original ugg 5389 boots Australia brand which hit Ireland circa 2006, costs 220 and upwards, with new styles like the Ugg Sundance gracing the shops this year.
thanks for sharing
Excellent reproduction replica rolex sells at resonable price.The rolex replica paypal with high quality and exclusive design.Choose one amazing of replica watch paypal to highlight your life style.
Above these goods really good, so beautiful jewelry!replica watches Really to be commended! Unfortunately, I have no money, He He. Otherwise I really bought"Magnificent", "Gum gee yuk yip", "True gold fears no fiery," "golden palace in books," "very strong"replica watchThere is a noble, time-delayed in ancient times left to shine; There is a wealth, status and power load across time and space.fake rolex watches You have a good typing speed soon yo!
Thank you.Hot Shoes Cheap Jordan shoes michael jordan shoes and are on sale-Free shipping.
High quality fake rolex paypal of well known brands - Jacob & Co, TAG Heuer, Cartier, Panerai and much more. *If you want to buy replica watches paypal, you have come to the right place! We are leading supplier of all kinds of strongfake watches. The leading name in luxury watches, replica rolex paypal has been the pre-eminent symbol of performance and prestige for over a century. Replica Swiss Rolex men's and ladies' watches.
just want to add a comment
wow gold
[url=http://www.thewowgold.net]wow gold[/url]
http://www.thewowgold.net
We can even think of gifting it someone because these replica Rolex watches also provides a wide range of models to choose from like Daytona replica Rolex watch,Swiss Rolex replica watch etc. Particularly when compared to the high price of a genuine Rolex, a Replica fake replica rolex watches watch shows itself to be a true value.
Yachtmaster replica
Rolex Day-Date II replica
Rolex Daytona replica
Rolex Explorer replica
Rolex GMT replica