Remote connect to SQL Server 2005 Express or SQL Server 2005

2010-09-01

Microsoft SQL Server 2005 or Microsoft SQL Server 2005 Express do not allow remote access by default. If you want to remote connect or use another computer in your LAN, you have to configure on your SQL Server 2005 or SQL Server 2005 Express.

In this article, we will use SQL Server 2005 Express as the sample database.

If you don't set something for remote connection, you may receive an error message. The following error message is a sampe:

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.

Enable remote connections for SQL Server 2005 Express or SQL Server 2005 Developer Edition

First, I suggest that you use SQL SQL Server** Authentication connection but not Windows Authentication connection (you still can) **for your SQL Server Database connection. If you use Windows authentication, you should have identical accounts on both server PC and remote PC (username and password are the same), This is not good for remote database connection. Don't you think so ?

To enable remote connections on the instance of SQL Server 2005 Express, please read the following steps:

  1. Find your Microsoft SQL Server 2005 program on the Start menu in your Windows XP or related OS, point to Configuration Tools, and then you will see the menu item SQL Server Surface Area Configuration (please see below):
  1. Click **SQL Server Surface Area Configuration, **then click Surface Area Configuration for Services and Connections.
  1. On the new page: Surface Area Configuration for Services and Connections page,select the database which you want to set for remote connection, in our case, we have installed 2 SQL Server Express databases, we select SQLEXPRESS2005 database, then expand Database Engine, click Remote Connections, check on the **Local and remote connections **radio button, then select the appropriate protocol to enable for your environment, and then click Apply.

  1. Please Click OK if you receive the following message:

  2. Return to the Surface Area Configuration for Services and Connections page, expand Database Engine, select Service, On the right side, click Stop button, then wait until the MSSQLSERVER (MSSQL$SQLEXPRESS2005) service stops, and then click **Start **button to restart the MSSQLSERVER (MSSQL$SQLEXPRESS2005) service.

(part of above text were from Microsoft website)

Other instruction info:

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.

(Above text copied from here Teratrax site, more info please click to visit )