How to configure remote access and connect to a remote MS SQL Server instance

Configuring remote access on a SQL Server instance

To enable remote connection on SQL Server right – click on the server and select the Properties option. In the Server Properties dialog under the Connections tab check the Allow remote connections to this server option:

Server Properties dialog - Connections tab - checking the Allow remote connections to this server option

Go to Start->Programs->Microsoft SQL Server 2005/2008/2012 ->Configuration Tools and select the SQL Server Configuration Manager:

Illustration of selecting the SQL Server Configuration Manager

Under the SQL Server Network Configuration select Protocols for <your server name>:

Selecting Protocols for <your server name> under the SQL Server Network Configuration

Make sure that TCP/IP protocol is enabled and right click on TCP/IP and select the Properties option. In the TCP/IP Properties dialog select the IP Addresses tab and scroll down to IPAII. If the TCP Dynamic Ports dialog box contains 0, which indicates that the Database Engine is listening on dynamic ports, delete the 0 and set the TCP Dynamic Ports to blank and TCP Port to 1433. Port 1433 is the default instance that SQL Server uses:

Configuring the IPAII properties via TCP/IP Properties dialog

When you click the OK button you will be prompted with a message to restart the service:

Warning message to restart the service

In the left pane of SQL Server Configuration Manager click SQL Server Services, right-click SQL Server<instance_name>, and click Restart:

Right-clicking SQL Server (instance name), and clicking Restart

Quick tip icon
Quick tip:

If you’re using firewall you need to add an exception for the 1433 port to allow TCP/IP traffic on Port 1433

Configure a Windows Firewall for Database Engine Access

To add a firewall exception for the 1433 port go to Programs -> Administrative Tools select the Windows Firewall with Advanced Security option and follow the steps:

Selecting the Windows Firewall with Advanced Security option

  1. In the Windows Firewall with Advanced Security dialog click on the Inbound Rules option and select the New Rule command:Selecting the New Rule command
  2. In the New Inbound Rule wizard select the Port option and click Next:Selecting the Port option in the New Inbound Rule wizard
  3. In the Protocols and Ports window specify the protocols and ports to which a rule applies. Select the TCP option, in the Specific local ports text box enter the 1433 port, and click Next:Configuring protocols and ports in the New Inbound rule wizard
  4. In the Action window select the Allow the connection to specify the action to be taken when a connection matches the conditions specified in the rule:Selecting the Allow the connection in the Action window
  5. Specify the profiles for which the rule applies in the Profile window, and click Next:New Inbound Rule wizard - Profile dialog
  6. In the last window specify the name of the created rule and click the Finish button:Specifying the name of the created rule

You can now see the created rule in the list of inbound rules:

Viewing the created rule in the list of inbound rules

Source

How to configure remote access and connect to a remote SQL Server instance with ApexSQL tools

 

Author:

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.