Cannot Connect to SQL 2000

[Guest]

I recently reinstalled SQL Server and now I am unable to create the DSN through CF Admin. The name of the SQL instance was changed during the reinstall. I have attempted to use the new name in CFAdmin but get the following error:

"java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][Macromedia][SQLServer JDBC Driver]The requested instance is either invalid or not running.
The root cause was that: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][Macromedia][SQLServer JDBC Driver]The requested instance is either invalid or not running."

The instance is running and I can create a connection through the Data Source (ODBC) tool in Admin Tools but I can set it up in CFAdmin.

Thanks in advance for your advise!

[JR__Bob__Dobbs-qSBHQ2]

Things to check:

1. Mixed mode authentication in enabled on your SQL server

2. TCP/IP protocol is enable on your SQL server

3. The port SQL listens on and the port configured in CFAdmin are the same

4. The port is not blocked by a firewall

[Guest]

Thanks for the reply. I've checked all the things you mentioned. They are all correct. The error looks to me like CF thinks the instance does not exist. I can see it running and can connect to the db so I am stumped. Thanks again.

[JR__Bob__Dobbs-qSBHQ2]

Does the machine have multiple instances of SQL installed, including MSDE or SQL Express? If so make sure that your connection includes "SERVERNAME\INSTANCENAME" for a named instance or "SERVERNAME\" for the default instance.

[Guest]

I don't think so. The instance is registered as servername\instancename. I am using the whole name as the server name.

[cf_dev2]

Are you sure the port number is correct? I've read a few posts by people having problems with SQL 2005 express not using the expected port number.

[Guest]

Yes, checked the port several times. My server name and instance names are the same. Do you think it is possible that could cause a problem?

[JR__Bob__Dobbs-qSBHQ2]

Is it possible that the CFIDE interface or the JDBC driver included in the ColdFusion install does not handle instance names properly?

I found an article on setting up a connection to SQL Express using a newer version of Microsoft's JDBC driver. It may, or may not, be helpful.

http://www.fusioncube.net/?p=59
http://www.microsoft.com/downloads/details.aspx?familyid=C47053EB-3B64-4794-950D-81E1EC91C1BA&displa...

[Guest]

Thank you. I gave that a try. The driver error went away, but it was replaced with the following:

"com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host has failed. java.net.UnknownHostException:
The root cause was that: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host has failed. java.net.UnknownHostException:"

[cf_dev2]

That's good news. Are your firewalls configured to allow access to the sql server? Also, is TCP/IP enabled?

[Guest]

Yes to both.

[cf_dev2]

> UnknownHostException

Hm. On second thought that error sounds like its not resolving the server name. Have you tried using using the IP address in place of the server name?

[JR__Bob__Dobbs-qSBHQ2]

Does the account the ColdFusion service is running as have rights to access other servers on your network?

I agree with cf_dev2, it looks like the service is having problems connecting on the correct port and/or resolving the address of the SQL machine.

[Newsgroup_User]

I have a vague recollection that there was a bug in one of the SQL2k
service packs that made it difficult/impossible for CF to talk to it.
What's your patch level on the SQL Server?

I've never attempted to connect to a named-instance SQL Server using the
instance name; it's never worked. I've always used IP Address (or DNS
domain name... NOT Windows network computer name) and port number.

Named instances of SQL2k can end up with weird port numbers, which are
sometimes in use by other services. Have you sniffed the port to see
what's actually repsonding on it? It might not be the SQL Server.

Can you telnet onto it (I'm buggered if I know any commands SQL Server will
accept via telnet, but at least getting a connection would be a start).

--
Adam

[Guest]

I have tried usingthe IP address instead. This is an instance running on my local machine. I have tried every combination I could think of IP\instancename, computername\IP. I have checked the port several times and even tried changing it. I can create a DSN through the admin tools in Windows and connect through an ODBC Socket connection but I get too many weird translations of my data. Actually I am running with no service packs. This is a fresh reinstall that was performed yesterday by Microsoft. Besides the client network tool, is there another way to determine the port? I cannot telnet to that port using 127.0.0.1.

[JR__Bob__Dobbs-qSBHQ2]

Try the using SQL Server Network Utility to check the port SQL is listening on.

Here are some Microsoft KB articles that may, or may not, help.
http://support.microsoft.com/kb/294453/en-us
http://support.microsoft.com/kb/293107/en-us
http://support.microsoft.com/kb/312935/en-us

I also suggest you apply the latest service pack in case your connectivity issue is related to a bug in the RTM version of SQL. Also you will be patching known security vulnerabilities.

[Newsgroup_User]

> local machine. I have tried every combination I could think of
> IP\instancename, computername\IP.

Nonono. Just the IP address. Forget about the instance name: you don't
need it.

Just use the IP address and the port.


> translations of my data. Actually I am running with no service packs.

Patch it up.


> a fresh reinstall that was performed yesterday by Microsoft. Besides the
> client network tool, is there another way to determine the port? I cannot
> telnet to that port using 127.0.0.1.

Have a look in
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp

--
Adam

[Guest]

Just the IPdoes not work. This is the error I get when I give that a try. "Connection verification failed for data source: lpSQL
java.sql.SQLException: [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.SQLException: [Macromedia][SQLServer JDBC Driver]Error establishing socket to host and port: 127.0.0.1:1433. Reason: Connection refused: connect"

I'm downloading the SP now.

[cf_dev2]

Yes, try the SP. I remember a conversation with Dr. Who a few weeks ago. According to them they were getting Connection refused: until they applied SP4 (CF8/MS SQL2000). In any case applying the SP probably wouldn't hurt.

[Newsgroup_User]

> In any case applying the SP probably wouldn't hurt.

Yep. Patch it up, and try again.

I realise Lynda said that the computer in question is a local PC, so
probably is not outwardly facing (ie: onto the 'net). But this warning
could still be relevant:

A couple of years ago - long after SQL Slammer had done the rounds, so one
might think it was no longer a problem - one of our jr developers was
tasked with setting up a new SQL Server instance which ended up being
outwardly facing. She didn't remember to patch it (and I - the box was my
responsibility - forgot to check).

It took about half an hour of uptime for it to get infected, and about
15min after that before our LAN was flattened by virus-enabled traffic
trying to find other machines to infect. Suddenly 20-odd people could not
work whilst we cleaned out their machines, and checked for any number of
other infections. I was not popular that day (not least of all with
myself!).

Always... ALWAYS... make sure your SQL Servers are fully patched.

I would *never* attach a machine with non-patched SQL Server even to a LAN,
let alone the outside world.

I cannot stress that enough.

--
Adam

[cf_dev2]

> It took about half an hour of uptime for it to get infected, and about
> 15min after that before our LAN was flattened by virus-enabled traffic
> trying to find other machines to infect.

Holy crap. You've got me wanting to check the SP levels of every sql server I work with... and I'm not the dba or network admin

[Guest · Accepted solution]

SP 4 fixed the problem.

Thanks to everyone for their help!!