I am having trouble connecting CF9 to a MSSQL 2008 R2 datasource

Report · May 02, 2011

Hey all,

I am having a bear of a time trying to connect CF9 with a datasource in administrator. I have checked firewall and port 1433 is open.

Here is the error I am getting.

Connection verification failed for data source: DHDataWeb java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer JDBC Driver]The requested instance is either invalid or not running. The root cause was that: java.sql.SQLNonTransientConnectionException: [Macromedia][SQLServer JDBC Driver]The requested instance is either invalid or not running

I am just pulling my hair out. I am using windows 7 and am setting up a full dev platform.

Any ideas?

Thanks

Didger

Report · May 02, 2011

What have you put into the settings in CFAdmin? That error seems to suggest quite clearly that you've requested an instance that simply isn't available.

Is the instance you're connecting to on a local server? Remote server? Listening on the default port? In you're connecting to a named instance it's not impossible that it's not open on port 1433, but on an alternative port. Can you telnet to port 1433 from the CF box? Is the instance actually running?

Report · May 03, 2011

Equally, from the CF box, can you connect the the DB using its native client tools, using exactly the same network settings (domain/IP & port) and login credentials?

--

Adam

Report · May 04, 2011

The Coldfusion server, MSSql server are on the same box. It is the box I am using right now. I had it working a few days before I made this request, but I had to reconfigure my entire box from scratch. I am pounding my head as to what can be wrong.

Quite honestly since they are both on the same box I should be able to connect in a snap. Port 1433 is open in my firewall. MSSql browser shows my database as running. I have enabled tcpip in SSQ Management

My Cf setting are default: here are my settings.

Data & Services > Datasources > Microsoft SQL Server

Report · May 04, 2011

To all trying to help. This is a screenshot of my SQL Management Studio. I hope you can enlarge it as it contains the database I am connecting to and the name of the server. They are all running because I can query the tables in my database.

Thanks for the help

Report · May 05, 2011

In your services console (start | run services.msc) find the ColdFusion Application service. What user account is it running as? Windows Authentication only works if the CF user has permissions to connect to the database.

Are you sure it's running on port 1433? Can you connect to it manually, using something like telnet?

Report · May 05, 2011

Irrespective of what authentication method you're using, JDBC needs to connect to the DB via TCP/IP. Does DIDGER-PC\SQLEXPRESS resolve to an IP address (eg: can you PING it)? I suspect not.

Also... now my recollection is sketchy as I've not set up a DB server for quite a while... but when using named instanecs, I'm fairly certain the port is not 1433 by default. Have you checked that? Actually checked it?

Basically, can you do this:

TELNET DIDGER-PC\SQLEXPRESS 1433

and get a response?

Owain has correctly pointed out that whilst it's all well and good that you can connect to the DB using windows auth... that will not automatically mean CF can too. It will need to be running under an account that has access to the DB (which it won't, be default).

--

Adam

Report · May 07, 2011

I'm sorry for being such a pain. I tried pinging and telnet which both displaying messages that they could not connect.

also configuring the settings in MSSQL is above my head. I haven't set up a Database before. Do I need to reinstall to change auth type. Or just someone to hold my hand?/

Thanks so much

Sean

Report · May 07, 2011

also configuring the settings in MSSQL is above my head. I haven't set up a Database before. Do I need to reinstall to change auth type. Or just someone to hold my hand?/

I think holding your hand would just make it trickier for you to type "change auth type on sql server" into Google...

This is what I'd do if I had to find out (and sorry, I dunno off the top of my head, and I'm not gonna google it for you on principle 😉

--

Adam

Report · May 10, 2011

Dave, Adam and Owain.

With your help I have finally configured my database as a datasource in CF9 Administrator.

Who know it would be such a pain in the &*!!

Thanks, Thanks, Thanks.

Report · May 07, 2011

You don't need to reinstall anything. But you will need to understand how to change the values I listed in SQL Server Configuration Manager, and how to enable mixed mode authentication in SQL Server. As Adam indicated, you can easily learn about each of those through a Google search. I was very careful in my previous post to use the correct terminology.

http://www.google.com/search?sourceid=chrome&ie=UTF-8&q=sql+server+2008+configuration+manager+ip+add...

http://www.google.com/search?aq=f&sourceid=chrome&ie=UTF-8&q=sql+server+2008+mixed+mode+authenticati...

Dave Watts, CTO, Fig Leaf Software

http://www.figleaf.com/

http://training.figleaf.com/

Dave Watts, Eidolon LLC

Report · May 05, 2011

You will need to configure that specific instance to listen to a specific port, and specify that port in CF. The last time I set up SQL server 2008 R2 for this, I disabled dynamic ports and enabled TCP/1433 in SQL Server Configuration Manager for external, localhost IPv4, and IPAll. Also, as others have mentioned, Windows authentication requires that the CF service run as the correct user, and it's hard to diagnose connection failures with it. I recommend you enable mixed mode authentication instead, and set a username and password.

Dave Watts, CTO, Fig Leaf Software

http://www.figleaf.com/

http://training.figleaf.com/

Dave Watts, Eidolon LLC