Microsoft.NET

……………………………………………….Expertise in .NET Technologies

Configuring Remote Connections for SQL Server Express 2005

Posted by Ravi Varma Thumati on May 6, 2009

This topic explains the trouble-free technique to connect a client system to a server to access the data from the SQL Server Express 2005 database.

 Points to Remember:

SQL Server Express 2005 is a Free Edition, which can be downloaded from http://msdn.microsoft.com/en-us/express/aa718378.aspx. There is some misconception, that since it is free, it cannot be used for multi-user environment. This is not true. SQL Server Express 2005 can be used in Multi-user Environment like SQL Server 2000.

To configure SQL Server Express 2005, for multi-user environment, you are going to do nothing in the Client Systems. To enable remote connections, we are going to work only on the Server. Let me explain, how to configure the SQL Server Express 2005 for Remote Connections.

Step 1: Go to the Server; check SQL Server Express 2005 has been installed properly. If not, download it from the above given link, and follow the steps in the document to install it properly.

Step 2: In Program’s Menu, select Microsoft SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration.

 sqlconfig1 

Step 3: You can see two blue colored links below in the screen appeared above. Choose Surface Area Configuration for Services And Connections.

 sqlconfig2 

Step 4: In the above picture, you can see the Database Engine link on the left-hand side. Expand it, select Local and remote Connections Option. And choose the type of protocol you want to use to establish the connection with the server. Click the Apply button. This will respond you by giving you an alert message shown below. Just click OK on it.

sqlconfig3  

Step 5: Next click on the Service link on the left-hand side menu. It will show you the window given below. For the changes to take effect, you have to restart the SQL Server Services. Click on the Stop button and then Click on the Start button. Wait till the service starts successfully.

sqlconfig4 

That’s it. Now you can try to access the database from the client system. The connection string syntax for SQL Express is given below.

Server=SERVERNAME\SQLEXPRESS; User;

password=SomePassword; Database=YourDBName;

SQL Express 2005 Connection Problems

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. (Provider: Named Pipes Provider, error: 40-Could not open connection to SQL Server)

Mainly these errors are caused when the server firewall prevents the access of SQL Service from the remote or client machines. These problems can be solved when the SQL Service is added to the Exception List of the Windows Firewall Settings. Just follow the steps given below to do it.

Process 1:

Step 1: Open the Control Panel, click on Windows Firewall. Go to the Exceptions Tab. Just scroll down and check whether you can see sqlservr.exe is added in the list and also its checkbox is checked.

sqlconfig5 

Step 2: If not, click Add Programs button, then click the Browse button and locate the sqlservr.exe inside the Program Files > Microsoft SQL Server > MSSQL.1 > MSSQL > BINN > sqlservr.exe. The path might be different depends on the SQL Server 2005 installation. So find the sqlservr.exe and add it in the exceptions list. Then Click OK.

 sqlconfig6 

Process 2:

The second process for the SQLExpress connection problem is to enable the TCP/IP protocol in the SQL Server Configuration Manager > SQL Server 2005 Network Configuration > Protocols for SQLExpress. On the right hand-side pane, right click on your TCP/IP protocol and choose Enable. You have to restart the SQLExpress Service again for this change to take effect.

sqlconfig7 

After you restart the service, again right-click on the TCP/IP protocol and choose properties. Go to IP Addresses Tab and note the IPAll > TCP Dynamic Ports Port Number.

 sqlconfig8 

Now change your Connection string syntax as follows and try to connect to the SQL Server Express.

Server=SERVERNAME\SQLEXPRESS,PortNumber;User

ID=YourUserId;password=SomePassword;Database=YourDBName;

Delete method from Class File

Note: Sometimes using SERVERNAME doesn’t work. So instead of SERVERNAME, you can try by replacing the SERVERNAME with the IP Address of the System where you installed SQL Express 2005. Now you can connect the remote system with the server and access the data from the SQL Server Expres

Advertisements

One Response to “Configuring Remote Connections for SQL Server Express 2005”

  1. HMW said

    Very Helpful and just what I was looking for. Thanks for the breakdown.

    HMW

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: