ColdFusion 2023: How to configure data sources with Microsoft JDBC Driver 10.6+ for SQL Server

Not much has changed since ColdFusion 10: How to configure data sources with Microsoft JDBC Driver 4.0 for SQL Server, but a small detail is now required to connect to your existing MsSQL Servers.

This article explains how you are able to use the Microsoft JDBC Driver for SQL Server under ColdFusion. The background why you should do this is the bug Macromedia database drivers leaking memory. This is a very serious bug as you can see in the linked article. Your servers will be very unstable if you run with the Adobe build in drivers for Microsoft SQL. Until today we have not found any single issue at all with the replaced drivers. It is really a drop in replacement without any side effect.

1. Download and install the Microsoft JDBC Driver for SQL Server
2. Extract the file named mssql-jdbc-12.2.0.jre11.jar from the package and copy it to C:\ColdFusion2023\cfusion\lib and also to every other instance you have installed e.g. C:\ColdFusion2023\[instance name]\lib
3. Restart all CF services via Windows Powershell e.g. Restart-Service Cold*
4. Log into ColdFusion Administrator.
5. Go to Data & Services > Data Sources
6. Create a database source of type Other. The data source name has named foo in this example. Use your own names, please.
   
   
7. Add the JDBC URL jdbc:sqlserver://192.168.0.1:1433;databaseName=foo and replace the databaseName value with your database name. The Driver Class name is com.microsoft.sqlserver.jdbc.SQLServerDriver. The Driver name can be everything, but I suggest you use the official name Microsoft JDBC Driver for SQL Server used by Microsoft. If you enabled Enable High ASCII characters and Unicode for data sources configured for non-Latin characters in the Adobe MsSQL drivers it is not required to set sendStringParametersAsUnicode=true (default) to enable UTF-8 support for the Microsoft driver. Otherwise to setup the connection with the Adobe defaults, just append sendStringParametersAsUnicode=false to disable UTF-8. Typically you do not need to add more connection parameters, but you can take a look into the list of available Connection Properties. Microsoft has enabled TLS encryption by default in driver version 10.6 and higher. If you MsSQL server has not enabled encryption the connection will fail, but you can add encrypt=false to return the previous behavior. If you run SQL instances you can simply use an URL like jdbc:sqlserver://192.168.0.1\MSSQL;databaseName=foo;encrypt=false.
   
   
8. You can also setup the Advanced Settings as before.
9. Done.

If you hate doing this manually again and again, you can use below database.cfm script and setup the database sources by code. Just place this file into your CF instance e.g. C:\ColdFusion23\cfusion\wwwroot\database.cfm and run the URL http://localhost:8500/database.cfm. Make sure you remove the CFM file from accessible paths after completed!

  admin = createObject("component","cfide.adminapi.administrator");
  datasource = createObject("component","cfide.adminapi.datasource");
  // Login to CFAdmin
  admin.login(adminPassword="your cfadmin password", adminUserId="admin");
  // Delete all configured DSNs.
  for (connection in datasource.getDatasources()) {
    datasource.deleteDatasource(connection);
  }
  // Setup global data connection parameters.
  stDSN = structNew();
  stDSN.driver = "Microsoft JDBC Driver for SQL Server";
  stDSN.class = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
  stDSN.login_timeout = 30;
  stDSN.timeout = 1200;
  stDSN.interval = 420;
  stDSN.buffer = 64000;
  stDSN.blob_buffer = 64000;
  stDSN.description = "";
  stDSN.pooling = true;
  stDSN.enableMaxConnections = false;
  stDSN.disable_clob = false;
  stDSN.disable_blob = true;
  stDSN.disable_autogenkeys = true;
  stDSN.disable = false;
  stDSN.storedProc = true;
  stDSN.alter = false;
  stDSN.grant = false;
  stDSN.select = true;
  stDSN.insert = true;
  stDSN.update = true;
  stDSN.create = false;
  stDSN.delete = true;
  stDSN.drop = false;
  stDSN.revoke = false;
  stDSN.username = "dbuser";
  stDSN.password = "your dbuser password";
  // Create the database connections.
  stDSN.name = "foo";
  stDSN.url = "jdbc:sqlserver://192.168.0.1:1433;databaseName=foo;encrypt=false;selectMethod=direct;sendStringParametersAsUnicode=true";
  datasource.setOther(argumentCollection=stDSN);
  stDSN.name = "bar";
  stDSN.url = "jdbc:sqlserver://192.168.0.2:1433;databaseName=bar;encrypt=false;selectMethod=direct;sendStringParametersAsUnicode=true";
  datasource.setOther(argumentCollection=stDSN);
 
  admin.logout();

History:

• 07/25/2023: Created documentation.