Connecting ColdFusion to SQL Server Express
Posted by Steve Brownlee on April 12, 2006Apr 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!
Posted by Steve Brownlee,
Please help out. After installing the Microsoft SQL Server Management Studio Express, i noticed that the “SQL Server Configuration Manager – link” happens to be “Grayed Out”.
Is there anything i am doing wrong?
Everyone seem to be happy with this article but me.
This is my second time posting on this website. I posted the first time and after two week, no one seem to be responding. well i will keep on trying until i get a response.
Like i said earlier, After installing the Microsoft SQL Server Management Studio Express, i noticed that the “SQL Server Configuration Manager – link” happens to be “Grayed Out”.
Is there anything i am doing wrong?
Anene, your problem really has nothing to do with the subject of my post. Your best bet is to find a SQL Server support forum. Sorry I can’t help you.
Hi All!
Please Help! I followed the instructions.
Yet i still get this error:
java.sql.SQLException: No suitable driver available for griffin_products, 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 griffin_products, please check the driver setting in resources file, error: com.microsoft.sqlserver.jdbc.SQLServerDriver
I have copied sqljdbc.jar file to the same directory as cfusion.jar.
I have my JDBC URL set to: jdbc:sqlserver://127.0.0.1:1433
=========================================
I went back and created a new datasource using the MSSQL driver, with the server set to:MOSSMAN and that only threw a login incorrect error.
I believe i am lost…
so
now i have three Datasources all set up different and all throwing errors:
Connection verification failed for data source: griffin
java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][SQLServer]Cannot open database “MOSSMAN\SQLEXPRESS\Databases\griffin_products” requested by the login. The login failed.
The root cause was that: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][SQLServer]Cannot open database “MOSSMAN\SQLEXPRESS\Databases\griffin_products” requested by the login. The login failed.
griffin2 Microsoft SQL Server Error
Connection verification failed for data source: griffin2
java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][SQLServer]Cannot open database “griffin_products” requested by the login. The login failed.
The root cause was that: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][SQLServer]Cannot open database “griffin_products” requested by the login. The login failed.
griffin_products SQL Server 2005 JDBC Error
Connection verification failed for data source: griffin_products
java.sql.SQLException: No suitable driver available for griffin_products, 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 griffin_products, please check the driver setting in resources file, error: com.microsoft.sqlserver.jdbc.SQLServerDriver
So i am using CF 8 and sql 2005 express I have tried all the steps in the tutorial except the I have not copied over the jdbc because people said you didn’y have to but i get the following errors. I tried to test the CF loging from server studio and i get this message
Cannot connect to BENJAMIN-5466E7\SQLEXPRESS.
——————————
ADDITIONAL INFORMATION:
Login failed for user ‘coldfusion’. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)
And when I try and set up the DSN from CF i get this message
* Connection verification failed for data source: sample
java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Error establishing socket to host and port: BENJAMIN-5466E7:1433. Reason: Connection refused: connect
The root cause was that: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Error establishing socket to host and port: BENJAMIN-5466E7:1433. Reason: Connection refused: connect
Please help
Thanks in Advance
Ok I know that it is bad forum ediquete to post again right after you have just posted but I really need some help and since the only stupid question is the question that isn’t asked, here I go again. I moved the JDBC driver to its right location for those of you that are having problems make sure that you are putting the JDBC driver in the webroot folder and then the path to the WEB-INF then LiB. coldfusion/webroot/Web-INF/Lib It might be confusing because there is another lib folder in the main coldfusion folder. Anyway thought that might solve someones problem.
When I set up the CF login and tried to test the login from the managment studio login screen I get this message
Cannot connect to BENJAMIN-5466E7\SQLEXPRESS.
——————————
ADDITIONAL INFORMATION:
A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)
I still get the same message from the CF admin from above.
Sorry for the double post but I really need this to work and I have tried so many thing that have failed.
Yeah me I figured it out!!!!!!!!
For those of you that are having problem with CF and sql 2005 here is a tip. In step five where you change the port to number to 1433, there is a box right under the active box labled “enable” by default this box is set to no. Change that box to yes (i.e. enabled) and that should take care of your problems connecting.This worked for me.
Triple post ouch. But well worth it.
Respected Steve Brownlee,
I just want to ask about the following problem
JDBC URL: jdbc:sqlserver://{server name}\sqlexpress:1433
if we have a sqlserver 2005 developer addition so same line will be work or not
Sorry, tariq, I have no idea
[...] http://www.fusioncube.net/index.php/coldfusion-sql-server-express? to open up TCP/IP, setup Security, and setup [...]
Does ColdFusion modify SQLserver 2005 configuration parameters ?
(sp_configure)
We are seeing parallelism being set to 1 -> so choking the processor and IO capacities !
Has anyone tried this: worked like charm for me.
Select datbase type as “other” then enter the following
URL: (enter as one line)
jdbc:macromedia:sqlserver://[dbserver]:1433;databaseName=pubs; SelectMethod=direct;sendStringParametersAsUnicode=false; MaxPooledStatements=0;AuthenticationMethod=Type2;
Driver Class:
macromedia.jdbc.MacromediaDriver
You could also refer to this link:
http://kb.adobe.com/selfservice/viewContent.do?externalId=1a3c2ad0&sliceId=2
There are a few driver updaes on the page too that could be useful.
I got this working with Coldfusion 8 with MS SQL 2005 Express server.
V
Hi All,
Thanks for this nice trouble shooting. Every thing is covered in these steps.I have spend 4 hrs today on this problem.
I want to share my experience with you.
1. Make sure you have enabled all the TCP/IP ports with Port no 1433 and mark them ON in the configuration manager.
2. Download the JDBC drivers from the Microsoft site and copy the entire COM folder into the lib directory.
3. In CF admin part use the OTHERS where you see the the drivers drop down such as access ,mysql and sqlserver
4. Finally change only the server name part in the following line jdbc:sqlserver://{server name}\sqlexpress:1433.
This link can also help you alot especially second part.:–
http://livedocs.adobe.com/coldfusion/6/Getting_Started_Building_ColdFusion_MX_Applications/config_dev_env3.htm
Best of luck……….
Sanjay Sharma
I tried with given steps and now I can able to create date source in cold fusion. The problem was tcp/ip enabled and port.
Thanks
Daya
Thank you!! Would have taken me ages to figure this out without your help. Your clear instructions did the trick. Actually I was able to make the CF/SqlServer connection through a DSN already set up, but had to download the sqljdbc.jar and also change all the server settings you mention above. I am running the Developer Editions for both SqlServer 2005 and ColdFusion. Thank you agan!!
Just wanted to add my thanks for this complete and accurate tutorial… I don’t think I would have made it thru without your help… THANK YOU!
Make sure right click user “sa” go to Properties-> Status-> Login (Enabled)
Connecting to SQL Server 2005 has been the biggest headache so far this year … I’m glad I found this article. Good work thank you!!!
I’m off to the pub …
Sapna
I have the 2008 version and got the same error. What can I do?
Bob
Thank you so much for taking the time to write this article, it is much appreciated and extremely useful!!
I have found the instructions very helpful and managed to set up two data sources linked to two databases on the same SQL Express Server instance – or so I thought.
I manually created the two database stubs using Server Management Studio Express and then used the same names when creating the datasources.
Working on the belief that I had connected to the two databases I then ran a ColdFusion process that auto autocreate and populate tables in each of these databases.
It was not until I went in with Server Management Studio Express to examine the tables that I realised that the tables had not been created in the two databases – after searching, I found the tables under:
{servername}\Databases\System Databases\master\Tables
Has anyone else experienced this problem
John
Thank you so much !! Have been trying to connect Adobe Flex Builder to the SQL server database for 2 days until I landed here
Really thanks alot !! Great help !!
Great article, I have also posted thanks on my blog:
http://philipbedi.blogspot.com/2009/09/sql-express-dsn-creation-problem.html
http://jdbcerrorinfo.blogspot.com/2009/11/exception-merantsequelink-jdbc.html
I am getting the error
“Connection verification failed for data source:TEST
java.sql.SQLException: Timed out trying to establish connection”
By using ColdFusion MX7 trying to create the datasources for the sql server 2008.
Any help!
I just got CF9 and SQL Express 2008 Working!
I used the newer Microsoft SQL Server JDBC Driver 2.0
There are two files and you must use the sqljdbc4.jar file. (I found that out from an error message)
All other instructions are the same however like some said earlier, be sure to restart Coldfusion after step 9, be sure to put the port number in all ip address sources, and make sure you give proper permissions to the SQL User you login with (I preferred to manage the security from the datasource level security folder rather than the database level security folder).
Thanks for your reply! I am using the jar which u have mentioned. I am getting the belwo error.
07:44:57,424 ERROR [STDERR] A non-SQL error occurred while requesting a connection from TEST
07:44:57,424 ERROR [STDERR] Timed out trying to establish connection
07:45:00,049 ERROR [STDERR] Exception in thread “Thread-21″
07:45:00,049 ERROR [STDERR] java.lang.LinkageError: javax/net/ssl/SSLSocketFactory
07:45:00,049 ERROR [STDERR] at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:1368)
07:45:00,049 ERROR [STDERR] at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1058)
07:45:00,049 ERROR [STDERR] at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:833)
07:45:00,049 ERROR [STDERR] at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:716)
07:45:00,049 ERROR [STDERR] at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:841)
07:45:00,049 ERROR [STDERR] at coldfusion.server.j2ee.sql.pool.JDBCPool.createPhysicalConnection(JDBCPool.java:562)
07:45:00,049 ERROR [STDERR] at coldfusion.server.j2ee.sql.pool.ConnectionRunner$RunnableConnection.run(ConnectionRunner.java:67)
07:45:00,049 ERROR [STDERR] at java.lang.Thread.run(Unknown Source)
07:45:00,049 ERROR [STDERR] A non-SQL error occurred while requesting a connection from
07:45:00,049 ERROR [STDERR] Timed out trying to establish connection
Hello All,
Its solved the problem Instead of choosing OTHER in th datasource type dropdown, choosed Microsoft SQL Server and its working. Please correct me if i am wrong. (In the database side, the compactability level set to 100)
Finally it worked for us with the dropdown “OTHER”. We have wrongly placed the sqljdbc4.jar. Instead of placing in the WEB-INF\lib, we placed inside the \WEB-INF\cfusion\lib.
Thanks for the great article.
Updated information from my other implementations of the newest driver.
I downloaded Microsoft SQL Server JDBC Driver 2.0 and used sqljdbc4.jar (the driver) and sqljdbc_auth.dll (for integrated security) from:
http://msdn.microsoft.com/en-us/data/aa937724.aspx
I put them in the appropriate directories as follows (restart cf service after placement):
C:\ColdFusion8\wwwroot\WEB-INF\lib\sqljdbc4.jar
C:\WINDOWS\system32\sqljdbc_auth.dll
New Datasource Type: “other”
CF Data Source Name: “WEB1″
JDBC URL: “jdbc:sqlserver://localhost:1433;databaseName=WEB1;integratedSecurity=true;”
Driver Class: “com.microsoft.sqlserver.jdbc.SQLServerDriver”
Driver Name: “Microsoft SQL Server JDBC 2.0″
User name: “”
Password: “”
Description: “”
Exceptionally useful info. Thank you!
Note-1: I have Protocols for MSSQLSERVER as well as Protocols for SQLEXPRESS beneath the Sql Server 2005 Network Configuration heading (above, point #4). I needed to make sure the TCP/IP for MSSQLSERVER is also enabled for port 1433.
Note-2: I had to copy/paste sqljdbc4.jar into the same folder where cfusion.jar already existed. Could be that sqljdbc.jar would also have worked, but since sqljdbc4.jar worked, I went with that.
Note-3: Since I had Protocols for MSSQLSERVER, I was also able to set up a connection using the Microsoft Sql Server Driver rather than ‘Other’ (above, point #10), as long as I had enabled TCP/IP for MSSQLSERVER. I did not test whether I still needed to add the sqljdbc4.jar file for the MSSQLSERVER connection to work. Perhaps not?
Steve – thanks for the post!
I’m using Coldfusion 9 with SQL Server Express 2005 and the packaged SQL SERVER driver.
Followed your instructions above (except for using the JDBC driver) and was getting Login Failed errors. Here’s what fixed it for me:
Step #8: after setting up user, password and default database, click on User Mapping and make sure Map is checked next to the database you are trying to connect to.
Once I did this, it worked like a charm.