SQL Server Enable Remote Connections

When we connect SQL instance from remote a computer is called SQL Remote Connection. This blog describes how to make the SQL instance available for a remote computer.

SQL Server Enable Remote Connections

Getting Started

By default the SQL instance is not available for a remote computer, the SQl Server Management Studio makes the SQL instance disabled when you installs it. justify

Three main reasons are there which blocks us to access SQL instance from remote computer. The first reason is when we install SQL server, the TCP protocol of SQl server is disabled by default. SQL server uses TCP protocol for communicating between two remote SQL instance, to make SQL instance available for remote computer we need enable TCP protocol first.

The SQL server is available default on the port 1433, you can change it while installing SQL instance or later. The second reason of not getting access of SQl instance from remote computer is that the fire wall blocks this SQL port, when we trying to access SQL instance until we add rules in Firewall of local computer for same port.

The third reason is the Remote access for SQL instance is also disabled by default by SQL server . The remote access option controls the execution of stored procedures from local or remote servers on which instances of SQL Server are running. This grants permission to run local stored procedures from remote servers or remote stored procedures from the local server. This default value for this option is 0 means disabled for accessing SQL instance from remote computer. To allow local stored procedures from being run from a remote server or remote stored procedures from being run on the local server, set the option to 1.

Above are the reasons exist behind and because of the reasons we are not getting access of SQL instance from remote computer. Now we will discuss how to enable SQL instance for remote computer.

Allow Remote Access To SQL Server Ports

We discussed about the SQL protocol (TCP) in the above discussion , so first we will enable the protocol . The TCP protocol can be enable using SQL Server Configuration manager, follow the below steps to enable TCP protocol.

Enabling TCP/IP Protocol

1. Open the SQL Server Configuration Manager in the Configuration Tools folder under the Microsoft SQL Server folder.(Go to Start->Programs->Microsoft SQL Server->Configuration Tools and select the SQL Server Configuration Manager.)
2. From the SQL Server Network Configuration node, select the Protocols item for the appropriate SQL Server. In the details pane, right click the TCP/IP item and select the Enable option.
   
3. After this step the Warning box will pop up in which informs us that changes that are made won’t take effect until the next time service is started, But don’t restart the server as we have two more configurations are pending.
   
   
   

We successfully enabled the TCP protocol fore remote access of SQL instance. Now we will open the SQL port in firewall for access from remote computer as the firewall blocks the port. To open the SQL port for public access or remote access we need to add rules in firewall, there are two types of rule fire wall supports i.e Inboug rule and Outbound rule

The Inbound Rules allow or block traffic attempts to access the computer that matches certain criteria in the rule. By default inbound traffic is blocked, to allow traffic to access computer, you need to create inbound rule and the Outbound Rules allow or block traffic originating from specifying computer creating rules. By default outbound traffic is allowed, so you need to create the rule that block outbound traffic.

Allow Remote Access To SQL Server Ports through Windows Firewall

1. Open Windows Firewall using Control panel and click the Advanced settings or just type wf.msc in Search program and files from the Start menu.
   
2. In the Windows Firewall with Advanced Security, click the Inbound Rules from the left pane, right click Inbound Rules and select the New Rule or from the Actions pane click the New Rule
   
   
   
3. In the New Inbound Rule wizard select the Port option and click Next.
   
   
   
4. Select the TCP option, in the Specific local ports text box enter the 1433 port(if you mentions other number for TCP port enter in the textbox), and click Next.
   
   
   
5. In the Action window,you will find the Allow the connection options by default is selected. Do not change it and click on next button.
   
   
   
6. Let keep the options of profile window as it is and click on next button.
   
   
   
7. In the last window specify the name of the created rule and click the Finish button.
   
8. Now we have successfully created new Inbound Rule for the SQL port, Click on the Outbound Rules and follow the steps mentioned for Inbound Rule.

Allow Remote Access To SQL Server Instance

This topic describes how to configure the remote access server configuration option in SQL Server by using SQL Server Management Studio or Transact-SQL.

Using SQL Server Management Studio

1. Open SQL Server and login into with your SQL credentials.
2. In Object Explorer, right-click a server and select Properties.
3. In the SQL Server Properties - <server_name> dialog box, click Connections.
4. Under Remote server connections, select or clear the Allow remote connections to this server check box.
   
   
   

Using Transact-SQL

1. Connect to the Database Engine.
2. From the Standard bar, click New Query.
3. Copy and paste the following example into the query window and click Execute. This example shows how to use sp_configure to set the value of the remote access option to 1.
   

    EXEC sp_configure 'remote access', 0 ;   
    GO   
    RECONFIGURE ;   
    GO   
   

Now we are done with all the configuration setting required to allow remote computer for accessing SQL server and you can restart your computer to gets the setting apply.

Thanks