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
- 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.)
- 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 - 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 is configured to allow remote connections
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- Open SQL Server and login in with your SQL credentials.
- In Object Explorer, right-click a server and select Properties.
- In the SQL Server Properties -
dialog box, click Connections. - Under Remote server connections, select or clear the Allow remote connections to this server check box.
SQL Server Enable Remote Connections
- Connect to the Database Engine.
- From the Standard bar, click New Query.
- 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
Related Articles
- Encrypt data across communication channels
- Recovery Database From Warm-Standy Mode
- Setting Memory Limit for a SQL Server Instance
- Encrypt data using Transparent Data Encryption (TDE) in SQL
Thanks