Let’s take a closer look at configuring SQL server remote access using sp_configure. Bobcares, as a part of our Server Management Services, offers solutions to every query that comes our way.

SQL server remote access using sp_configure

With the SQL server remote access option, it is possible to regulate the execution of stored procedures from local or remote servers that host instances of SQL Server. The default value is set to 1. If we need to avoid running local procedures from remote servers or remote procedures from the local server, we have to change the value to 0.

Only servers added using sp_addserver are eligible for the remote access option, which is for backward compatibility. By default, all users have permission to execute sp_configure with no parameters or just the first parameter. A user needs to have the ALTER SETTINGS server-level permission in order to run the RECONFIGURE statement or execute sp_configure with both parameters to change a configuration option. The fixed server roles sysadmin and serveradmin implicitly hold the ALTER SETTINGS permission.

How to configure SQL server remote access?

We have to execute the following steps to configure SQL server remote access using sp_configure:

• Firstly, use a Windows administrator account or the SQL SA account to connect to the SQL Server Database Engine.
• Then click “New Query” under the Standard bar.
• Lastly, include the following code in the query window. Then click “Execute”.

  USE 'ServerName' ;

  GO

  EXEC sp_configure ‘remote access’, 0 ;

  GO

  RECONFIGURE ;

  GO

   

The above code configures the SQL server remote access option to 0. With this, we can also control server procedures on a local computer.

[Searching for a solution to a different question? We’re here to help.]

Conclusion

In this article, we provide a quick solution to configure SQL server remote access with the sp_configure function.