EasyCFM.COM ColdFusion Forums / MS SQL Server Related Issues / CF8 & MSDE SQL SErver DSN Connection Error Solved!

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: CF8 & MSDE SQL SErver DSN Connection Error Solved!

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

permx11
02-04-2008 @ 12:47 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 1
Joined: Feb 2008

Getting the following error trying to add a CF DSN to SQL Server?

Connection verification failed for data source: datasource_name
java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Error establishing socket to host and port: localhost:1433. Reason: Connection refused: connect
The root cause was that: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Error establishing socket to host and port: localhost:1433. Reason: Connection refused: connect


There are several steps in order to make ColdFusion 8 and MSDE SQL 2005 Server

1.     TCP/IP is not enabled by default. When looking in SQL Server Configuration Manager it appears TCP/IP is enabled by there is a second place that it has to be enabled.
2.     The TCP/IP port is dynamically assigned each time the service is started, if they are no errors it the port number stays the same but if there is an error the port number is dynamically changed the next time the service starts.
3.     You have to add your dsn login username and password to the database in order to connect without using the sa username and password.

Steps to get DSN setup correctly.

1.     Open SQL Server Configuration Manager
2.     Expand the SQL Server 2005  Network Configuration Group
3.     Click on Protocols for SQLEXPRESS
4.     Right click TCP/IP in the right pane and select properties
5.     On the protocol tab under enabled set to yes and apply.
6.     Disable dynamic port assignments by following the instruction below:

If enabled, the default instance of the Microsoft SQL Server Database Engine listens on TCP port 1433. Named instances of the SQL Server Database Engine and SQL Server Compact Edition are configured for dynamic ports, which means they select an available port when the SQL Server service is started. When connecting to a named instance through a firewall, configure the Database Engine to listen on a specific port, so that the appropriate port can be opened in the firewall.

To assign a TCP/IP port number to the SQL Server Database Engine
In SQL Server Configuration Manager, in the console pane, expand SQL Server 2005 Network Configuration, expand Protocols for <instance name>, and then double-click TCP/IP.

In the TCP/IP Properties dialog box, on the IP Addresses tab, several IP addresses appear, in the format IP1, IP2, up to IPAll. One of these are for the IP address of the loopback adapter, 127.0.0.1. Additional IP addresses appear for each IP Address on the computer. Right-click each address, and then click Properties to identify the IP address that you wish to configure.

If the TCP Dynamic Ports dialog box contains 0, indicating the Database Engine is listening on dynamic ports, delete the 0.

In the IPn Properties area box, in the TCP Port box, type the port number you wish this IP address to listen on, and then click OK.

In the console pane, click SQL Server 2005 Services.

In the details pane, right-click SQL Server (<instance name>) and then click restart, to stop and restart SQL Server.

7. Open Microsoft SQL Server Management Studio Express
8. Connect to your local copy of SQL Server
9. Expand the Security group
10. Right click login. Select new login.
12. Enter the dsn login name you want.
11. Choose SQL Server Authetication.
12. Enter the dsn password you want twice.
13. Disable user must change password at next login
14. Choose default database if you like.
15. Click User Mappings in left pane.
16. Click databases you want login to have access to.
17. Click ok.


You should now be able to add your CF DSN using localhost for server and the port number you put in earlier. Default port number is 1433.

You can view which port number SQL is listening to at this registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\SQLEXPRESS\MSSQLServer\SuperSocketNetLib\Tcp
tcpport

You can test connectivity to the sql server by typing telnet localhost 1433 or the port number you selected at a command prompt. You should get a blank screen not an error.

Hope this helps, I spent 3 hours figuring it out.

David


Webmaster
02-04-2008 @ 1:21 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Administrator
Posts: 4542
Joined: Jan 2002

Great post; thanks for thinking of others and making someone's life a bit easier! Keep 'em coming!

Pablo Varando
Senior Application Architect
EasyCFM.COM, LLC.

904.483.1457 \\ mobile
webmaster@easycfm.com \\email

SirRawlins
02-04-2008 @ 5:22 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 951
Joined: Mar 2006

Yeah this is quite a well known issue, I think it only exists with SQL Express edition though, for some reason then ship it with TCP/IP connections diabled, makes life a little harder.

Thanks for taking the time to write this up, it'll be a real help to other people in the same boat down the line I'm sure, and like you say, it can be a real time waster trying to fix it.

Here are a couple of other articles written by ColdFusion guys about the same issue.

http://kb.adobe.com/selfservice/viewContent.do?externalId=kb400255&sliceId=1

http://www.fusioncube.net/?p=59

http://www.digitalmediaminute.com/article/2455/connecting-coldfusion-to-sql-server-2005-express-2

http://www.mattwoodward.com/blog/index.cfm?event=showEntry&entryID=01930619-FD0A-4F02-31B1BFC7D1D9A44C

http://demirkapi.net/avblog/permalinks/2005/11/27/ColdFusion--SQL-Server-2005-Express/

As you've shown in your guide, its actualy a pretty simple problem to solve, its just about knowing what to look for.

Rob


Website Designed and Developed by Pablo Varando.