Home > Resources > SQL Server > Configure SQL Server Remote Access

How to Configure Remote Access Available in SQL Server

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.

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:

Hot Categories
Windows Password
Product Key Finder
SQL Password
More +
Hot Articles
4 Ways to Change SQL Server SA Password
SQL Server 2012 SA Password Reset
How to Login SQL Server after SA Account Locked Out
How to Change Server Authentication Mode in SQL Server
How to Enable Mixed Mode Authentication for SQL Server
More +
Latest Articles
How to Correct SQL Login Failed Error after SA Password Forgot
Solve SQL Server Connection Error 26
How to Fix Login Failed to SQL Server
More +
Hot Products
Windows Password Genius
iTunes Password Genius
Product Key Finder
More +