• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Can't connect CF 9 to MSSQL 2008 R2

Guest
Apr 20, 2011 Apr 20, 2011

Copy link to clipboard

Copied

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)

TOPICS
Database access

Views

19.2K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Community Beginner , May 21, 2012 May 21, 2012

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 loo

...

Votes

Translate

Translate
Guide ,
Apr 20, 2011 Apr 20, 2011

Copy link to clipboard

Copied

Hi didger,

Firewall is not blocking port 1433?

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

HTH, Carl.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Apr 22, 2011 Apr 22, 2011

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
May 10, 2012 May 10, 2012

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
May 10, 2012 May 10, 2012

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
May 10, 2012 May 10, 2012

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
May 10, 2012 May 10, 2012

Copy link to clipboard

Copied

Thanks to the awful formatting on these forums it's hard to tell which parts of that are your question, but there's a bit I think was yours saying "login failed for user" - isn't that just a credentials issue? You shouldn't be connecting to SQL Server as an administrator from CF.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
May 10, 2012 May 10, 2012

Copy link to clipboard

Copied

I believe SQL Server and Windows Authentication mode is setup to allow

proper authentication credentials for our development environment. We have

a "Web Box" running CF9 Developer Edition and Windows Server 2008 R2 with

IIS7 along with a "Data Box" that communicates with it running MS SQL

Server 2008 R2.

My client computer is setup with a remote connection to both boxes with

admin permissions.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
May 10, 2012 May 10, 2012

Copy link to clipboard

Copied

I believe  SQL Server and Windows Authentication mode is setup to allow proper authentication credentials for our development environment. We have

Apparently not

For that to work, ColdFusion must also be running as the computer administrator (again, a bad idea) for it to connect. I'd try creating a proper login and user within SQL Server with only the permissions it needs over the one database, and use those credentials in CFAdmin to connect.

None of this "connect as administrator" nonsense

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
May 10, 2012 May 10, 2012

Copy link to clipboard

Copied

Yeah, tried that earlier too with a User named "Developer" and got the same

error message about it not recognizing login.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
May 10, 2012 May 10, 2012

Copy link to clipboard

Copied

So basically, the CF website is given to us to take over and I'm to setup 3

different environments as follows:

Sandbox: A localhost CF9 test environment for training and playing in -

setup without a problem

Pre Production - The in house environment currently being worked on to

mirror the Post Production/Live Website

Post Production/Live Website: The 3rd party hosted provider setup and

working

We can see the restored database on our network and all the tables & rows

etc but unable to verify the data source in CF9 Admin Portal.

OMG this is getting frustrating;)

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
May 10, 2012 May 10, 2012

Copy link to clipboard

Copied

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?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
May 11, 2012 May 11, 2012

Copy link to clipboard

Copied

Ah? Okay since I have a simple index.cfm set in the root of my ColdFusion 9

Server to display an output of server and browser info via a cfscript as

follows:

I've also run the SQL Profiler and saved the file although not

seeing/understanding anything useful in it.

So, I remote back into the DATABOX today and open up the ODBC Data Source

Administrator and notice User Data Sources not filled in and think well, if

the source isn't defined here, how can it verify in CF9? I go through

adding it so it shows up in the list and tests successfully when

authenticity of login ID is set to use Windows NT authentication using the

network login ID.

However, when I check With SQL Server authentication using a login ID and

password entered by the user.

Along with Connect to the SQL Server to obtain default settings for the

additional configuration options:

Login ID: XXXXX

Password: XXXXX

I always get the error:

Microsoft SQL Server Login

Connection failed:

SQLState: '28000'

SQL Server Error: 18456

[ODBC SQL Server Driver][SQL Server]Login failed for user

'Developer'

Re-added data source back into cf admin and still won't verify

I thought it best to reset the password to be sure the right combo was

being used so I opened MS SQL Server Manager Studio, connected to database

-> Security -> Logins -> (found the user account) right clicked ->

properties and changed password and restarted server.

Re-added data source back into cf admin after server reboot and still won't

verify.

Port was confirmed this morning to be using the default 1433 and dynamic

port blanked to make sure 1433 for TCP is the only one it can come through.

Where the website and database have be given to us to take over is it

possible there is some kind of credentials I'm missing from the previous

developers or just something I'm doing wrong?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
May 11, 2012 May 11, 2012

Copy link to clipboard

Copied

Aww, looks like the output didn't display on the forum, I'll attempt

to re-post it as it most likely tried to be executed as a command:

cfscript[closetag]

writeDump(server);

cfscript[closetag]

CFQUERY NAME="myQueryName" DATASOURCE="OURDATASOURCE"

USERNAME="DATABOXNAME\Developer" PASSWORD="null"[openetag]/CFQUERY[closetag]

The web site you are accessing has experienced an unexpected

error.

Please Contact your Administrator.

The following information is meant for the website developer for debugging

purposes.

Error Occurred while Processing Request

Error Executing Database Query.

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

"DATABOXNAME\Developer'.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
May 21, 2012 May 21, 2012

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Aug 10, 2012 Aug 10, 2012

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Aug 17, 2015 Aug 17, 2015

Copy link to clipboard

Copied

LATEST

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation