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)
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
...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.
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
Copy link to clipboard
Copied
I'm having the exact same issue, does anyone have an answer to this?
Copy link to clipboard
Copied
Can you telnet from your CF server to the SQL Server on port 1433?
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
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.
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.
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
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.
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;)
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?
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?
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:
writeDump(server);
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'.
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.
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.
Copy link to clipboard
Copied
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