I recently had an issue where the datasources using the Microsoft SQL Server Driver in ColdFusion were failing. The error was:
java.sql.SQLException: Timed out trying to establish connection
There was no change with the database server. For some reason the driver was just not connecting. This led to an exploration of connecting using JDBC both with the Adobe jar included with ColdFusion and by downloading the Microsoft JDBC Driver for SQL Server.
Using the Microsoft JDBC Driver for SQL Server
First you must obtain the driver and make it available to ColdFusion:
- Download Microsoft JDBC Driver for SQL Server from: https://learn.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver16
- Unzip the download and place the driver jar(s) into the following directory on the server (for Windows):
C:\ColdFusion2021\cfusion\lib - Restart the ColdFusion service
To create a JDBC data source to connect to an MS SQL Server database in ColdFusion:
- Login to CFADMIN
- Navigate to the Data & Services tab in CFADMIN
- Enter a Datasource Name:
developmentServerJDBC - For Driver choose:
Other - Click:
Add
On the ensuing page enter the additional information (change to your info):
- CF Data Source Name:
developmentServerJDBC - JDBC URL:
jdbc:sqlserver://developmentServer:databaseName=developmentDatabase;Port=1433;encrypt=false; - Driver Class:
com.microsoft.sqlserver.jdbc.SQLServerDriver - Driver Name:
mssql-jdbc - User name:
developmentUser - Password:
************ - Description (optional):
Uses Microsoft jar file
Using ColdFusion’s Microsoft SQL Server Driver
To create a data source to connect to an MS SQL Server database in ColdFusion:
- Login to CFADMIN
- Navigate to the Data & Services tab in CFADMIN
- Enter a Datasource Name:
developmentServer - For Driver choose:
Microsoft SQL Server - Click:
Add
On the ensuing page enter the additional information (change to your info):
- CF Data Source Name:
developmentServer - Database:
developmentDatabase - Server:
developmentServer - Port
1433 - User name:
developmentUser - Password:
************ - Description (optional):
Uses ColdFusion's Microsoft SQL Server Driver
BONUS: Using ColdFusion’s Microsoft SQL Server Driver with JDBC
To create a JDBC data source to connect to an MS SQL Server database in ColdFusion:
- Login to CFADMIN
- Navigate to the Data & Services tab in CFADMIN
- Enter a Datasource Name:
developmentServerMicrosoftJDBC - For Driver choose:
Other - Click:
Add
On the ensuing page enter the additional information (change to your info):
- CF Data Source Name:
developmentServerMicrosoftJDBC - JDBC URL:
jdbc:sqlserver://developmentServer:databaseName=developmentDatabase;Port=1433;encrypt=false; - Driver Class:
macromedia.jdbc.MacromediaDriver - Driver Name:
macromedia-jdbc - User name:
developmentUser - Password:
************ - Description (optional):
Uses Adobe jar file