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
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…
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.