Skip to content

Latest commit

 

History

History
85 lines (57 loc) · 5.17 KB

configure-the-remote-access-server-configuration-option.md

File metadata and controls

85 lines (57 loc) · 5.17 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic helpviewer_keywords
Server configuration: remote access
Learn about alternatives to the deprecated remote access option. View other sources for troubleshooting issues with SQL Server connections.
rwestMSFT
randolphwest
07/22/2024
sql
configuration
conceptual
remote servers [SQL Server], stored procedure execution

Server configuration: remote access

[!INCLUDE SQL Server]

This article is about the remote access configuration option, which is a deprecated [!INCLUDE ssNoVersion] to [!INCLUDE ssNoVersion] communication feature.

This option affects servers that are added by using sp_addserver and sp_addlinkedserver. You should leave remote access enabled (the default) if you use linked servers.

Important

[!INCLUDE ssNoteDepFutureAvoid]

If you reached this page because you're having trouble connecting to [!INCLUDE ssNoVersion], see one of the following articles instead:

Programmers might be interested in the following articles:

Manage remote access

The remote access configuration option controls the execution of stored procedures from local or remote servers on which instances of [!INCLUDE ssNoVersion] are running.

The default value for the remote access option is 1 (enabled). 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 (disabled).

This setting doesn't take effect until you restart [!INCLUDE ssNoVersion].

Remote access is required for the log shipping status report in SQL Server Management Studio (SSMS) to work and the LSAlert Job to complete appropriately.

Permissions

Execute permissions on sp_configure with no parameters or with only the first parameter are granted to all users by default.

To execute sp_configure with both parameters to change a configuration option, or to run the RECONFIGURE statement, a user must be granted the ALTER SETTINGS server-level permission. The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.

Use SQL Server Management Studio

  1. In Object Explorer, right-click a server and select Properties.

  2. Select the Connections node.

  3. Under Remote server connections, select or clear the Allow remote connections to this server check box.

Use Transact-SQL

  1. Connect to the [!INCLUDE ssDE].

  2. From the Standard bar, select New Query.

  3. Copy and paste the following example into the query window and select Execute. This example shows how to use sp_configure to set the value of the remote access option to 0.

EXEC sp_configure 'remote access', 0;
GO
RECONFIGURE;
GO

For more information, see Server configuration options.

Related content