Local SQL Server 2008 ColdFusion Datasource

This morning I finished installing SQL Server 2008 on my local machine for development.

Having set up the database, I hit a small issue when trying to add the database as a datasource within the ColdFusion administrator, receiving the following error:

Connection verification failed for data source: romixrDB
java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer JDBC Driver]Error establishing socket to host and port: 127.0.0.1:1433. Reason: Connection refused: connect
The root cause was that: java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer JDBC Driver]Error establishing socket to host and port: 127.0.0.1:1433. Reason: Connection refused: connect

You can see a connection issue with the datasource, with a failure to connect on the socket using the localhost (127.0.0.1) and port number (1433). By default, SQL Server 2008 doesn’t have the port number enabled.

To resolve the issue, open up your SQL Server Configuration Manager application (Programs -> Microsoft SQL Server 2008 -> Configuration Tools -> SQL Server Configuration Manager).

Expand the SQL Server Network Configuration menu item, and click on the Protocols option for the server, which will display the list of protocols in the main window:

SQL Server Configuration Manager

Right-click on the TCP/IP option, and select ‘properties’ from the context menu, which will open up the properties window.

Change the ‘Enabled’ option to YES under the ‘Protocol’ tab.

TCP IP Properties

Selecting the ‘IP Addresses’ tab, you can see the TCP Port for all IP references is set to 1433, the default port. You can also set the ‘Enabled’ option for the IP1 and IP2 options to ‘YES’ here as well. Click ‘Apply’ to save your changes.

TCP IP Properties

The changes are now saved, but you will need to restart the SQL Server service before they take effect. To do so, select the ‘SQL Server Services’ option in the config manager, and select the SQL Server item from the list.

Click the ‘Restart service’ icon on the toolbar (circled).

SQL Server Configuration Manager

Back into the ColdFusion Administrator, verify the connections again, and the new SQL Server datasource should be OK now that the ports have been opened on the database server.

22 Comments

Leave a Comment
  1. Hi Matt,

    Thanks for your article. I am facing the below issues when i tried to connect the sql server 2008 by using coldfusion MX7.

    07:44:57,424 ERROR [STDERR] A non-SQL error occurred while requesting a connection from DATASOURCE NAME

    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 DATASOURCE NAME

    07:45:00,049 ERROR [STDERR] Timed out trying to establish connection

  2. Thanks Matt, solved my problem
    two other problems i had (for anyone else doing this process):

    i use the sa user for development, but it is disabled by default. In SQL Server Management Studio, under ‘security’ and ‘logins’, double click the sa user and on the ‘status’ page check ‘enabled’ for login.

    But in cfadmin i still got “Login failed for user ‘sa’. The user is not associated with a trusted SQL Server connection”. that was because SQL server only allows windows authentication by default. to change that, connect to the server, right-click and open the properties window, and on the security tab select “SQL server and Windows Authentication mode”

    dev machine back up now…

  3. Matt – my error is slightly different. The tcp/ip are active and enabled and SQL Services have been restarted. But my ColdFusion v8 shows this error when using the SQL Server driver. It DOES connect using the ODBC Socket, but I’d rather use the SQL Server driver. I hope you can help.

    Connection verification failed for data source: rsl2
    java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]No more data available to read.
    The root cause was that: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]No more data available to read.

  4. Last I encountered this issue was when I installed CF7 and SQL 2005 Express. It happened again today when I installed CF8 and SQL Server 2008 Express. Thanks for the detailed post on this subject!

  5. I am receiving this error. I’ve tried opening all ports and adding the localhost IP into the fields, but nothing works.

    Error Message:
    Connection verification failed for data source: (this would be the name of my datasource as listed on the Local SQL Server 2008 Configuration Manager)
    java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer JDBC Driver]Error establishing socket to host and port: localhost:1433. Reason: Connection refused: connect
    The root cause was that: java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer JDBC Driver]Error establishing socket to host and port: localhost:1433. Reason: Connection refused: connect

  6. Give this a shot:

    Open a command prompt and type: NETSTAT -an

    to find out which ports are in use. I found that 127.0.0.1:1434 was listening, and not :1433. First time I had experienced this, but I changed the port in the CF Admin, and my datasource passed verification.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>