Monday, November 24, 2008

Connect Remotely to SQL Server Express.

If you are using MS SQL Server 2005 standard edition or enterprise edition this remote connection feature is enabled by default but for SQL Server Express edition by default this feature is disabled , if your application need to connect SQL server express database remotely then you need to enable remote connection manually, here are those 8 simple steps to enable the feature and allow you application to connect remotely

1. Go to start menu, browse to Microsoft SQL Server 2005 – > Configuration Tools –> and open SQL Sever Surface Area Configuration


2. Click on “Surface Area Configuration for Services and connections”




3. Select “Remote Connection” , you can see there the option “Local connection only” selected as default selection.



4. change this to Local and Remote connections.


5. it will show you a message windows indicating that this change  need SQL Server restart. say “OK”



6. now again Browse to main menu-> Microsoft SQL Server 2005 –> Configuration Tools-> and select SQL Server Configuration Manager




7. In SQL Server Configuration Manager Select Protocols for SQLEXPRESS and double click on TCPIP Protocol.


8. Open IP Address Tag in IP One set Enabled  as ‘Yes’, enter IP of you machine in IP1, and select TCP Port as 1433 ( you may use other port, 1433 is default port on which SQL Server communicates") in all IP section you can set same port no as 1433. ( no need to set dynamic port values, leave them with default values)



Click Apply and then ok, you are done with settings, now in SQL SERVR 2005 Services node right click on SQLEXPRESS and say restart to restart SQLEXPRESS Service.

Now from remote machine simply Enter IP address of this machine on which you did above configuration as a Server name, you don't need to specify instance name if it is default instance. Enter SQL Server Login name and password to connect to database.

1 comment:

Michael Amban said...

Mahesh? Am I right? Thanks for the post man. I am looking for this for an hour on how to connect my computer to the network sql server. Thanks