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:
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.
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.
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).
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.
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