Connection to SQL Server 2005 Express?

Hi all, I've been reading other posts for awhile now and I can't figure out what I'm doing wrong.
I'm a Web Developer and not a DBA. I have SQL Server 2005 Express installed on my machine and it's working just fine. I also have the Management Studio Express CTP installed and working.
I'm trying to connect to a database using a username and password (from ColdFusion Server which is working fine for external Oracle and Access databases, so that's not an issue), so I set up the server to allow mixed mode authentication. I can log in to the Management Studio using Windows authentication OR the username and password without any issues.
I have TCP/IP enabled in the Configuration Manager and 127.0.0.1 enabled.
This seems like a ridiculous question, but I need to know the servername, port, username and password to connect to the database. I'm trying all combinations of things but nothing's working.
If I put in 127.0.0.1 as the server, 1433 as the port, and use the username and password that worked in Management Studio I get this:
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
If I use MACHINENAME\SQLEXPRESS I get this:
java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][Macromedia][SQLServer JDBC Driver]The requested instance is either invalid or not running.

The root cause was that: java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][Macromedia][SQLServer JDBC Driver]The requested instance is either invalid or not running.
That makes me think I'm on the right track with 127.0.0.1 but what should I do at this point?
Edit: I have also tried to connect to my local SQL Server Express via the Migration Assistant. If I use 127.0.0.1 I get this:
Connection to SQL Server failed.

Please make sure that TCP/IP protocl is enabled on your target server and its TCP port is not blocked by a firewall.

[Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket.
I'm really at a loss and I wish I knew more about this. Thanks for any advice!Try one of two things:

(1) Instead of port 1433 specify the port obtained from SQL Server Configuration Manager: SQL Server 2005 Network Configuration -> Protocols fro SQLEXPRESS -> TCP/IP -> Properties -> IP Addresses -> IP All -> TCP Dynamic Ports.

(2) Start the SQL Browser service, and specify the server as MACHINENAME\SQLEXPRESS.|||Were you able to find a solution to this issue? I'm having the same problem.

Rob|||No, unfortunately I still can't get it to work. I've moved to another testing environment with SQL Server 2000 and that's working fine. If you find a way to get this to work I'm very interested!|||Peter is the man! First off, in SQL Server Config Manager, under Network Config. Found that TCP/IP was Disabled. Once enabled, I had to restart my instance, then got the TCP Dynamic Port - plugged that into Cold Fusion Administrator and BOOYAH!

No need to muck with the server as MACHINENAME\SQLEXPRESS, I left it blank (default to 127.0.0.1).

Thanks!|||Is the port being blocked by Firewall. If yes, then the port needs to be opened explicitly.|||I am able to connect to my sqlexpress instance from a machine with sqlserver 2003 but not from a similar machine with sqlserver 2000. I have done the stuff listed above and still cant get an udl to work. There are no intervenig firewalls as the non-connecting machines are on the same subnet. Anybody got another guess.|||

I had the same problem, and yes it is now working.

If you are using SQL EXPRESS 2005 here it is:

Open SQL Serve Configuration Manager

under SQL Server 2005 Network configuration, click on Protocole for SQLEXPRESS, then TCP/IP, right-click on it and go in properties. Select the IP adresses Tab, at the bottom you gonna see IPALL - check what is your TCP DYNAMIC PORTS, For me the port number is 2711.

In CF Administrator I have put server 127.0.0.1 and the port 2711 and everything work fine.

Hope this help

|||

The solutions here worked for me.

Just be sure you have looked at webacadie's post above. Also be sure you have the sqlserver browser service and the database service both running... My problem was I didn't have the browser service running.

|||I still can't get this thing to connect.
I went through all the suggestions in this forum and still I cannot connect with SQLEXRESS, so far this db has been the most difficult to connect to that I have encountered.|||

Hi les_ihs,

I'm sorry that you're having so much problems getting connections to work. Perhaps this information contained within this blog posting may help: http://blogs.msdn.com/sql_protocols/archive/2005/11/14/492616.aspx.

Thanks,

Il-Sung.

|||I had the exact same problem. The confusing part for me was the fact that it says "SQLServer 2000 JDBC driver" while I was using SQL Server 2005.

In the end all I had to do was Start the SQL Server Browser service. (which required me to first change its start mode...). A few hours of my time wasted trying to solve this problem...could have all been avoided if the SQL Server Migration Assistant I was trying to connect with had decent documentation.