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