View or Configure Remote Server Connection Options (SQL Server)
Applies to: 
SQL Server
This article describes how to view or configure remote server connection options at the server level in SQL Server by using SQL Server Management Studio or Transact-SQL.
Important
This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
Permissions
Executing sp_serveroption requires ALTER ANY LINKED SERVER permission on the server.
Use SQL Server Management Studio
View or configure remote server connection options
In Object Explorer, right-click a server, and then select Properties.
In the SQL Server Properties - <server_name> dialog box, select Connections.
On the Connections page, review the Remote server connections settings, and modify them if necessary.
Repeat steps 1 through 3 on the other server of the remote server pair.
Use Transact-SQL
View remote server connection options
Connect to the Database Engine.
From the Standard bar, select New Query.
Copy and paste the following example into the query window and select Execute. This example uses sp_helpserver to return information about all remote servers.
USE master;
GO
EXEC sp_helpserver ;
Configure remote server connection options
Connect to the Database Engine.
From the Standard bar, select New Query.
Copy and paste the following example into the query window and select 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 SQL Server,
SEATTLE3, to be collation compatible with the local instance of SQL Server.
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.