Tuesday, October 26, 2010

Help SQL Server to identify each other across internet

Recently I bought a new laptop and had SQL Server 2008 installed on that. The old one carried SQL Server 2005 Express edition.

Thought why not try to identify each of these SQL Server instances  when both of them are connected to internet or try registering SQL Server express edition instance in the laptop that carry SQL Server 2008.

The first step that you need to do is make sure that the instance which you want to register runs in a static port. There are two types of ports which can be used by SQL Server (Static and Dynamic).Static ports once set are not changed when SQL Server is restarted. Dynamic ports are changed every time SQL Server restarts.

To set a static port Open SQL Server Configuration Manager ->SQL Server Network Configuration->Select the Protocols for your instance->Select TCP/IP on the right side and open its properties.Go to IP Addresses tab, scroll down and check if under IP All category you can find TCP Dynamic Ports and TCP Port.Make sure that you don't have any value in TCP Dynamic Ports and enter a value for TCP Port (say 2315). Now restart SQL Server instance. SQL Server instance would use run on this port and it woudn't change regardless the number of time SQL instance gets restarted.

The second step is to make sure that an exception is added in firewall settings for the port underwhich SQL Server runs.In our case it would be 2315.

To add exception you need to go to run and execute firewall.cpl. This will open the firewall window using which you can set exception.Click on allow a program through firewall.Go to the exception tab and click on add port.A new window will pop up. Just enter the Port Number and associate it with a name.Click on OK and then apply.Thats all you are all set to access this SQL Server from other computer in internet.

 To verify go to the other computer and open SQL Server Management studio.Try registering the SQL Server for which you have added exception by  giving IP address and port number.For example if the IP of SQL Server is and port number was 2315 then try accessing it  as,2315.If you want to hide IP and
access SQL Server instance with name please add an alias in Client Configuration Utility. 

No comments:

Post a Comment