SQL Server, Allow remote connections to this server option. What does this do?

Question

Can someone clue me in on what this option actually does and what would it affect if I were to suddenly uncheck it in an established production environment?

Answer 1

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.

That is false.

See also

• How to execute a stored procedure against linked server?

Answer 2

It is generally confused with remote access to the instance itself, over TCPIP, but that is controlled at the service level. This option is used to control execution of procedures from remote instance/servers. What you would do through a linked servers. It is actually ignored as of 2008 R2, I believe. This is now controlled when you configure the linked server.

Reference:

This topic describes how to configure the remote access server configuration option in SQL Server 2016 by using SQL Server Management Studio or Transact-SQL. The remote access option controls the execution of stored procedures from local or remote servers on which instances of SQL Server are running. This default value for this option is 1. This grants permission to run local stored procedures from remote servers or remote stored procedures from the local server. To prevent 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 0.

Answer 3

Enabling this will allow you to communicate with the SQL Server via any medium internally (VPN network) and externally.

Some practical uses are:

• Third Party SQL server reporting application like: https://www.manageengine.com/products/applications_manager/sql/ (You need to enable that checkbox and setup your TCP port using this application)
• Multi-server Administration Jobs and S-Procedures

Note that TCP IP configuration is paired with this sometimes.