logo
SQL Server Configure SQL Server Remote Access

How to Configure Remote Access Available in SQL Server

By Hally | Last Updated

The remote access option in SQL Server controls the execution of stored procedures from local or remote servers on which instances of SQL Server are running. And default value of remote access option is 1, which grants permission to run local procedures from remote servers or remote procedures from the local server. If you want to prevent this, set the option to 0.

Restrictions:

The remote access option only applies to servers that are added by using sp_addserver, and is included for backward compatibility.

This article describes how to configure remote access in SQL Server by using SQL Server Management Studio or Transaction-SQL.

  • Configure Remote Access using SQL Server Management Studio
  • Configure SQL Server Remote Access with Transaction-SQL

Way 1: Configure Remote Access using SQL Server Management Studio

Please login into SQL Server with Windows Authentication mode or SQL Server Authentication mode.

Tips: Why Cannot Login into SQL Server with Windows Authentication Mode

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

choose server properties option

2. Click the Connections node.

select remote server connections in SQL Server Management Studio

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

Way 2: Configure SQL Server Remote Access with Transaction-SQL

1. Connect to the SQL Server Database Engine with SQL SA account or Windows administrator.

2. From the Standard bar, click New Query.

configure SQL Server remote access with transaction

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

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

After trying with above two ways, believe you have configured remote access for SQL Server, you could control server procedures on local computer.

Related Articles: