This Q&A was previously posted on the Stack Exchange Network of websites.
This content is licensed under the Creative Commmons Attribution-Sharealike (CC BY-SA) license. As the author of these answers, this archive serves as a canonical location for permalinks, which will outlive Stack Overflow. See all my answers by looking at the Stack Exchange post category.
SQL Server Configuration – Disabling the “Allow Remote Connections to this Server” Option
Question
My organization had a security audit and was told that we need to DISABLE the "allow remote connection to this server" option for all of our SQL Servers. What impact would this have on connection to the SQL Server via Management Studio (SSMS) from a client machine/desktop or from a web app that might use a connection string or any other external app? All of the documentation I see regarding connectivity issues to a SQL Server seem to include "ENABLING" this option as a solution. Is there a workaround that will allow us to disable it but still allow the access we need from the target clients?
We have multiple versions that we would be doing this on 2012, 2014, 2016 and 2017.
asked 2021-06-07 by SFDCShark
Answer
This configuration option is deprecated, but deprecated doesn’t mean function-less. It does have functionality, and if you happen to be using (legacy) remote servers, you will not be able to disable this setting.
Per the docs:
This configuration option is an obscure SQL Server to SQL Server communication feature that is deprecated, and you probably shouldn’t be using it.
Note that my interpretation of this language is "you probably shouldn’t be changing this configuration", and should not be read as "you probably should disable this setting."
The documentation continues to describe the functionality:
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.
…
The remote access option only applies to servers that are added by using
sp_addserver, and is included for backward compatibility.
For more information on remote servers, see the documentation. New work should be (and probably is) using linked servers, rather than remote servers. However–if you do happen to be using legacy remote servers, then disabling this option would break stored procedure calls with the remote server.
answered 2021-06-07 by Andy Mallon