Hi Guys!
I have installed MS SQL Express on my machine with a Server Administrator ("sa") password. I have also installed ColdFusion MX 7 Developers Edition server on my machine. When I go to the "Data & Services > Datasources" section to the ColdFusion Administrator and try to add a new data source using "Microsoft SQL Server" as the driver, "master" as the database, "127.0.0.1" as the server, "1433" as the port, "sa" as the Username, and the Server Administrator ("sa") password as the password, and click the Submit button, I get this error:
Connection verification failed for data source: master
java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Error establishing socket. Connection refused: connect
The root cause was that: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Error establishing socket. Connection refused: connect
What should my settings be?
Dave
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Side note... depending on what software you have on your system your firewall might be blocking the port as well or perhaps filtering the loopback for some reason. Try using localhost instead of 127.0.0.1 and see if that works for you... or try using the actual physical address of your NIC... (ipconfig will tell you what that is if you are unsure).
Launching SQL Server Configuration Manager and enabling the TCP/IP protocol for SQLEXPRESS and restarting the SQL Server (SQLEXPRESS) has no effect, or gets what looks like the same error:
Connection verification failed for data source: master
java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Error establishing socket. Connection refused: connect
The root cause was that: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Error establishing socket. Connection refused: connect
Renaming the C F Data Source Name to CFDataSourceName has no effect or gets what looks like the same error:
Connection verification failed for data source: CFDataSourceName
java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Error establishing socket. Connection refused: connect
The root cause was that: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Error establishing socket. Connection refused: connect
Changing the Server to localhost has no effect or gets the same error:
Connection verification failed for data source: CFDataSourceName
java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Error establishing socket. Connection refused: connect
The root cause was that: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Error establishing socket. Connection refused: connect
Typing "ipconfig /all" in the Command Prompt and getting:
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\Documents and Settings\Compaq_Owner>ipco
Windows IP Configuration
Host Name . . . . . . . . . . . . : The-Big-Mistake
Primary Dns Suffix . . . . . . . :
Node Type . . . . . . . . . . . . : Hybrid
IP Routing Enabled. . . . . . . . : No
WINS Proxy Enabled. . . . . . . . : No
Ethernet adapter Local Area Connection:
Connection-specific DNS Suffix . : hsd1.ma.comcast.net.
Description . . . . . . . . . . . : Realtek RTL8139/810x Family Fast Eth
ernet NIC
Dhcp Enabled. . . . . . . . . . . : Yes
Autoconfiguration Enabled . . . . : Yes
IP Address. . . . . . . . . . . . : 192.168.1.106
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . : 192.168.1.1
DHCP Server . . . . . . . . . . . : 192.168.1.1
DNS Servers . . . . . . . . . . . : 68.87.71.226
68.87.73.242
Lease Obtained. . . . . . . . . . : Thursday, January 19, 2006 12:14:05
PM
Lease Expires . . . . . . . . . . : Friday, January 20, 2006 12:14:05 PM
And plugging the "192.168.1.106" into the Server input field of the Data & Services > Datasources > Microsoft SQL Server : CFDataSourceName form has no effect or produces the same error:
Connection verification failed for data source: CFDataSourceName
java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Error establishing socket. Connection refused: connect
The root cause was that: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Error establishing socket. Connection refused: connect
Disabling Norton Internet Security and Antivirus auto-protect and resubmitting has no effect are produces the same error:
Connection verification failed for data source: CFDataSourceName
java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Error establishing socket. Connection refused: connect
The root cause was that: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Error establishing socket. Connection refused: connect
Try creating a test database with a test table inside.
Then create a login + user (note those are two different things) in SQL for the new database. Then give it permission to the table.
Try connecting with that.
Often there is difficulty getting permissions correct with the sa account and master database (and since you n ever really want that anyway, why test with that)
Connecting to THE-BIG-MISTAKE\SQLEXPRESS
Cannot connect to THE-BIG-MISTAKE\SQLEXPRESS
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default setting SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error locating Server/Instance specified) (Microsoft SQL Server)
Connecting to THE-BIG-MISTAKE\SQLEXPRESS
Cannot connect to THE-BIG-MISTAKE\SQLEXPRESS
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default setting SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error locating Server/Instance specified) (Microsoft SQL Server)
These are the current connection properties:
Authentication Method: Windows Authentication
User Name: THE-BIG-MISTAKE\Compaq_Own
Database: model
SPID: 52
Network Protocol: SharedMemory
Network Packet Size: 4096
Connection Timeout: 15
Execution Timeout: 0
Encrypted: No
Product Name: Microsoft SQL Server Express Edition
Product Version: 9.0.1399 RTM
Server Name: THE-BIG-MISTAKE\SQLEXPRESS
Instance Name: SQLEXPRESS
Language: English (United States)
Collation: SQL_Latin1_General_CP1_CI_
Computer Name: THE-BIG-MISTAKE
Platform: NT INTEL X86
Operating System: 5.1 (2600)
Processors: 1
Operating System Memory: 1470
Following these directions:
1) Launch the User Accounts Control Panel
2) Click "Create a new account" in the "Pick a task..." section
3) Type in test_login for the new account name and click the "Next >" button
4) Make sure the "Computer administrator" radio box is checked and click the "Create Account" button
5) Click the test_login link in the "or pick an account to change" section
6) Click the "Create a password" link
7) Use test_login as a password and click the "Create Password" button
8) Close the User Accounts Control Panel
9) Launch Microsoft SQL Server Management Studio Express
10) Set the Authentication Method to Windows Authentication
11) Set the Network Protocol to Shared Memory
12) Connect to THE-BIG-MISTAKE\SQLEXPRESS
13) Create a test database called Test_Database
14) Create a new table called Test_Table
15) Bring up the Login - New dialog box by right-clicking Logins in the Security Branch of THE-BIG-MISTAKE\SQLEXPRESS
16) Set the Login name to THE-BIG-MISTAKE\test_login
17) Make sure the Windows Authentication radio box is checked
18) Change the Default database to Test_database
19) Click the OK button
20) Bring up the Database User - New dialog box by right-clicking Users in the Security Branch of Test_Database and selecting the "New User..." menu item
21) Set the User name to test_user
22) Set the Login name to THE-BIG-MISTAKE\test_login
23) In the Securables section, add Test_Table
24) Check all Grants in the Explicit Permissions for dbo.Test_table
25) Click the OK button
26) Launch a web browser to http://127.0.0.1:8500/CFID
27) Log in and navigate to the "Data & Services > Datasources" section
28) Click on the CFDataSourceName link to bring up the edit form
29) Change the Database to Test_Database
30) Change the server to 127.0.0.1
31) Change the Username and Password to test_login
32) Click on the Submit buttons
Get this error:
Connection verification failed for data source: CFDataSourceName
java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Error establishing socket. Connection refused: connect
The root cause was that: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Error establishing socket. Connection refused: connect
Following these directions:
1) Launch the ODBC Data Source Administrator Control Panel
2) Make sure the User DSN tab is clicked
3) Click on the Add... button
4) Select "SQL Server" and click the Finish button
5) Type in test_ODBC for the Name and (local) for the Server
6) Click the "Next >" button
7) Click the "With SQL Server authentication using a login ID and password entered by the user." radio button
8) Check the "Connect to SQL Server to obtain default settings for the additional configuration options." checkbox
9) Type in test_login to the Login ID and Password fields
10) Click the "Next >" button
Gets this error:
Connection failed:
SQLState: '01000'
SQL Server Error: 2
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.
This is very obviously a connectivity problem and not anything with your account (at least it doesn't appear so from the errors... that *might* be a secondary problem). The key is that you are still not getting outside access to your database (SQL is not allowing outside connections).
I found a few tips here: http://blogs.msdn.com/sqle
First, apparently (at least on some, but not on other installations) SQL Express appears to listen on port 1106 NOT 1433 as it should. Apparently this might be due to something holding that port, but at least one person claims that SQL picked the port in error. (Who knows what Microsoft was thinking, but that's EvilEmpireSoftware for ya). So you will want to check your SQL Server log to verify which port it's listening on.
Secondly - [QUOTE]The default install for SQL Express installs Express as a named instance(called SQLExpress), with no network listening and with Windows Authentication support only.[/QUOTE] You can see in your post from above that only 'Shared Memory' is indicated. Unless you didn't provide the output for the other protocols it appears that you are neither running TCP or NP as connectivity options. At the link above there is a detailed walkthrough of testing your connectivity and making sure that you have everything opened properly.
If that fails... try here: http://msdn.microsoft.com/
It would appear that you do not have networking enabled... follow the instructions above (under Networking Support):
[Quote]Use the Surface Area Configuration tool to enable networking and enable and start the SQLBROWSER service.
Use SQL Server Configuration Manager to enable relevant protocols and start SQL Browser. Figure 2 shows the usage of this tool to enable the networking protocols.
Use DISABLENETWORKPROTOCOLS=0 in the setup command line, if you know in advance that networking support is needed.
Use SMO-based scripting to enable the protocols.[/Quote]
Typing this at the command prompt:
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\Documents and Settings\dbabbitt>sqlcmd -S.\sqlexpress
1> Exit
C:\Documents and Settings\dbabbitt>net start sqlbrowser
Gets this error:
System error 1058 has occurred.
The service cannot be started, either because it is disabled or because it has n
o enabled devices associated with it.
C:\Documents and Settings\dbabbitt>
Following these directions:
1) Uninstall Norton Internet Security (what a piece of crap!) and replace it with ZoneAlarm
2) Launch SQL Server Configuration Manager
3) Expand the SQL Server 2005 Network Configuration section
4) Select Protocols for SQLEXPRESS
5) Right-click on TCP/IP and choose Properties
6) Click on the IP Addresses tab
7) Expand the IPAll section
8) Copy the port number in TCP Dynamic Ports (it seams to pick a random number)
9) Go to the "Data & Services > Datasources" section to the ColdFusion Administrator
10) Add a new data source using "Microsoft SQL Server" as the driver, "master" as the database, "127.0.0.1" as the server, the copied port number as the port, "sa" as the Username, and the Server Administrator ("sa") password as the password
11) Click the Submit button
Gets this result:
datasource updated successfully.
Yay! :-)
Congratulations! I haven't worked with SQL2005 yet but from your situation it seems obvious that some of the 'enhancements' that MS has built into the new version are not actual 'improvements'. I'm guessing that by the second odd number service pack they'll have it right. At least that's how their track record has been going in recent years. That dynamic port issue is what I figured was the problem - but who knows why they even left that as a default is anyone's guess. You'd think that trying 1433 first and then prompting the user for a custom port if 1433 is occupied would be a much better solution than merely picking one at random and then forcing the user to parse a log file to figure out what port the install chose is definitely NOT a user-friendly support.
I'm sure your question will be helpful for many other users in the future. Glad I could help you, but you should give yourself a pat on the back for doing all the legwork!
Cheers!
Business Accounts
Answer for Membership
by: digicidalPosted on 2006-01-19 at 10:54:44ID: 15741530
Your problem is most likely either with the name of the database - this causes same error, or because you do not have SQL set up to use TCP/IP yet (I believe that the desktop version just installs named pipes initially) not sure because I've never used the 'Express' version, but those are the only two times I've gotten the error you mentioned is when the port is not available because SQL wasn't using TCP or because I had a typo in the name of my database or an incorrect login.