SQL Server Enable Remote Connections

Kailash Chandra Behera | Sunday, April 29, 2018

Introduction

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


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 install it.

Three main reasons are there which blocks us to access SQL instance from a remote computer.

The first reason is when we install the SQL server, the TCP protocol of the SQL server is disabled by default. SQL server uses TCP protocol for communicating between two remote SQL instances, to make SQL instance available for the remote computers we need to enable TCP protocol first. Hence verify that sql server is configured for remote connections.

The second reason is the Remote access for SQL instance may also disabled by default by the 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 locally 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 a remote computer. To allow locally 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.

The third reason is the firewall. The firewall inbound and outbound rules allow or block traffic attempts to access the computer that matches certain criteria in the rule. you need to change windows firewall settings to allow sql server through firewall

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

SQL Allow Remote Connections


We had discussed the SQL protocol (TCP) in the above discussion, so first we will enable the protocol. The TCP protocol can be enabled using the 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.

    sql server enable remote connections
    SQL Server Enable Remote Connections


  3. After this step, the Warning box pops 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.

    sql server enable remote connections
    SQL Server is configured to allow remote connections
We successfully enabled the TCP protocol for remote access to the SQL instance. Now we will open the SQL port in the firewall for access from the remote computers as the firewall blocks the port. To open the SQL port for public access or remote access we need to add rules in the firewall, there are two types of rule firewall supports i.e Inbound 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 the computer, you need to create an 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 rules that blocks outbound traffic.

MSSQL Allow Remote Connections

Configure the Remote Access Option Using SQL Server Management Studio
  1. Open SQL Server and login in with your SQL credentials.
  2. In Object Explorer, right-click a server and select Properties.
  3. In the SQL Server Properties - dialog box, click Connections.
  4. Under Remote server connections, select or clear the Allow remote connections to this server check box.

    sql server allow remote connections
    SQL Server Enable Remote Connections


Configure the Remote Access Option 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 settings required the SQL allows remote connections for accessing the SQL server and you can restart your computer to gets the setting to apply.

Related Articles

  1. Encrypt data across communication channels
  2. Recovery Database From Warm-Standy Mode
  3. Setting Memory Limit for a SQL Server Instance
  4. Encrypt data using Transparent Data Encryption (TDE) in SQL

Thanks