Why sql server allow remote connections and what does it do?

The remote access option controls the execution of stored procedures from local or remote servers on which instances of SQL Server are running.

This checkbox is the GUI way of setting the remote access configuration option,

EXEC sp_configure 'remote access', 0; -- UI checkbox unchecked EXEC sp_configure 'remote access', 1; -- UI checkbox checked

The feature is labelled incorrectly in the SSMS UI:

• Incorrect: Allow remote connections to this server
  
• Correct: Allow remote connections to from this server
  

and the documentation describes it wrong:

Allow remote connections to this server

Controls the execution of stored procedures from remote servers running instances of SQL Server. Selecting this check box has the same effect as setting the sp_configure remote access option to 1. Clearing it prevents execution of stored procedures from a remote server.

It should be:

Allow remote connections to from this server

Controls the execution of stored procedures from to remote servers running instances of SQL Server. Selecting this check box has the same effect as setting the sp_configure remote access option to 1. Clearing it prevents execution of stored procedures from to a remote server.

Documentation from BOL 2000

SQL Server 2000 was the last time this feature was documented. Reproduced here for posterity and debugging purposes:

Configuring Remote Servers

A remote server configuration allows a client connected to one instance of Microsoft® SQL Server™ to execute a stored procedure on another instance of SQL Server without establishing another connection. The server to which the client is connected accepts the client request and sends the request to the remote server on behalf of the client. The remote server processes the request and returns any results to the original server, which in turn passes those results to the client.

If you want to set up a server configuration in order to execute stored procedures on another server and do not have existing remote server configurations, use linked servers instead of remote servers. Both stored procedures and distributed queries are allowed against linked servers; however, only stored procedures are allowed against remote servers.

 Note Support for remote servers is provided for backward compatibility only. New applications that must execute stored procedures against remote instances of SQL Server should use linked servers instead.

Disabling the option prevents outgoing connections

If you try to execute a stored procedure on a remote linked server (i.e. sp_addlinkedserver):

  EXECUTE [Hyperion].[SharePoint].[dbo].[GetUnreadDocuments]

it will run fine.

If you then disable remote access on the local server:

• EXEC sp_configure 'remote access', 0;, or
  
• uncheck "Allow remote connections to from this server"
  

the same linked stored procedure will fail:

  EXECUTE [Hyperion].[SharePoint].[dbo].[GetUnreadDocuments]

  Msg 7201, Level 17, State 4, Procedure GetUnreadDocuments, Line 1

Could not execute procedure on remote server 'Hyperion' because SQL Server is not configured for remote access. Ask your system administrator to reconfigure SQL Server to allow remote access.

Short version

Remote access controls outgoing access to remote stored procedures.

And the documentation incorrectly notes that using sp_addlinkedserver avoids the problem:

This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use sp_addlinkedserver instead.