Solved

CFMX v 8 to SQL Server 2008 R2

Posted on 2011-03-21
25
712 Views
Last Modified: 2012-06-27
We are connecting from ColdFusion v 8 to SQL Server 2008R2 where tcp/ip has been enabled.  We cannot create the datasource using the Microsoft SQL Server Driver in CF as we receive this error.

Connection verification failed for data source: dbname
java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]No more data available to read.
The root cause was that: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]No more data available to read.
0
Comment
Question by:jlshakal
  • 9
  • 9
  • 4
  • +2
25 Comments
 
LVL 14

Expert Comment

by:RickEpnet
ID: 35184683
You need to make sure there are no firewall rules on either server preventing the connection we use SQL 2008 R2 not problem with CF8
0
 

Author Comment

by:jlshakal
ID: 35184763
We've been able to work around it with the ODBC Socket, however, some of our applications require the SQL Server driver.  Checking firewall rules now.
0
 

Author Comment

by:jlshakal
ID: 35184775
Firewall is OFF on both servers
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 19

Expert Comment

by:Jones911
ID: 35184816
Are you sure your putting in the right IP address of the SQL server?
0
 
LVL 14

Expert Comment

by:RickEpnet
ID: 35184848
You should not need OBDC.

Are you running 64bit on the SQL 2008 R2?

Do you have 32Bit Clients like you see in the screen shot?
3-21-2011-2-55-07-PM.jpg
0
 

Author Comment

by:jlshakal
ID: 35184894
@RickEpnet - Yes 64bit on the SQL Server, and 32bit SQL Native Client just as your graphic indicates.  
0
 
LVL 14

Expert Comment

by:RickEpnet
ID: 35184922
When you say you enable TCP/IP have you enabled it on both standard and 32bit?
0
 

Author Comment

by:jlshakal
ID: 35184966
In the SQL Server Configuration Manager -
SQL Native Client 10.0 Configuration (32bit)
 Client Protocols TCP/IP Enabled
and under
SQL Native Client 10.0 Configuration
 Client Protocols TCP/IP Enabled

0
 
LVL 19

Expert Comment

by:Jones911
ID: 35184972
Are you sure your putting in the right IP address of the SQL server?

Also after enabling TCP/IP have you rebooted?
0
 
LVL 14

Expert Comment

by:RickEpnet
ID: 35184994
Here too? You can go back in and disable any you do not want once you get it working.
3-21-2011-3-18-47-PM.jpg
0
 

Author Comment

by:jlshakal
ID: 35184998
@jones911 - used the name of the sql server, then recreated the datasource using the IP address and got the same results.  The servers have been rebooted serveral times.  We've been live for months, but are running out of options since sql 2005 is desupported next month.
0
 

Author Comment

by:jlshakal
ID: 35185019
@RickEpnet - on those properties only IP1 and IP2 show Enabled.  I went and marked them all YES, but have to wait to restart the services.  
0
 

Author Comment

by:jlshakal
ID: 35185229
@RickEpnet - same error even after changing all IPs to Yes / Enable and restarting the services.
Connection verification failed for data source: nexgen1
java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]No more data available to read.
The root cause was that: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]No more data available to read.

0
 
LVL 19

Expert Comment

by:Jones911
ID: 35185283
Can you ping the server?  Does the DB username exists?  Try a SA user/pass?
0
 
LVL 14

Expert Comment

by:RickEpnet
ID: 35185284
Have you applied all the patches to the CF machine. I am talking about Coldfusion Patches?
0
 

Author Comment

by:jlshakal
ID: 35185319
@Jones911 - the server can be pinged - the DB is valid, I used our SA user/pass and same error.  as noted in prior comment, we are using odbc socket, but appears some of our application require the sql server driver.  that's where we are failing at connectivity.
0
 
LVL 19

Expert Comment

by:Jones911
ID: 35185351
Is it an issue with a names instance of the database?
0
 
LVL 14

Expert Comment

by:RickEpnet
ID: 35185353
I am kind of at a loss. Not sure if the updates will help.
0
 

Author Comment

by:jlshakal
ID: 35185390
the issue is with the default instance - not named instance.  no firewall rules, tcp/ip is enabled, SA accounts can login via odbc, but not w/coldfusion using sql server driver.  very odd.
0
 
LVL 14

Expert Comment

by:RickEpnet
ID: 35185478
I too am using the default instance.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35186441
Shot in the dark, but check *all* of your CF logs. There may contain more detail about the error.

Also, have you tried using a different driver instead, such as the MS Driver or jTDS?
MS Driver: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=99b21b65-e98f-4a61-b811-19912601fdc9&displaylang=en
JTDS: http://jtds.sourceforge.net/ 
0
 
LVL 11

Accepted Solution

by:
Brijesh Chauhan earned 500 total points
ID: 35186871
Check out your windows EVENT logs as well, they might have the reason for the error,

here is a similar issue posted

 http://mayoorathen.blogspot.com/2010/08/coldfusion-sql-server-2005.html...
0
 
LVL 14

Expert Comment

by:RickEpnet
ID: 35191177
You had encryption turned on in the SQL server?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35191260
So .... what was the problem?
0
 
LVL 14

Expert Comment

by:RickEpnet
ID: 35191314
No just unusual. Is you SQL server exposed to the internet.
I am so glad someone thought to post that for you. I am just sorry I did not think of it.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question