Activities

November 2013
M T W T F S S
« Oct   Dec »
 123
45678910
11121314151617
18192021222324
252627282930  

Enable Remote Connections on MS SQL Server

Some of the cases, you may need to enable remote MSSQL connectivity as certain desktop or other web application which hosted on different network want to access a common database. So the best part is, you need to identify the IP or Network range of remote host which you need to allow database access and permit only these IP to have access.

You can done this either of the way shown below,

a. Setting Windows Firewall towards known/trusted IP/network.
b. Permit network access through AWS security IP firewall if you are using AWS Mgmt Console
c. Through a Hardware Firewall.

How To Enable Remote Connections SQL Server 2008 Express Edition

1. Open the SQL Server Configuration Manager and expand SQL Server Network Configuration – Protocols for SQLEXPRESS
2. Choose the protocol TCP/IP and right click on it, then enable.
3. Right click TCP/IP and open Properties
Check IPAll and ensure that Dynamic Ports is blank and make sure that the TCP port is set to 1433

if you want to change the MS SQL port, you need to type the port number on Dynamic Ports

4. Restart the MSSQL service to take effect.

MSSQL default port is 1433

How do I test my custom port is listening

a. You may use telnet for this

[root@rc-040 ~]# telnet 192.168.0.71 8000
Trying 192.168.0.71...
Connected to test.com (192.168.0.71).
Escape character is '^]'.
^]
telnet> quit
Connection closed.

b. You can use Management Studio 2008 to test this,

Pls note the server name will be : ,
See the screen shown below,

MSSQL_remote_connectivity

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>