It took me one day in troubleshooting “error: 40 – Could not open a connection to SQL Server” error whenever I attempt to connect to Management Studio Express (MSE) or sqlcmd. I tried the solution suggested on Internet and eventually landed on the following solution:

1. Go to “SQL Server Configuration Manager”
2. Go to “SQL Server 2005 Network Configuration” -> “Protocol for SQLEXPRESS”.
3. Right-click on “TCP/IP” and select “Enable” if it’s disabled.
4. Right-click on “TCP/IP” again and choose “Properties”
5. Go to “IP Address” tab.
6. Under “IP1″, change “Enabled” to “Yes”.
7. Erase both “TCP Dynamic Ports” content.
8. Set both “TCP Port” to 1433. (1433 is the default TCP/IP port)
9. Click OK
10.Select “SQL Server 2005 Services” from the left frame
11.Right-click on “SQL Server (SQLEXPRESS)” and select “Restart”
12.Try to reconnect to SQL Server using MSE (type in 127.0.0.1 and authentication details). It should be working fine by now.

Related posts:

1. MS SQL Server 2005 Express Edition Adventure (2)
2. There is not FOR loop in SQL Server
3. Server Application Unavailable