AspdotnetCodes.com
Search
Articles
Books
Resources
Asp.Net News
Downloads
Free Tech Magazines
Asp.Net Web Hosting
Archives
Softwares
Newsletter
Suggest Us
Link to Us
Feeds Subscription
Articles
Questions & Answers
Tips & Tricks
 Configuring Remote Connections For SQL Server Express 2005
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. NO. 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. I mean DON’T DO ANYTHING IN YOUR CLIENT SIDE. 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, sit in a nice chair and 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 your Program’s Menu, select Microsoft SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration.

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



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.




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.



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 ID=YourUserId;password=SomePassword;Database=YourDBName;
 


SQL Express 2005 Connection Problems



Sometimes when you configure the SQL Express 2005 for remote connections, you get some errors like


An error has occured 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.


WorkAround 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.


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.


WorkAround 2:

The second workaround 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.

  
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.
 


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;
 
FYI: 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 Express 2005.
Post your comments, doubts and questions about this tutorial here.
Sponsored by
Web Application Security — How to Minimize Prevalent Risk of Attacks
Easing the Migration to Microsoft SQL Server 2005
Everything You Know About CSS Is Wrong! - Free 37 Page Preview!