Cry about...
SQL Server Troubleshooting

---

HResult 0x2, Level 16, State 1 Named Pipes Provider: Could not open a connection ...

---

Symptom:

When attempting to connect to a SQL Server 2005 Express database with sqlcmd, such as:

sqlcmd -s <server>\SQLEXPRESS

where '<server>' is the name of the pc/server with SQL Server 2005 Express installed. The following error is generated:

HResult 0x2, Level16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : 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 settings SQL Server does not allow remote connections..
Sqlcmd: Error: Microsoft SQL native Client : Login timeout expired

Cause:

When first installed SQL Server 2005 Express Edition does not allow any connections other than via the Shared Memory protocol on the local machine.

This error can also be caused by firewall settings blocking access to SQL Server.

Possible Remedies:

• Enabled Named Pipes and TCP/IP protocols on the database server.
  1. Start → All Programs → Microsoft SQL Server 2005 → SQL Server Configuration Manager
  2. In the left hand pane, expand "SQL Server Configuration Manager (Local) → SQL Server 2005 Network Configuration"
  3. In the left hand pane, highlight "Protocols for SQLEXPRESS"
  4. In the right hand pane, right click "Named Pipes" and select "Enable"
  5. In the right hand pane, right click "TCP/IP", select "Enable" and then select "Properties"
  6. On the "IP Addresses" tab ensure that "Enabled" is set to "Yes" for each network adapter listed.
  7. Click [OK] to close the TCP/IP Properties dialog.
  8. In the left hand pane select "SQL Server 2005 Services"
  9. Right click "SQL Server (SQL EXPRESS)" and select "Restart"

  Whilst it is not required for this process, it can make the task of configuring remote access to SQL Server Express easier if you also start the process "SQL Server Browser". You may need to open the properties and on the "Service" tab change the Start Mode from Disabled to Automatic, before you can start the process.

• Add SQL Server 2005 Express as an exception to the windows firewall.

  You will need to add SQL Server 2005 Express as an exception to any firewall software that is running locally. The following sequence assumes the Windows XP Firewall:

  1. Start → Control Panel (classic view) → Windows Firewall
  2. On the Exceptions tab, click "Add Program..."
  3. Browse to "sqlserver.exe" and click [OK]. This is normally located in the folder "Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn"
  4. Repeat for "sqlbrowser.exe" if you have set the "SQL Server Browser" service to run. This is normally located in the folder "Program Files\Microsoft SQL Server\90\Shared"
  5. Click [OK] to close the Windows Firewall dialog.

  If you still find that you cannot connect, then try opening TCP Port 1666 in the Windows Firewall:

  1. Start → Control Panel (classic view) → Windows Firewall
  2. On the Exceptions tab, click "Add Port..."
  3. The "Name" can be anything, but I suggest something like "TCP Port 1666 for SQL Server". For the "Port number" enter 1666, and ensure that TCP is selected. Click [OK]
  4. Click [OK] to close the Windows Firewall dialog.

  For those that are interested, the port number 1666 comes from the "TCP Dynamic Ports" displayed on the "IP Addresses" tab of the "TCP/IP Properties" of the TCP/IP Protocol listed by SQL Server Configuration Manager.

  If you get this error when trying to connect using Microsoft SQL Server Management Studio then try opening UDP port 1434.

• Ensure that the SQL Server 2005 Express server process is running.

  Check this by:

  1. Start → Control Panel (classic view) → Administrative Tools → Services
  2. Scroll down and check that "SQL Server (SQLEXPRESS)" has the status of "Started". Start it if it is not already started.

---

These notes have been tested against SQL Server Express 2005 running under Vista and Windows XP.