SQL Server 2005 and SQL Server Express connection and port troubleshooting

Protocol configuration and firewalls play an important role when you are trying to establish a connection to any database server. SQL Server 2005 and SQL Server 2005 Express are no exception. The greater the need to protect databases against attacks, the harder it is to troubleshoot failing connection attempts.

Firewalls

The first thing that can block a connection to SQL Server is a firewall. If you have any firewalls, make sure they are configured to allow connections to SQL Server. The default TCP port that SQL Server uses is 1433. Firewalls include McAfee, Norton, Windows Firewall which ships with Windows XP SP2, and Internet Connection Firewall (ICF) which ships with Windows 2000.

Service verification

Before you can connect to SQL Server 2005, you need to verify that SQL Server is running. By default, the SQL Server Express edition is installed as a named instance (SQLEXPRESS). This means that you need to access it by using (local)\SQLEXPRESS from the local machine. SQLEXPRESS without the prefix will not work. You can also use the 127.0.0.1 IP address on a local machine to avoided DNS related problems.

To verify that the service is running, type sqlcmd –S(local)\SQLEXPRESS at the command prompt. If you see "1>" that means that you managed to connect. Type exit to exit the sqlcmd program.

Connection protocols

SQL Server 2005 supports a number of protocols for connecting client applications with the database server. These protocols are TCP, Named Pipes (NP), Shared Memory (SM), VIA, and HTTP. Only TCP, NP, and SM are supported in SQL Server Express.

By default, only SM is accessible for SQL Server Express on the local machine. This means that connections from a remote machine to SQL Server Express will fail unless TCP and/or NP is enabled. If you want to use TCP and NP, you must explicitly turn them on. Use SQL Server Configuration Manager to explicitly enable TCP and NP for server and client. After enabling TCP and NP, you need to start the SQL Browser service (See SQL Browser below).

If you are connecting remotely, you need to substitute "(local)" with the IP address of the server. You can also use the server name instead of the IP address if DNS can resolve the name.

If you are connecting via a specific IP address, make sure you enable the connection for it. In SQL Configuration Manager, expand the SQL Server 2005 Network Configuration node then select TCP/IP Properties from the pane on the right. Select the IP Addresses tab and change Enabled to Yes for the specific IP address.

SQL Server Configuration Manager

The SQL Server Configuration Manager in SQL Server 2005 and SQL Server Express replaces both Client Network Utility and the Server Network Utility. It allows you to configure the protocols that SQL Server listens to as well as the protocols that ADO.NET 2.0 application can use. However, to configure client protocol for applications that use ADO instead of ADO.NET 2.0, you still need to use the Client Network Utility. The Client Network Utility ships with ADO and is part of Windows 2000, Windows XP, and Windows 2003.

To connect to SQL Server Express remotely, make sure that the server can listen to TCP connections. From the SQL Server Configuration Manager, expand "SQL Server 2005 Network Configuration" and navigate to "Protocols for SQL Server Express" then enable TCP. You need to restart the server for the change to take effect.

SQL Server Browser

SQL Browser is a new service in SQL Server 2005 that is used to identify the ports that named instances listen on. The SM protocol does not use this service. This service is turned off in SQL Server Express by default. This means that the user will have to start this service so that remote access can work. You can start the SQL Browser service from the Service Manager or by typing "NET START SQLBROWSER" from the command line.

SQL Browser listens on UDP port 1434. However, pre-SP3 versions of SQL Server 2000 holding port UDP 1434 may result in failure of SQL Browser name resolution, since they may refuse to give up the port. The workaround is to upgrade all SQL Server 2000/MSDE instances on the machine to SP3 or higher.