TechNote (Archived)

Troubleshooting data sources and database connectivity for Windows platforms

If you are having trouble verifying a data source in Macromedia ColdFusion, this guide may give you some tips on how to troubleshoot your connectivity problems.

Connecting To Your Database
Configuring Data Sources
Testing Data Sources
Troubleshooting Data Sources That Fail Verification
Troubleshooting Data Sources by Database Type
(Troubleshoot your specific data source: DB2, Informix, Interbase, mySQL, Oracle, SQL Server, Sybase, SQL Anywhere)

Connecting To Your Database

ColdFusion Server can communicate with virtually any database through ODBC, OLE DB, Native and JDBC database drivers.

ODBC

Available in all versions of ColdFusion Server.
ColdFusion Server can communicate with databases that have an ODBC 3.0 compliant driver.
ColdFusion uses Microsofts ODBC subsystem and ships with Microsofts ODBC Drivers for several well-known databases (Microsoft Access, Microsoft SQL Server, Paradox, and so forth). Macromedia does not make ODBC drivers.
Please contact your database vendor for ODBC drivers. There are also several 3^rd party vendors who make ODBC database drivers, including Merant/Intersolv, OpenLink Software and ATI.

OLE dB

Available through version 5 of ColdFusion Server.
Many vendors (including Microsoft) make OLE dB drivers and ODBC drivers. OLE dB is a different way of connecting to a database.

Native

Available through version 5 of ColdFusion Server
ColdFusion Server Enterprise can connect to Sybase, Oracle, Informix and DB2 using Native Drivers.

JDBC

Available in ColdFusion MX.
ColdFusion Server can communicate with databases that have a JDBC 2.0 compliant driver.
ColdFusion uses DataDirect's JDBC Drivers for several well-known databases (DB2, Informix, Sybase, Oracle). Macromedia does not make JDBC drivers. Supported database driver versions vary by ColdFusion Server version. Refer to the documentation for your ColdFusion Server to see what database driver versions are supported.
Please contact your database vendor for JDBC drivers. There are also several 3^rd party vendors who make JDBC database drivers. A searchable listing is available at industry.java.sun.com/products/jdbc/drivers.

The steps for configuring data sources vary by version of ColdFusion and type of driver and database being used. Refer to the documentation for your ColdFusion Server to see the steps for your particular configuration.

Testing your Database Connection

To test your database connection, use the following steps:

Create a simple CFML template containing a cfquery tag that queries your data source.

For instance, if you set up a data source in your ColdFusion Administrator called testdatasource, which contains a table called testtable, you could create a simple CFML file with the following lines:
```
 <cfquery name="testquery" datasource="testdatasource"> Select * from testtable</cfquery> 
```
You may also want to try embedding the username and password attributes into the cfquery tag to see if the query works.
Put the file in your webroot.
Browse the file from your web browser.
If the query fails, use the steps and related TechNotes in this document to troubleshoot the problem.

Troubleshooting Data Sources That Fail Verification

Under certain conditions, an otherwise valid data source connection can fail to verify in ColdFusion Administrator or produce an error message. Use the steps below to successfully verify a data source in the ColdFusion Administrator:

Ensure that you have properly set up the data source connection inside of ColdFusion Administrator. Check the following for your database:
- Check that the Server Name/Host String or path to the database file(s) is correct.
- Check that the username and password is correct. In the ColdFusion Administrator Data Sources area, click CF Settings on the detail page for your data source. Check the username and password for your data source.
- If you use OLE dB, check that you have entered the correct Provider name. For the Microsoft OLE dB Drivers, the Provider should be SQLOLEDB. For all MS SQL 6.5/7.0 connections and and other databases, the Provider should beJETOLEDB. For all other OLE dB data sources, consult your database driver documentation.
If using ODBC or JDBC, update your drivers. It is recommended that you use the driver provided by your database vendor. After updating ODBC drivers, reboot your machine and recheck your data source definitions.
Enter appropriate information from the ODBC Control Panel. There are some options that can only be set from the ODBC Control Panel. For instance, you can set up a network library (Named Pipes, MultiProtocol, TCP/IP, and so forth) for a SQL Server 6.5/7.0 System DSN to communicate to the server with.
- Open the ODBC Control Panel.
- Click your System DSN.
- Click the Configure button.
- Click through the configuration screens, checking that all entered information is correct.
Ensure that all configuration parameters are correct for the data source. If you are unsure, consult your database documentation.
For ODBC connections, download the tool, SqlCon32, and try connecting to the database. If you cannot connect to the database using this tool, consult your database documentation or ODBC driver vendor for assistance; your connectivity problem is due to your ODBC driver.
For JDBC connections, be sure the Driver Class and URL fields are entered correctly. Note that the information in these fields is case-sensitive.
For file-based databases (Access, Paradox, and so forth), ensure that that the database is not flagged as a read-only file, and that the user that the ColdFusion services "Log in as" has read/write access to the file and/or the directory containing the file. For more information on this, see TechNote Accessing remote databases with ColdFusion (TechNote 17010).
Changing the account that the ColdFusion Server services run under from the System account (the default) to an account that has Administrator privileges on the local machine. You can do this by click Start > Settings > Control Panel > Services > double-click all ColdFusion services and change the account.

If changing the account ColdFusion runs under fixes the problem, it means that you have some sort of security issue on your system that needs to be resolved. Additionally, ensure the following items:
- That the System account has rights to the \cf_root\bin directory, any directories in your webroot which contain CFML files and, if applicable, the directory where your native database software is contained.
- If you changed the account that ColdFusion runs under, make sure that account has administrative rights to the local machine (including the "Log on As A Service") and is in the Administrators group on the local machine.