MS-SQL Connection fail

[rr008]

I am using a PC with Windows XP, ColdFusion MX 6.1, MySQL 4.1 Server,
DreamWeaver MX installed.
Also works with a PC with Windows XP, ColdFusion MX 7.1, MySQL 4.1 Server, DW
8.
The MySQL Database is now recognized as a Data Source in CF MX 6.1 or CF MX 7x.
1. Downloaded the mysql-connector-java-3.0.17- version of MySQL Connector/J
from the MySQL download site.
Extracted the JDBC driver JAR file: mysql-connector-java-3.0.17-ga-bin.jar and
copied it to the C:\CFusionMX\wwwroot\WEB-INF\lib folder.
2. In the ColdFusion Administrator, clicked on the "Java and JVM" page.
Entered the full path to the JAR file in the Class Path field:
C:\CFusionMX\wwwroot\WEB-INF\lib\mysql-connector-java-3.0.17-ga-bin.jar,
Note: It says to use a comma (,) to separate class paths when this field
contains multiple values. (Cold Fusion adds a comma at the end in CF MX 6.1.
3. Then I used the ColdFusion Administrator Data Sources page to add a new
data source for the MySQL Connector:
I named the data source MyDS and chose 'Other' for the Driver before pressing
the Add button. Then I set the following parameters:
JDBC URL: jdbc:mysql://localhost:3306/mydatabase
Driver Class: com.mysql.jdbc.Driver
Driver Name: MySQL Connector/J
Username: myuname
Password:
Note: I previously installed MYSQL 4.1 Server. I used MySQL Administrator to
set up a user named myuname with all access to the MySQL mydatabase located in
C:\Program Files\MySQL\MySQL Server 4.1\data folder and tested it by logging in
with MySQL Browser and executing a select command.
The Username and Password I put in above is for the MySQL username and
password that I created in MySQL Administrator.
I clicked Submit to apply the changes:
It now verifies the Data Source for MySQL database.

[dwright65]

I am experiencing this exact same issue. In my case, the database resides on
the same machine as Coldfusion. It's a MS SQL Server Desktop Edition DB,
running on XP pro. Any help will be greatly appreciated.

[speedpedal]

For the folks that uses MS-SQL Server (2000):

What type of authentication do you have for SQL Server? Windows or SQL Server
Authentication?

For CFMX 6.1 & 7.0, it is recommended to upgrade your DataDirect Drivers. See
this technote:

Updated DataDirect JDBC drivers (version 3.5)
[url]http://www.macromedia.com/go/42dcb10a[/url]

If your are using SQL authentication, just folow links when creating a new
datasource with SQL Server. If you are using windows authentication, follow
these instructions:


--------------------------------------------------------------------------------
----------
SQL Server NT authentication users (Windows Authentication)

These JDBC drivers support SQL Server NT authentication, designated as "type
2". Macromedia expects this feature will replace the less robust
ODBC/Sequelink/ODBC Socket Server solution for many users requiring SQL Server
NT Authentication on Windows platforms

Use of the JDBC NT authentication feature requires a shareable library,
DDJDBCAuth.dll. Place this DLL in the same directory as macromedia_drivers.jar.
A server restart is required.

To use NT authentication, leave both the username and the password blank, in
both the data source and in cfquery.

When ColdFusion or JRun is started from the command prompt, the login
credentials for the command prompt user are used. If ColdFusion or JRun is
started as a Windows service, the credentials of the Windows service will be
used for authentication. It is not possible to specify a Windows account at the
time of the query.

To enable NT Authentication, add the following to the JDBC URL:

AuthenticationMethod=Type2

For ColdFusion MX 7, AuthenticationMethod=Type2 can be added to the URL in the
Advanced Settings > Connection String box.

For ColdFusion MX 6.1, create an "Other" data source with the following
parameters (example):

URL: (enter as one line)
jdbc:macromedia:sqlserver://:1433;databaseName=pubs;
SelectMethod=direct;sendStringParametersAsUnicode= false;
MaxPooledStatements=0;AuthenticationMethod=Type2;

Driver Class:
macromedia.jdbc.MacromediaDriver

For queries that do not require NT Authentication, supply a username for the
query. If a username is specified, SQLServer username/password authentication
is used. SQL Server should be configured for mixed authentication in this case.


Hope this helps.

[dwright65]

I updated the dirvers jar file and restarted the service. I'm using SQL server
authentication, but it still doesn't work. I now get a slightly modified
version of the earlier error. I suspect that it's just updated language in the
new jar file and that the error is the same thing.

"Connection verification failed for data source: FDP_dev
java.sql.SQLException: Error establishing socket to host and port:
M-XP207020:1433. Reason: Connection refused: connect"

I'm able to connect the the database through the DB admin tool that I'm using
as well as through MS Access, so I know the DB is up an running fine. What are
the issues with running the DB on the same server as the ColdFusion application
server?

Thanks

David

[Toannn]

I met the same problem with Mandriva 2006, Coldfusion 7.01, MySQL. After a
while searching on the local file system, I found a file named 'mysqld' in the
folder '/etc/sysconfig'. I edited the file, remove the text
'--skip-networking', restart mysql, then chek the data source again in
Coldfusion and it works!

Hope that it work for you too!

Toan

[drforbin1970]

Every time I have a problem connecting to a SQL Server DB via CF Admin, I do
the following:

In Windows, Control Panel, Administrative Tools, Data Sources(ODBC), set up a
User DSN and a System DSN to the SQL Server DB. USE THE SAME NAME FOR BOTH. If
you are using the local server on the machine, type in (local) as the server
for the select box 'Which SQL Server do you want to connect to?' Make sure the
connection tests OK at the end of each setup.

Go into CF Admin under Data Sources and Add a new datasource but DO NOT pick
SQL Server, use ODBC. Select the ODBC DSN(the entry you added should be in the
dropdown list). Click 'Trusted Connection'. Leave username set to system .
Click Submit and you should be connected.

If this does not work, you may have to add a user to the Users section of the
database with sufficient access. You will have to add the user under Control
Panel/User Groups also.

Then go back into CF Admin and replace username with the username of the new
user you added and the password you assigned this user under Control Panel/User
Groups

[dwright65]

I did some more investigating and found the cause of the problem was that SQL
Server hadn't enabled TCP/IP. I followed the following documentation and things
were fixed right away. Since I am using the Desktop Edition of SQL Sever I had
to use the server network utility (Svrnetcn.exe) instead of SQL Server
Enterprise Manager to enable TCP/IP.

SQL Server does not enable the TCP/IP protocol. This problem can happen when
SQL Server is on the same computer as ColdFusion MX. To fix this problem,
perform the following steps:

1. In SQL Server Enterprise Manager, right-click on the name of your SQL
Server and click Properties.
2. Click Network Configuration and the General Tab.
3. Move TCP/IP from the Disabled Protocols section to the Enabled Protocols
section.
4. Click OK.
5. Restart the SQL Server services.
6. Verify your data source.

[I'mAndy]

I'm getting the same connect error/problem with cf mx7 and sql server 2005
express running on same machine
the sql server configuration manager shows that tcp/ip is enabled and default
port is 1433
which is what I use in trying to set up the DSN

Any suggestions

[JMGibson3]

Are all 3 set to 1433?. For me, the IPALL value was dynamic and a random value. IPALL was the one that was killing me. Once I changed its dynamic to blank and put 1433 in the static port it worked.

[I'mAndy]

Thanks for that
The dynamic entry shown in ipall matched with the server log and worked when i used it
for the DSN but I took your advice and switched to 1433 static
Now working, touch wood

[RobScio]

Thans for this post. Most helpful. I racked my brain on this over the weekend to find that I missed the classpath step. D'oh!