Connecting to Microsoft SQL Server 7.x, 2000

Comments

jrunrandy said on Sep 8, 2003 at 6:28 AM :

SEe http://www.macromedia.com/support/coldfusion/ts/documents/sql2000_sp_var_names.htm for information on a known incompatibility returning some variable names in result data from a stored procedure when using the SQL Server driver.

Terry Reiber said on Nov 7, 2003 at 5:17 PM :

Your best bet is to try this procedure against the NORTHWINDS database that comes with the MS SQL 2000 installation. Here's how I did it with the MS SQL 2000 Developer Edition software:

Here is some instructions:
1) In ColdFusion Administrator, use NORTHWIND as the data source.
2) Use NORTHWIND as the database.
3) Use 127.0.0.1 as the Server
4) Use 1433 as the port number

Okay, now here's the TRICKY part. Supplying the USERNAME and PASSWORD.
5) In SQL Server Enterprise manager, expand the server group.
6) Then expand the Server
7) Under the Security folder, right click on Logins.
8) Select New Login
9) Click on SQL Server Authentication button and specify NAME (make up anything you want and specify PASSWORD. This name and password will become your USERNAME and PASSWORD in Coldfusion Administrator when you try to set up your Data Source.
10) While in the SQL Server Login Properties box, also specify the database, this will be NORTHWIND and specify language ENGLISH.
11) Make sure your server is using mixed authentication. While in Enterprise
Manager, right click on the server, select Properties, then Security and select Security tab. Make sure SQL and Windows radio button is clicked.
12) Okay, with the above information, you should be able to get an OKAY button when you register the Datasource Name.

You can thank me, or if you have questions, my email is terry@softreq.com.
(I pulled my hair out for two days to get this solution).

kitchjr said on Dec 21, 2003 at 6:50 PM :

One more thing if you have done the preceding but are still having a problem...

If you are running a named instance of SQL Server you will also have to discover what random port your names instance is listening on. Once you find it out, substitute that port # for the default in the CF Admin.

The link below details the issue and provides a link to Microsoft documentation on how to find out what port your named instance is listening on.
http://www.macromedia.com/support/coldfusion/ts/documents/tn18294.htm

CRJAngel said on Mar 7, 2004 at 7:17 AM :

If you are running separate instances of CF on the Jrun configeration how do you specify data sources for explicit instances?

jrunrandy said on Apr 8, 2004 at 6:12 AM :

Updated drivers are available at http://www.macromedia.com/support/coldfusion/ts/documents/cfmx61_sqlserver_cpu.htm.

No screen name said on Jun 2, 2004 at 9:39 AM :

hi i have followed all of the above instructions and i get the following error i have been trying to get mysql server to work for about 3 hours now. please help grunt2001c@mail.ru

Connection verification failed for data source: data
[]java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][SQLServer]Cannot open database requested in login 'new database_Data'. Login fails.
The root cause was that: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][SQLServer]Cannot open database requested in login 'new database_Data'. Login fails.

shawnbecker said on Jun 8, 2004 at 7:55 PM :

same as above, I thyought it was just me

Wnt2Retire said on Jul 22, 2004 at 10:32 AM :

Connection verification failed for data source: DevelopLGPKB
[]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

I've tried all of the suggestions above also and I can not get my CF admin to connect to MS SQL Server. I get the above error everytime.

scribnerc said on Jul 22, 2004 at 11:11 AM :

I get a slightly different error after following the above...
Connection verification failed for data source: test
[]java.sql.SQLException: Timed out trying to establish connection
The root cause was that: java.sql.SQLException: Timed out trying to establish connection

any thoughts?

scribnerc said on Jul 22, 2004 at 11:13 AM :

one more thing...

I'm able to connect and add my datasource using the Jrun admin on Windows XP Pro, but the connection times out when trying to establish the datasource in the CF admin. Username and password apparently work but not on CF admin .. hmmmm...

Mike L said on Jul 26, 2004 at 6:13 PM :

The suggestions above for the NORTHWINDS database worked flawlessly for me after spending quite a bit of time trying to resolve it through other methods.

Macromedia- there must be a better way to document and set this up.

Terry Reiber said on Jul 29, 2004 at 8:17 PM :

I've been getting a lot of complimentary email regarding my instructions to connect coldfusion with the sql2000 northwinds database. Please, keep the email thanks coming!

I see a few people are still having trouble connecting. The only other instruction I can think of is to make sure that your sql server is up and running. Usually you will see a little server icon with a green (go) dot on it. Thats the service manager. Double click on the icon then click the green start button.

There's so much to learn about sql2000 and coldfusion. If you have questions, feel free to email me at terry@softreq.com

Terry

Gekko2004inUK said on Aug 2, 2004 at 9:00 AM :

Frankly, I think cold fusion sucks when it comes to SQL Server connectivity. I had this thing set up and running fine, but one day, all of a sudden the thing decides it is not in the mood to connect to SQL anymore. Even though I can set up an ODBC connection on the machine with no problems whatsoever
And... yes.... SQL is running, thank you.
And... yes.... I do have SQL Service pack 3 installed....
And yes, there is a connection available from my coldfusion machine to
my SQL server machine.....
And yes, my IIS website is running
And yes to all the other extremely stupid ideas you might think of!

It's NOT WORKING!
Any good ideas why this should be giving me the following error?:
Connection verification failed for data source: BabelDB
[]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

Surprise me, please!

jrunrandy said on Aug 5, 2004 at 4:06 AM :

I asked a QA engineer about the "Connection Refused" error message, and this is what he said:
*******
Two things come immediately to mind:

1. They are running SQLServer on W2K Pro (max 10 connections) instead of on W2K Server, and this request is the 11th TCP/IP connection.

2. They have a connection-limited license for SQLServer - and CF is requesting an additional connection beyond their limit.

Both of these problems can be surmounted by setting the Datasource [Limit Connections] and [Restrict connections to] settings in CF Admin (taking into account any other connections which may be present.
*******

No screen name said on Aug 25, 2004 at 10:00 PM :

In response to jrunrandy . I was having the socket connection problem and this solved it.

Boise Media said on Sep 9, 2004 at 11:44 AM :

Yesterday I consecutively upgraded ColdFusion MX to MX 6.1 and installed Microsoft's new XP Security Patch 3. Since doing that, I can no longer connect to local MS SQL Server databases. All other DB types are working properly and I am able to connect to external MS SQL Databases. Unfortunately, I can't tell which upgrade is causing the problem, but after reading the comments above, my guess is that its MX 6.1. On my localhost I am seeing the following error:

"[Macromedia][SQLServer JDBC Driver]Error establishing socket. Connection refused: connect".

Can anyone help? Jrunrandy's 8/5/04 suggestion above is not clear to me...
Thanks!

Jorge Monardes said on Sep 27, 2004 at 8:38 PM :

I have ColdFusion Enterprise running on Windows Server 2003, and i cant solve the same problem:

[]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

i have all conectivity ok: user, password, iis running, sql running.

Please help me ... i need my application for work.

thank anything that can give a hand.

asaxe said on Oct 2, 2004 at 10:45 PM :

I had the same incredible frustrating problem with "Connection refused" error. I am running SQL Server on the same machine (WinXP, SQL Server 2000) as ColdFusion for development purposes. I would receive the error everytime I tried to veify the datasource.

I however, fixed the problem with a simple adjustment to SQL Server 2000. Step to resolve:

1. In Enterprise Manager, right click on the name of your SQL Server and click Properties.

2. Click Network Configuration...General Tab.

3. There are two select boxes--Disabled Protocols and Enabled Protocols. Move TCP/IP to the Enabled Protocols section and click OK.

4. Restart SQL Server services.

5. Verify your datasource.

It worked for me...hope it works for you.

No screen name said on Oct 5, 2004 at 3:56 AM :

After I upgrade Windows XP from SP2, the ColdFusion cannot to the database of MSSQL Server with the following error:

Connection verification failed for data source: PetMarket
[]java.sql.SQLException: [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]The requested instance is either invalid or not running.

Extrajoss2 said on Oct 5, 2004 at 10:28 PM :

I have a very similar problem to that encountered by No Screen name above.

Connection verification failed for data source: DATASOURCENAME
[]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

I have just installed XP Pro SP2 and upgraded to CFMX6.1 (developer edition) Everything was working fine previous to the SP2 installation.

Any ideas. I have tried all the above with no success thus far.

TAS2573 said on Oct 11, 2004 at 5:00 PM :

I have the same problem was very frustrated because I had to do a demo and my SQL connection was not working.

Anyways I found the fix. You guys will love this. Change the sql port to a different port. I used 8433. After doing this, I was able to connect.

I think the problem is with MS not CF.

BlueSpline said on Oct 13, 2004 at 5:13 AM :

I've spent hours on this. Finally it works!!

My configuration has:
* XP Pro (without SP2)
* CFMX 61. (with the August 04 update)
* SQLServer 2000 (developer edition with SP3)

I think the keys to success were:
* Creating users in SQLServer under Security/Logins
* Using mixed-mode authentication for SQLServer (windows and SQL)

The document at http://www.macromedia.com/support/coldfusion/ts/documents/tn18245.htm
was particularly useful.

Thanks to everyone who has posted thoughts on solving this problem - it's clearly a major issue for many people.

Mike

BlueSpline said on Oct 13, 2004 at 5:42 AM :

One more thing ...

If you have installed SQLServer using a server name other than the default, you must use your chosen domain\servername wherever there's a reference to (local).

Mike

sanju said on Nov 11, 2004 at 7:57 PM :

Connection verification failed for data source: mxlocal
[]java.sql.SQLException: [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]The requested instance is either invalid or not running.

I'm running Win XP (SP2), SQLServer 2K (SP3), Coldfusion MX 6.1 Dev edition.

I have tried everything that was suggested above but have had no luck.

AustinBoy said on Nov 14, 2004 at 12:06 AM :

I have been pulling my hair out for days concerning this issue but, I tried what was suggested by asaxe (see above) and it worked. However, the first time I did the fix i did not take so what i did was to go into the TCP/IP properties and just click on the port dialog box ..then i restarted again and it worked. Who knows? My theory on the cause is that the TCP/IP protocol was disabled by XP SP2...that is for SQL server anyway.

jrunrandy said on Nov 17, 2004 at 6:19 PM :

The only thing I can think of is to make sure that SP2 isn't blocking port 1433, somehow.

Dragonwright said on Nov 17, 2004 at 3:13 PM :

Just upgraded to XP SP2. My error mesage is a little different than other's:
# Connection verification failed for data source: sammco
[]java.sql.SQLException: SQLException occurred in JDBCPool while attempting to connect, please check your username, password, URL, and other connectivity info.
The root cause was that: java.sql.SQLException: SQLException occurred in JDBCPool while attempting to connect, please check your username, password, URL, and other connectivity info.

I have not changed any passwords, and it all worked this am. Ideas expressed above haven't helped.
David

sysam said on Dec 1, 2004 at 5:16 AM :

Has anyone managed to connect to SQL server with winXP SP2 yet.
I am fed up of having to uninstall SP2 in order to get connections working. On a new PC SP2 comes pre-installed and the only way to get rid of it is to install an older copy of XP - that I know of anyway

pallep said on Dec 5, 2004 at 2:01 PM :

XP SP2 enables the firewall.
look at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;841251&product=sql

if you are a non XP user, you coud stille get the problem - i have just been strugling with it for a while, setting up a demo. - it apears that you need to have a working network connection for the tcp/ip to work - even if you connect to 127.0.0.1, ........... try to plug the PC into any lan !!!

regards

No screen name said on Jan 22, 2005 at 12:29 AM :

The problem is Windows XP SP2. The work around is to route your CF Admin Datasource connection through an MS ODBC socket.

I found the solution here:

http://www.macromedia.com/cfusion/webforums/forum/messageview.cfm?catid=6&threadid=934725&enterthread=y

It worked for me!

No screen name said on Feb 1, 2005 at 2:41 PM :

Using cold fusion 4.5, I always used a trusted connection and NT security on my SQL server, but as was pointed out, you need to set SQL server to use both windows and server security, and, most importantly - you need to stop and re-start the SQL server for it to work!

Pete

bert.d said on Feb 9, 2005 at 6:50 AM :

We has a couple of beefy servers which ticked along at 10% CPU, then JRun crashed every hour or so when under load (~500 pages/minute).
Setting Max Pooled Statements to zero completely solved the problem: they now tick along happily at around 10% CPU.
Just thought i'd post that in case anyone else is going mad trying to track unexplained crashes - it was a while before we triedd the above.
2 x CFMX6.1 with updater, SQL server2000 on a separate box, all win2003, dual xeons, loads of ram (~4gb)
Cheers
Bert

mcollins said on Feb 10, 2005 at 8:40 AM :

Please check out http://support.microsoft.com/kb/839269 if you are receiving connection refused after installing sp2.

niyifajemidupe said on Feb 18, 2005 at 7:50 AM :

Well solving the SQL Server connectivity problem with coldfusion could be relatively easier than you think. Go to sql server and make sure you have a dbo access to the table.
Also make sure you are in sql server authentication mode and you are part of the users for that table.
If this does not solve the problem, then
Go to the server (remote desktop if necessary), Go to start>control panel>administrative tools>odbc. Click on the system DSN TAB . Click on a db name, click next, make sure the sql authentication is checked, click client configuration to get the PORT NUMBER on which the SERVER IS RUNNING. The default is 1433 but sometimes security policies may change it to another port number. Get the port number and use the same in cfadmin. Hope this helps.

Kashif Naseer said on Mar 14, 2005 at 3:05 AM :

Hi,
I’m getting the following error:
"
Connection verification failed for data source: testing []java.sql.SQLException:
SQLException occurred in JDBCPool while attempting to connect, please check your username, password, URL, and other connectivity info.The root cause was that: java.sql.SQLException: SQLException occurred in JDBCPool while attempting to connect, please check your username, password, URL, and other connectivity info.
"

here are info. which is being given to the Server:

Database Name:test
User Name: sa [given all rights]
password: password
port 1433
Server: 127.0.0.1 or (local) or charmed [machine Name]

Currently, I’ve even re-installed Cold Fusion Server after re-installing the “Orating System”.

Kindly Guide me how could I get rid from this problem. I’ve been facing it for about a month.

jrunrandy said on Mar 17, 2005 at 12:44 PM :

The ColdFusion MX 7 documentation lists troubleshooting suggestions for this problem. Most of the solutions probably apply to you, too:

http://livedocs.macromedia.com/coldfusion/7/htmldocs/00001743.htm

If that doesn't help, I suggest posting your question to the online forums: http://webforums.macromedia.com/coldfusion

VA_Rob said on Jun 22, 2005 at 9:44 AM :

All-

I tried just about every suggestion on here and many other sites BUT I just stumbled across my solution!!!

Follow this link:
http://www.macromedia.com/cfusion/knowledgebase/index.cfm?id=tn_19518

Worked for me! If you need help, let me know.

Rob

No screen name said on Jun 24, 2005 at 9:01 AM :

The issue connecting to SQL server 2K might be in security settings.
Check your connection to the SQL in your ODBC sources of your Windows 2K or Windows XP located in the Administrator Tools. If you can establish the connection there ( you can test and see if successful), then it would Coldfusion server.
SQL server can have Windows authentication or SQL authentication. If yoau are connecting as a user just created in your database, but the server is configured as Windows authentication (an existing account in Active Directory), you will get the error.
In MX version, I had to go into ODBC sources in Coldfusion administrator and verify them again after the upgrade from Coldfusion 5 and I was getting the same error. I retyped the user name and password, clicked submit and after that it verified the connection. If you are just clicking 'verify' with default password it will give an error, retype the password.
If somebody has a question, please e-mail Olga at olga@winholt.com. I would like see your case scenario.

STeja said on Jul 20, 2005 at 5:56 PM :

I have a 2003 server (SE) with sql 2000 running on it. I had the same "connection refused" error, but I could fix it successfully with the installation of sql server Data Components SP4. If you looked at everything and it seems not to work still, update your sql server instance to the latest sp and I am sure it works.

One word of caution tho, read all the sp documentation carefully, follow the steps exactly as given and you will be able to do a clean install.

jwlewisii, your comments really helped. We have been having this problem for a while now, but thank god it is resolved.

STeja said on Jul 20, 2005 at 5:58 PM :

Here is the link to jwlewisii's comments page

http://livedocs.macromedia.com/coldfusion/6/Administering_ColdFusion_MX/datasources_ADV_MJS8.htm

No screen name said on Jul 24, 2005 at 10:18 AM :

Can not believe that, I just upgrade my ancient CF4.01 to CF7, now I get following msg when verify data connections

Connection verification failed for data source: xcl
java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][SQLServer]Login failed. The maximum simultaneous user count of 20 licenses for this 'Standard Edition' server has been exceeded. Additional licenses should be obtained and installed or you should upgrade to a full version.
The root cause was that: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][SQLServer]Login failed. The maximum simultaneous user count of 20 licenses for this 'Standard Edition' server has been exceeded. Additional licenses should be obtained and installed or you should upgrade to a full version.

jrunrandy said on Jul 25, 2005 at 6:22 AM :

The CFMX 7 documentation describes how to handle this issue:

http://livedocs.macromedia.com/coldfusion/7/htmldocs/00001743.htm

ddrackley said on Feb 15, 2006 at 1:12 PM :

I too had this problem, and the only thing that fixed it for me was changing the port number for my local database instance (in both SQL Server Enterprise Manager and ColdFusion MX Administrator) from the default 1433 to 8433 per a previous suggestion (thanks!). I'm sure that other alternative port numbers may work as well.

sklar said on Apr 18, 2006 at 7:47 AM :

I had the same problems. "Error establishing socket. Connection refused: connect"

I installed SQL Server sp3a (3 patches) and everything worked.

make sure if XP Pro that your firewall is turned off, or properly configured to allow the port.

Adam Reynolds said on Jun 19, 2006 at 3:32 PM :

I would just add that make sure you are on the latest Service pack (4 at the time of writing) for Win 2k SQL server.

No screen name said on Nov 17, 2006 at 7:17 PM :

I have had problems connecting CF 7 to SqlServer 2000 for months and switch to ODBC bridge BUT this cause problems with some sql types.
"The root cause was that: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Error establishing socket. Connection refused: connect"

I tried ritUpdPkg_ENU.exe and it did nothing to help

But then I found a fix - and its easy
In my case it was a prot conflict - I went into SQLServer Enterprise manager and chose the General Tab > Press Network Configuration
Make sure TCP is in the Enabled Box and click it > Then click Properties
-the only option here is Port # I changed my to 8434.
Click Ok > OK & restart SqlServer.

Then set you CF DSN to use port 8434 and I hope it works for you like it did me. IF not then put the port # back to 8433 so you dont get confused later for no reason.

Anthony

jaabi said on May 10, 2007 at 4:18 AM :

On the form where you create a DSN in CFAdmin

If the SQLServer you wish to connect to was installed as a named instance:

A) Use the default port 1433 only if you supply the instance name in the 'server' field. The instance name will look something like this:
COMPUTERNAME\SQLEXPRESS or the like.

OR

B) Use the instructions at http://support.microsoft.com/kb/265808 to locate the port that SQLServer is using only if you want to supply an IP or domain in the 'server' field eg localhost or 192.168.1.45. The port will NOT be the default 1433.

Hope this helps..