In the past Microsoft rebranded the Data Direct JDBC driver for their own just like Macromedia does with ColdFusion MX. As of sometime in early 2003 they started building their own driver. As of Nov 2003 the SP2 version was available. Data Direct has a comparison data between the Microsoft driver and Data Direct drivers. This would apply to the Macromedia drivers as well since they brand the latest drivers from Data Direct.

Steps for configuring the Microsoft SQL 2000 driver:

1. Go to the download page or registration page you may need to log in with a Microsoft Passport to gain access to the file.
2. Scroll to the bottom of the download page and download the setup.exe
3. Run the setup.exe program. It will install to C:\Program Files\Microsoft SQL Server 2000 Driver for JDBC
4. Under C:\Program Files\Microsoft SQL Server 2000 Driver for JDBC\lib copy the files msbase.jar, mssqlserver.jar, msutil.jar to the JRun4/servers/lib or CFusionMX/runtime/servers/lib directory. (depending on how you installed CF). In some cases if you installed standalone you will not have a CFusionMX/servers/lib directory, if you do not go ahead and create it.
5. Restart CF
6. Open up the ColdFusion Administrator and click on Data Sources
7. Enter a Datasource name and choose "other" in the dropdown menu for the driver
8. Enter the following information for the driver details
   JDBC URL: jdbc:microsoft:sqlserver://hostname:port;databasename=dbname
   (my example jdbc:microsoft:sqlserver://192.169.1.71:1433;databasename=tacwebdb)
   Driver Class: com.microsoft.jdbc.sqlserver.SQLServerDriver
   Driver Name: com.microsoft.jdbc.sqlserver.SQLServerDriver
   Username: dbusername
   Password: dbpassword
9. Click Submit and the datasource should verify correctly if you have all the parameters correct

Thanks to Sarge for assisting with the steps.

The DataDirect JDBC driver claims to be able to do Windows Authentication. If this is the case, then how come Macromedia's doesn't do this?

Shouldn't the standalone installation path be (cfmx_root)/runtime/servers/lib?

Thanks for the catch on the typo, I corrected it. I will look into the windows authentication question.

Cheers Brandon. By the way, I didn't fancy retyping the jdbc URL and class in the CF administrator for each of my SQL server datasources, so I modified the datasource settings directly in CFusionMX/lib/neo-query.xml.

I added the following xml to the top of the file, where the different types of datasource are defined. This should configure a datasource type named 'Microsoft Native SQL Server driver' with the appropriate URL and class. <var name='MSNativeSQLServer'><struct type='coldfusion.server.ConfigMap'><var name='url'><string>jdbc:microsoft:sqlserver://[host]:[port];databaseName=[database];SelectMethod=[selectmethod];sendStringParametersAsUnicode=[sendStringParametersAsUnicode]</string></var><var name='name'><string>Microsoft Native SQL Server driver</string></var><var name='handler'><string>sqlserver.cfm</string></var><var name='vendor'><string>Microsoft</string></var><var name='class'><string>com.microsoft.jdbc.sqlserver.SQLServerDriver</string></var><var name='port'><string>1433</string></var></struct></var> I now have 'Microsoft Native SQL Server driver' available as an option in the drop-down menu on the CF admin datasources page, which makes it a bit more convenient to add a SQL server datasource using the microsoft driver.

In response to the question about the data direct drivers doing windows authentication, that feature was introduced in the 3.3 version of the Data Direct Drivers. We are curently shipping version 3.2 and will probably not move to 3.3 until the next release of CF. There are several issues with the 3.2 drivers that are being addressed and Macromedia will release a hotfix for the 3.2 drivers in the near future.

I tried the tip above, adding to the neo-query.xml file. I get the choice in my drop down, however, when I create the datasource, the Macromedia driver is used instead of the Microsoft driver. Also, the screen says 'Microsoft SQL' instead of 'Microsoft Native SQL Server driver'.

You're right - my mistake. As far as I can tell, <var name='handler'>sqlserver.cfm</var> tells CFMX which template to use to add the new datasource, and sqlserver.cfm adds a 'Microsoft SQL' datasource.

I suppose the solution would be to write a custom handler for the Microsoft driver, but I don't know how that template works.

I would guess it's also possible to change the 'Microsoft SQL' settings in neo-query.xml so that those datasources use the Microsoft driver instead of the Macromedia driver.

Has anyone noticed performance improvements with the Microsoft driver? Our cfmx server still falls over with 100% cpu usage every now and again.

Does CFMX 6.1 or 7 have the ability to do windows authentication built into the CF ADMIN without having to go through hoops to get it to work? Has anyone had to do this? Thanks for any info.

Hi All, the link to the Data Direct page with the comparison seems to be broken. Here's a link directly to the report:

http://www.datadirect.com/products/jdbc/docs/ddsqlvsms.pdf