CFMX7 and Local SQL Server Connections

[jjay@aus]

I recently upgraded from CF5 to CFMX7. All went well, but I am now not able
to access SQL Server DBs on the local Machine.

When I Verify the Datasource via CF Admin, I am getting the following error.

Connection verification failed for data source: lo_db_laptop
java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Error establishing
socket. Connection refused: connect
The root cause was that: java.sql.SQLException: [Macromedia][SQLServer JDBC
Driver]Error establishing socket. Connection refused: connect

These Datasources were working fine in CF5, and have not been changed since
migration.

Has anyone experienced similar issues? Any suggestions as to what may be
causing this?

Many Thanks in advance

[dimaint]

What happens if you try using ODBC socket to connect to the database? Same way
was used in CF5. Create System DSN first and then use it in datasource
settings. It'd be interesting to see if that works.
Alos, are you specifying username/password when creating datasources? Is your
database configured for SQL Server authentication only or both SQL Server and
Windows?

[jjay@aus]

Hi dimaint, thanks for your reply.

The datasources are created via ODBC Administrator as a System DSN. They are
working fine via ODBC Data Source Administrator. And they were fine via CF5
before upgrading to MX7.

This is the case for Datasources that existed prior to upgrade, as well as any
created post upgrade.

I'm not specifying the username and password when creating or connecting to
the Data Sources. Datasources are configured with Windows Authentication.

Thanks again

[dimaint]

Have you tried using Microsoft SQL Server JDBC drivers? When creating datasource, try to select Microsoft SQL Server from dropdown list of datasource types.

[jjay@aus]

If you mean have I tried creating a DSN via the Cold Fusion Administrator, then
yes I have, but still getting "Connection refused".

As a side point. Any Access Datasources I had setup are working fine. It is
just with SQL Server.

[dimaint]

You might want to specify username/password when creating SQL Server DSN in CF
Admin. Either username/password for specific domain user that has privilegies
or if you have mixed authentication and specific SQL Server user, then use that
user.

[jjay@aus]

I managed to connect using an ODBC Socket, and that has got me connected fine
for now. Will try connecting with UN&PW in future.

I'm still keen to know though why these DSNs wouldn't work, so if anyone has
any suggestions would love to hear them.

Thanks for your help dimaint.

[ksmith]

Review your odbc datasource in windows control panel. As you go thru to edit
the DSN, I believe the 2nd page of the dialog will offer a 'client' button.
Click and determine what client libraries are used. You likely will find it is
a connection VIA pipes. JDBC connections require a TCPIP connection. You will
need to update the mssql server to allow TCPIP, if that is the case.

You should then be able to connect with jdbc.

[jjay@aus]

Thanks for the suggestion Ken,

Network libraries were already set to TCP/IP.

Any more ideas?

Thanks

[ksmith]

First what version of mssql are you running?

Next, create a test DSN using mssql/jdbc. Do not worry if it does not
validate yet. Next create a simple test query using that datasource. "Select
* from foo" is fine. What error does it return? Often the error from a query
is much better than info given in cfadmin.