How to set up a remote server to allow the use of remote stored procedures (Transact-SQL)

How to set up a remote server to allow the use of remote stored procedures (Transact-SQL)

To set up a remote server to allow the use of remote stored procedures

1. Run the following code on the first server running Microsoft® SQL Server™:

   EXEC sp_addlinkedserver ServerName1, N'SQL Server'
   EXEC sp_addlinkedserver ServerName2
   EXEC sp_configure 'remote access', 1
   RECONFIGURE
   GO
   

2. Stop and restart the first SQL Server.
   
   
3. Run the following code on the second SQL Server. Make sure you are logging in using SQL Server Authentication.

   -- The example shows how to set up access for a login 'sa'
   --  from ServerName1 on ServerName2.
   EXEC sp_addlinkedserver ServerName2, local
   EXEC sp_addlinkedserver ServerName1
   EXEC sp_configure 'remote access', 1
   RECONFIGURE
   GO
   -- Assumes that the login 'sa' in ServerName2 and ServerName1
   --  have the same password.
   EXEC sp_addremotelogin ServerName1, sa, sa
   GO
   

4. Stop and restart the second SQL Server.
   
   
5. Using the sa login, you can now execute a stored procedure on the second SQL Server from the first SQL Server.

See Also

sp_addremotelogin

sp_configure

sp_addlinkedserver

RECONFIGURE