A

Anonymous

Answered

Can't connect CF 9 to MSSQL 2008 R2

Forum|Forum|15 years ago
April 20, 2011
4 replies
19954 views

I hope some help is out there. I will post the error message at the end of this post. I can't verify a datasource in Administrator. this is the error:

Connection verification failed for data source: DHDataWeb_Dev

java.sql.SQLNonTransientConnectionException: [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.SQLNonTransientConnectionException: [Macromedia][SQLServer JDBC Driver]Error establishing socket to host and port: 127.0.0.1:1433. Reason: Connection refused: connect

Any help would be greatly appreciated. I know this should be very easy, but I can't get it to work. I am running Windows 7 SP1, My SQL database, web server (IIS 7) and CF server are all running on the same box.

Thanks in advance.....

Didger (who is most confused)

Database access

This topic has been closed for replies.

Correct answer kayolhope

If you ignore CFAdmin verification and just try adding the DSN then putting the credentials into a CFQuery, does that work? If not, maybe run SQL Profiler to see what's going on?

Figured it out, issue resolved after working with the previous developers.

Seems the source names were a bit different and the instance name had to be

changed to the ip of the data box the sql server was installed on. I

believe the permissions were fine with the new user setup as were the

previous ones. SQL authentications had to be changed as Windows NT mix mode

authentication wasn't doing it, still not quite sure why though. The port

was fine and the firewall settings were fine. Looks like I had looked for

the datasource names kind of in the wrong place but it was pointed out for

our project.

Thanks everyone for the assistance though.

larrym78096085

Participant

Hey, sorry for the late answer.

All you had to do was to create a new login with the Management Studio for ColdFusion.

see here a good explanation: http://www.fusioncube.net/index.php/coldfusion-sql-server-express

A

Anonymous

Most of the time I have had the issue of not being able to set up a datasource for SQL Server it's because the Security Settings on the SQL Server are set to Window Authentication Mode which allows me access via my netork login. More than a few times I have had to reset it to SQL Server and Windows Authentication Mode, by right clicking on the server name in SQL Server Management Studio and select Properties (bottom of menu) and click on Security in the popup window. At the top is Server Authentication. If it's Windows only, switch it to allow both and restart SQL Server in Services (Control Panel Admin Tools), and your SQL Login Access will be activated. If you are using a restored dateabase from another server, you must set the orphaned logins to the new server master tables USE (your tablename) EXEC sp_change_users_login 'Report' to check for orphaned logins. Use EXEC sp_change_users_login 'Auto_Fix' username to move the authentication to new master tables, then query USER Master SELECT * FROM sys.server_principals WHERE name = yourusername and SELECT * FROM sys.syslogins WHERE name = yourusername to verify your SQL login has been recovered from your database to the master tables in your new location.

K

kayolhope

Participating Frequently

I'm having the exact same issue, does anyone have an answer to this?

Owainnorth

Inspiring

Can you telnet from your CF server to the SQL Server on port 1433?

K

kayolhope

Participating Frequently

D:\>telnet XXX.XXX.XXX.XXX 1433

Connecting To XXX.XXX.XXX.XXX ...Could not open connection to the host, on

port 1433: Connect failed

Nope, but I can get to the CF9 Admin Panel and had enabled TCP within

Windows firewall which wasn't enabled before:

From Documentation Notes:

- After researching changing the the instance name back to the default port

of 1433 for ms sql 2008 r2, had a feeling TCP wasn't turned on as it is not

by default along with not being able to telnet to it prior. Enabled TCP

through remoting into sql box, Start -> All Programs -> Microsoft SQL

Server 2008 R2 -> Configuration Tools -> SQL Server Configuration Manager

then, on folder tree - SQL Server Network Configuration -> Protocols for

SQLEXPRESS (source: https://www.youtube.com/watch?v=KD8PI72MK6w) finally,

restarted server for changes to take effect

- Data Source still given same error and will need to check port being

listen on.

- Next, allowed sql through allows programs of Windows firewall through Dev

Data Server by going to Start -> Control Panel -> System and Security ->

Windows Firewall -> Allow a Program through Windows Firewall. This is to

allow packets to travel across the network without getting blocked. Went

into allow another program and Browsed to and selcted "D:\Program

Files\Mircosoft SQL Server\MSSQL.(name of

instance)\MSSQL\Binn\sqlservr.exe" and added. SQL Browser was already

opened.

- Headed over to MS Windows Firewall Advanced Security and right clicked on

"Inbound Rules", "New Role", choose Port from the radio button, next,

choose TCP as port type and specified local port 1433, next, allow

connection. Checked Domains and private, with Public unchecked to apply to.

Next and names MS SQL Server 2008 R2. With this now linked and server

restarted should connect to ms sql 2008.

- Now getting logon error msg when verifying datasource as follows:

Connection verification failed for data source: DATABASESOURCENAME

java.sql.SQLInvalidAuthorizationSpecException: [SQLServer JDBC

Driver][SQLServer]Login failed for user ' MSSQLBOX2008\Administrator'.

The root cause was that: java.sql.SQLInvalidAuthorizationSpecException:

[SQLServer JDBC Driver][SQLServer]Login failed for user

'MSSQLBOX2008\Administrator'.

- The port is now listening and connected according to port scan

- Not getting any connect refused error anymore when verifying data source

in CF9 Admin

- Not sure if Server is setup to not respond to Telnet requests

- Using MS Server 2008 R2 w/ MS SQL Server 2008 R2 and CF 9 Development

Edition

- Server is connected remote

- Web box uses IIS7

C

carl type3

Legend

Hi didger,

Firewall is not blocking port 1433?

The tcp protocol is enabled in "SQL Server Configuration Manager" > "Network Configuration"?

HTH, Carl.

A

Anonymous

Am hoping someone can help. I just did a clean install of SQL Server 2008 R2 Developer on a Win7 Pro PC. It has been working fine, and I copied my database created in SQL Server 2000 to it, and have accessed it (within SQL Server) with no problems.

I just installed CF9 then the CF9.0.1 update, and had same problem, same error message when trying to set up the database connection in CF Admin. However, I did not have TCP enabled in: "SQL Server Configuration Manager" > "Network Configuration", as suggested by carltype3.

After I enabled that, and re-started SQL Server, I'm getting a different error message, which is (note, replaced actual User name with "xxxx" for security reasons:

"Connection verification failed for data source: SkynetSQL
java.sql.SQLInvalidAuthorizationSpecException: [Macromedia][SQLServer JDBC Driver][SQLServer]Login failed for user 'xxxx'.
The root cause was that: java.sql.SQLInvalidAuthorizationSpecException: [Macromedia][SQLServer JDBC Driver][SQLServer]Login failed for user 'xxxx'."
==============
It would appear I'm now getting through to the server, but it cannot validate my logon? I'm new to SQL Server 2008. I'm using (trying to connect to) a database created in SQL Server 2000. Only 2 accounts show up in SQL 2008, which is using Windows Authentication. My Windows UserID, which is "xxxx" (as shown above), has no password, and is the only account on my Windows 7 Pro PC.

There is also another account showing up, called: "Coldfusion", which I assume was copied from the SQL 2000 version, and was showing up in SQL 2008 before I even installed ColdFusion.

With our other PCs and servers running CF7 and SQL 2000, I use the "sa" account to connect to this database. And on those, SQL 2000 is configured for dual Windows and SQL Server Authentication. But SQL 2008 doesn't use "sa" accounts, from what I can tell.

We will soon be upgrading our production servers to SQL 2008 and CF9, and need to determine how to make this connection work. Appreciate any advice/help. Thank you,

Gary

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded