Connecting ColdFusion to SQL Server Express
Posted by Steve BrownleeApr 12
I’ve had SQL Server 2005 Express Edition installed for a while just to play around with, but I recently decided to try to develop a ColdFusion application that uses it as the backend - for fun. It turned out not to be fun, but with a little searching and tinkering with some settings, I was able to get ColdFusion 7 to successfully connect to it.
These instructions assume that you have the Microsoft SQL Server Management Studio Express installed to manage these tasks.
1. Open the SQL Server Studio
2. Click on the Registered Servers button in the toolbar
3. Open the Configuration Manager
4. If needed, enable the TCP/IP protocol under SQL Server Configuration Manager > SQL Server 2005 Network Configurations > Protocols for SQLEXPRESS
5. Now double-click the TCP/IP protocol to view the properties. Click on the IP Addresses tab and make sure that the TCP Port is set to 1433 for every IP type.
6. Open the Properties page for your server instance
7. Go to the Security item and make sure that you have Mixed Mode enabled. After making sure all of the these settings are in place, you should now restart your server.
8. Once the server restarts, create a new login to be used by ColdFusion, give it a SQL Server Authentication password, and set the default database to the one you’re setting up.
9. Now it’s time to set up the ColdFusion datasource. Download the Microsoft SQL Server 2005 JDBC Driver and place the resultant sqljdbc.jar file in the WEB-INF\cfusion\lib directory, or wherever your cfusion\lib directory is located.
10. Log in to your ColdFusion Administrator and create a new datasource with the same name as the database you want to connect to. Make the datasource type ‘other’.
11. Fill in the fields with the following properties
JDBC URL: jdbc:sqlserver://{server name}\sqlexpress:1433
Driver Class: com.microsoft.sqlserver.jdbc.SQLServerDriver
Driver Name: SQL Server 2005 JDBC
Then enter in the username and password you created in step 8 into the appropriate fields. This should get you a successful ColdFusion datasource using SQL Server Express.
Reference Articles
Login failed … not associated with a trusted SQL server connection
SQL Server Express 2005, Finally Installed
Great Article!!!!
Thanks for taking the time to explain this process…
Helped alot,
MG
I have been looking for an article on how to do this for ages. This article needs to go to adobe(macromedia) and microsoft so it can help the other thousands of people with the same question !! awsome post!!
Thanks for the clear description.
Question: why install the jdbc driver in preference to using the SQL Server driver option available in the CF Administrator DSNs? Is the jdbc driver newer/beter for SQL Express?
That’s correct. The driver that ships with CFMX is not compatible with SQL Server Express.
Thanks for confirmation Steve. the standard CF admin SQL server driver option seems to work ok (no errors as far as I can tell) with SQL Express. All said I’ll rather install the jdbc driver as you advise. Again many thanks for the nice article.
Could not get it to work with the jdbc driver - whilst CF admin shows dsn connection status = OK unable to select anything from tables in database via cfquery using the dsn. Works fine with the standard CF SQLServer driver option however.
Spoke too soon - got it working, I had incorrect permissions set in my SQL server account for the DSN.
That’s odd, I could not get it to work with the packaged SQL Server driver. I’m glad you got it all set up and that I was able to help a little.
The one difference in setting up with the jdbc driver vs the included CF driver was name of the server. In the jdbs driver setup, as per your example, it is something like this:
jdbc:sqlserver://{servername}\sqlexpress:1433
with the CF driver set you just use
Server: {servername} without the “sqlexpress” and supply the database name (so the dsn name does not have to be thesame as the database name).
I have tried the example several times and I still can’t get it to work. I keep getting an error stating No suitable driver available. I know that this has to do with the JDBC URL but having tried many different combinations I am still getting the same result.
Jeremy, what application server are you using and where did you place your driver JAR file?
I am using coldfusion mx 7 as a local testing server. I placed the following file in the following folder:
sqljdbc.jar
C:\CFusionMX7\wwwroot\WEB-INF\lib
I’d recommend copying the sqljdbc.jar file to the following path (if it exists). If it doesn’t just make sure that the file is in the same directory as cfusion.jar.
C:\CFusionMX7\wwwroot\WEB-INF\cfusion\lib
cool…ty. I actually decided to use a remote server that has mySQL file on it. That allowed it to work.
Hey Hi,
Thanks for the info,
can you explain the steps wit an example?
Thanks a lot,
Johnson
Hi All,
The exception i am getting is
——
An unexcepted error occurred. The name of the exception is: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host has failed. java.net.UnknownHostException:
–
Can anyone advise??
Thanks,
Johnson
Johnson, as for your first comment, the steps are the examples. I’m not clear on what you need. For your second comment, the Exception appears to be self-explanatory. The IP address or host name that you specified in your driver URL is either invalid or SQL Server isn’t running on that machine.
Hello All,
Thanks for the info. I have been trying to get this to work for a couple of days. I figured someone out there was smart enough to figure it our. Good work Steve.
Everything worked just fine except I found out I had to map the new login user to the my default database to get the login to work. (user mapping - within login properties for the newly created user)
Thanks for the help.
Update on using the JDBC driver compared with the CF supplied SQL driver (see earlier comments). Whilst both work I noted that the JBDC driver handles Unicode characters correctly (with inserts/updates via queryparam with cfsqltype=”cf_sql_varchar”) whilst the CF supplied driver does not. So using the JDBC driver as you have described is certainly preferrable.
In order to get it to work, I had to take one extra step.
I had to go into User Mapping when creating the login and click on the database you want to connect to. Without this, it just does not work for me.
Hope this would be helpful to some people.
HEllo,
I used the steps you stated above. However, I still cannot get it to work…I have placed the sqljdbc file in both the web-inf\lib and the cfusion\lib dir…..i keep getting this error:
No suitable driver available
Thanks,
Edgar
Hello,
Nevermind. I got it to work. I was typing the username incorrectly.
Hi Steve,
Really an execellent page, you given step by step clearly.
But in the 9th step you told to download SQL JDBC driver.. With out using thta i connected SQL server from ColdFusion perfectly.
If you feel it is needed I am always ready to send the details.
Thanks,
Mahesh Komuravelli
Hi Steve,
Great job on outlining how to make this happen. After trying several other “solutions” yours was the one that worked!
Thanks.
Antonio
All,
I have spent 1 hour working on this.
All of the comments were helpful.
I had recently downloaded CFMX7 and SQLEXpress
I got this working without using the JDBC driver above.
I did steps 1 - 8 above. However in the replies above you do have to remember to enable the new SQL user to the database you are mapping.
I then used the Microsoft SQL server option and was able to connect. At least that’s what CF Admin says….
Thanks to all who have participated in this discussion. I learned alot.
Your article is very good. We are having a strange issue. The error we keep getting is:
Connection verification failed for data source: xpSearchJDBC
java.sql.SQLException: No suitable driver available for xpSearchJDBC, please check the driver setting in resources file, error: null
The root cause was that: java.sql.SQLException:
No suitable driver available for xpSearchJDBC, please check the driver setting in resources file, error: null
However if I look at the CF Server Java Class Path it shows:
C:/JRun4/servers/cfusion/cfusion-ear/cfusion-war/WEB-INF/cfusion/lib/sqljdbc.jar;
We have installed the JDBC drive under the default Prog files directory structure.
Driver Class: com.microsoft.sqlserver.jdbc.SQLServerDriver
JDBC URL:
//192.168.1.63:1433
Does anything need to be installed on SQL2005 server?
wahoo!!!!! This post enabled me to set up the datasource. Thank you SO MUCH!!!
Some extra things I had to do, in case they help anyone else: I had to restart my ColdFusion service, and I set the permissions on my database to grant “SELECT”, “UPDATE”, etc so that I wouldn’t get the error “The SELECT permission was denied on the object ‘M_Names’, database ‘Actions’, schema ‘dbo’.”
Thank you again!!!!!
One of the developers was able to correct the string. We are working now. Thanks!
Lee
Lee Solt,
Which string the developer fix and how did he fix it?
Thanks,
Jess
I quickly got ColdFusion MX7 working with Sql Server Express on one of my systems by enabling Named Pipes and TCP/IP as describe at http://www.adobe.com/cfusion/knowledgebase/index.cfm?id=kb400255.
When I tried to do the same thing on my laptop I received “The requested instance is either invalid or not running”. After trying all of the suggestions on this site and half a dozen others I finally noticed JS’s comment above and removed “\SQLEXPRESS” from the server name in the datasource specification in ColdFusion Administrator. And then it worked fine.
I have since talked to someone else who had a similar problem. It seems that if you have another version of Sql Server installed you MUST specify SQLEXPRESS in the server name, e.g. MYSERVER/SQLEXPRESS. I there is no other version of Sql Server you MUST NOT specify SQLEXPRESS, e.g. MYSERVER.
Hope this helps
very useful information and well explained.
Thanks.
Hi Mahesh,
Pls post details about connecting without sqljdbc driver.
Am having a headache setting up a datasource for CF8 (standard edition) to talk to a ms sql server 2005 database with ms jdbc driver. The only difference is that the box has ms sql server 2000 running already, so, I’m using a different port other than 1433.
Keep on getting “No suitable driver available for {datasource}… please check the driver setting in resources file…”, so, it looks like the
sqljdbc.jar file was not found by jrun or cf server, so, I placed this file in several cf and web directories (one can think of logically), and yet… it really messed up my weekend.
Thoughts would be much appreciated.
Attempt of using the default Macromedia’s jdbc driver did not get better…
When I try to connect, I get “The requested instance is either invalid or not running”. What have I forgotten?
NB I’m using TCP (it’s enabled and ‘Listen All’ is true); I’ve set up SQL Server’s ‘IPAll’ settings to have no dynamic port, static port 1434; and I’ve specified the port number in the connection string, as well as the instance name.
Never mind; looks like firewall problems (local connection on the database server succeeded).
This manual setting is possible through c#.net 2.0 coding, througn coding i want to make it possible this setting, if it is possible please mail me how it is.
Thank u,
with regards
kanchana.p
Hi Steve i got this error.
i already follow the comments and put the file “sqljdbc” to following directories”C:\ColdFusion8\lib” where my “cfusion.jar” there.
Connection verification failed for data source: ows
java.sql.SQLException: No suitable driver available for ows, please check the driver setting in resources file, error: com.microsoft.sqlserver.jdbc.SQLServerDriver
The root cause was that: java.sql.SQLException: No suitable driver available for ows, please check the driver setting in resources file, error: com.microsoft.sqlserver.jdbc.SQLServerDriver
please guys i dont knw what im doing wrong but i have tried your instructions can anyone just drop an email with full instructions please..
Hello,
First of all thanks for providing all the information.
I tried all the settings and JDBC driver but here is the error when creating DSN.
Connection verification failed for data source: sports
java.sql.SQLException: No suitable driver available for sports, please check the driver setting in resources file, error: null
The root cause was that: java.sql.SQLException: No suitable driver available for sports, please check the driver setting in resources file, error: null
Please help……
Thanks
A.A. Khan
@AA, where did you install your driver file? Also, I have to ask, you restarted your ColdFusion service after installing it, correct?
Hello Friend thank you for your reply.
C:\CFusionMX7\lib\sqljdbc.jar
also
C:\CFusionMX7\wwwroot\WEB-INF\lib\sqljdbc.jar
in both places.
i have all the other settings that mentioned above as point 1,2,3….
Once again thanks for your reply.
With best regards
A.A. Khan
Hello My friend,
I am so much happyyyyyyyyyyy your last words worked
“you restarted your ColdFusion service after installing it, correct?”
The error just made me mad and i forgot to restart it, now i have restarted it and it is working
datasource updated successfully.
Thank you so muchhhhhhhhhhhh
With best regards
A.A. Khan
Great post - I wouldnt have been up and running without it.
I encountered almost every error mentioned and could still not get up and running.
My final solution was to go to SQL Server Configuration –> TCP/IP properties.
Enter 1433 for TCP Port in the “IPALL” section at the bottom of the properties screen.
Worked a treat after that.
Thanks!!
Hi guys,
I still cannot connect to SQL express 2005 with CFMX 6.1.
My error is java.sql.SQLException: Login failed for user “Coldfusion”.
In fact the database name doesn’t appear in the connect string set in CF admin? Right?
It’s made via the SQL user (here coldfusion) which map to the database (paf for me) in the “User Mapping” menu. Is it?
Thanks for help.
Alice
[...] the Internet, I did find two write-ups that were a great deal of help in using ColdFusion 7 at Fusioncube and House of Fusion. I was pleased to discover that I did not need any extra JDBC drivers for CF8. [...]
This article really helped me. A few things I had to do to get mine to work:
1. After Step 9, I had to restart my ColdFusion Server. This isn’t specified in the instructions and I’m fairly new to CF administration so I didn’t realize I had to do that. Once I read some of the other posts I figured it out.
2. In Microsoft SQL Server Management Studio Express, I created a new database and a new user. The new user defaulted to the public role, but I forgot to assign the “public” role in my new database permissions. Once I had that assigned, it worked well.
I am having the worst problem with something similar to this. However, I am running Coldfusion 8 with SQL server 2000. I cannot get it set up properly and I am having a hard time finding anything about it on the internet. Can anyone lend some advice?
[...] the Internet, I did find two write-ups that were a great deal of help in using ColdFusion 7 at Fusioncube and House of Fusion. I was pleased to discover that I did not need any extra JDBC drivers for CF8. [...]
You don’t need the extra JDBC driver. Make sure you put in the servername without the \SQLEXPRESS appended to it. That is how I finally got it to work.
I am write in portuguese, please translate. tanks
Ótimo post. Passei esta tarde tentando fazer esta conexão pois estava sem conexão com internet e estava realizando tentativas. Agora consultei este post e realizei a conexão.
Muito obrigado!