view-or-configure-remote-server-connection-options-sql-server.md

title	View or Configure Remote Server Connection Options (SQL Server) | Microsoft Docs
ms.custom
ms.date	03/14/2017
ms.prod	sql-non-specified
ms.prod_service	database-engine
ms.service
ms.component	configure-windows
ms.reviewer
ms.suite	sql
ms.technology	database-engine
ms.tgt_pltfrm
ms.topic	article
helpviewer_keywords	remote servers [SQL Server], connection options servers [SQL Server], remote connections [SQL Server], remote servers
ms.assetid	356d3e6b-8514-4bd2-a683-9de147949b2b
caps.latest.revision	25
author	MikeRayMSFT
ms.author	mikeray
manager	craigg
ms.workload	On Demand

View or Configure Remote Server Connection Options (SQL Server)

[!INCLUDEappliesto-ss-xxxx-xxxx-xxx-md] This topic describes how to view or configure remote server connection options at the server level in [!INCLUDEssCurrent] by using [!INCLUDEssManStudioFull] or [!INCLUDEtsql].

In This Topic

• Before you begin:

  Security

• To view or configure remote server connection options, using:

  SQL Server Management Studio

  Transact-SQL

• Follow Up: After you configure remote server connection options

Before You Begin

Security

Permissions

Executing sp_serveroption requires ALTER ANY LINKED SERVER permission on the server.

Using SQL Server Management Studio

To view or configure remote server connection options

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

2. In the SQL Server Properties - <server_name> dialog box, click Connections.

3. On the Connections page, review the Remote server connections settings, and modify them if necessary.

4. Repeat steps 1 through 3 on the other server of the remote server pair.

Using Transact-SQL

To view remote server connection options

1. Connect to the [!INCLUDEssDE].

2. From the Standard bar, click New Query.

3. Copy and paste the following example into the query window and click Execute. This example uses sp_helpserver to return information about all remote servers.

USE master;  
GO  
EXEC sp_helpserver ;  

To configure remote server connection options

1. Connect to the [!INCLUDEssDE].

2. From the Standard bar, click New Query.

3. Copy and paste the following example into the query window and click Execute. This example shows how to use sp_serveroption to configure a remote server. The example configures a remote server corresponding to another instance of [!INCLUDEssNoVersion], SEATTLE3, to be collation compatible with the local instance of [!INCLUDEssNoVersion].

USE master;  
EXEC sp_serveroption 'SEATTLE3', 'collation compatible', 'true';  

Follow Up: After you configure remote server connection options

The remote server must be stopped and restarted before the setting can take effect.

See Also

Server Configuration Options (SQL Server)
Remote Servers
Linked Servers (Database Engine)
sp_linkedservers (Transact-SQL)
sp_helpserver (Transact-SQL)
sp_serveroption (Transact-SQL)