mssql config error

[jkdfjk]

I've just installed mssql and can't get CF to recognize the database. This is
the error that is thrown:

"Connection verification failed for data source: myDB
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 believe that it's probably permissions. I've tried many combinations of
username/password and server name with no luck. I would really appreciate any
advice.

[mxstu]

What are your datasource settings and can you provide some details about your
enviornment? If you're running a simple single instance development
environment, with CF and ms sql installed on the same machine, your settings
might look something like this:

Datasource Name: myDB
Database: Northwind
Server: 127.0.0.1
UserName: sa
Password: xxxxxxxxxxxx
Port: 1433 (default)

* Note: Typically, you would not use the "sa" login in production

If you're running a firewall, make sure it is not blocking communication with
the database. You might also check out the suggestions in the comments at the
bottom of this livedocs page:
[url]http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/dataso12.htm[/url]

[jkdfjk]

My data source setting are:

Datasource Name: Northwind
Database: Northwind
Server: 127.0.0.1
UserName: Robert
Password: xxxxxxxxxxxx
Port: 1433 (default)

I'm using the my Windows Auth username and password and trying to connect to
the Northwind db. My environment is XP Pro and I'm using MSSQL 2000. I've been
able to verify an ODBC connection through the "Administrative Tools" from the
control panel.

[kim il sung]

i have no experience win auth with ms sql server/coldfusion.
set you ms sql server to mixed auth mode and use sql server logins. it works like no charm.
kim

[jkdfjk]

I will give it a try.

Thank you

[jorgepino]

You could also add the username robert to your SQL user list
don't use you SA or admin as username/password

[jkdfjk]

Ok, nothing seems to be working so I've decided to layout a detail list of
everything so far.

When I try to validate the db connection from the CF Admin I get this error
message:

"Connection verification failed for data source: Northwind
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"

Here is my sequence of events:

1. Install MSSQL 2005
2. Installed MSSQL 2000 once I realized that the db I had to work with was
2000 (Selected "Windows authentification" for login)
3. Uninstalled MSSQL 2005
4. Verified MSSQL 2000 was running and connectivity to the Northwind and Pub
dbs that were place during installation. I return querys from these dbs using
Query Analyzer
5. Establish an ODBC connection to Windows XP through "Administrative Tools"
for Northwind db. I used "Northwind" for the name of the db and "babyruth3" for
the name of the server. For login I selected "Windows NT authentification using
network login ID." The ODBC connection was verified.
6. Verified that the firewall was OFF
7. In the CF Administator, I entered
A. DSN: Northwind
B. Driver: MS SQL Server
C. Database: Northwind
D. Server: babyruth3 (I've also tried "localhost" and 127.0.0.1)
E. Username: Robert (windows login username)
F. Password: xxxxxxx (windows login password)
NOTE: I have tried many variations of username and password including
leaving them both blank. I have update XP with SP2 and updated drivers from
Macromedia's web site. I reinstalled the old drivers when the new ones seem to
cause even more problems. I've tried to change my login authentification to SQL
from the Windows login but an error is thrown when I try to establish the
username and password. Basically it says that there is no user account named
Robert yet I know that it is there, I set it up.

I've spent many hours searching answers for this issue, using trial and error.
Any help would be beyond words (I have now pulled all my hair out and am thing
about my fingernails next.)

[kim il sung]

my small check list

in ms sql server
1) check ms sql server is in mixed auth mode
2) check you are using tcp/ip
3) create login robert in sql server
4) give robert owner rights to nothwind database
5) TEST: check connection querying something from nothwind with queryanalyzer

in system/ODBC connection
1) create odbc source with name nothwind, point to to the sql server/northwind
database
2) TEST: odbc source

in cf admin create odbc source
1) DSN: Northwind (type just like in sql server)
2) Driver: MS SQL Server
3) Database: Northwind
4) Server: 127.0.0.1 (should work just fine)
5) Username: Robert (sql server login username)
6) Password: xxxxxxx (sql server login password)
7) TEST: verify odbc connection

if all thouse 3 tests succeeded, basicly everything is fine for using this
data source.
dont take panic, this can be figured out.
if you succeed first 2 tests, this trouble could be tcp/ip connection,
sockets, etc..

[cfmlguy]

I am having identical problem! Can anybody help us!

Adding insult to injury my ODBC also never works! Windows ODBC checks out ok!
But the ODBC SOCKET option in SQL Server does'nt work and get the message The
ColdFusion MX 7 ODBC Server service is not running or has not been installed -
The windows services clearly state that the services are running! I am puzzled
and confused.

[philh]

If the identical problem includes a SQL instance with Windows auth only, you MUST change it to mixed authentication and use a SQL Server, not a Windows, login to connect to the server.