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_addserver ServerName1, local

   EXEC sp_addserver 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_addserver ServerName2, local
EXEC sp_addserver 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