Remote Servers

By definition, a remote server is a server that you access as part of a client process without opening a separate, distinct, direct client connection. SQL Server manages the communication between servers using RPCs. Essentially, the SQL Server to which the client is connected opens up another connection to the remote server and submits the stored procedure request to the remote server. Any results are passed back to the local server, which then passes the results down to the originating client application (see Figure 19.1).

Figure 19.1. The remote server is accessed through the local server, and the client maintains only a single connection to the local server.

You call a remote procedure the same way that you call a local procedure; the only difference is that you need to fully qualify the name of the procedure with the name of the server. Following is the syntax:

 execute  remote_server_name.db_name.owner_name.procedure_name  

Remote servers are more limited in functionality and a bit more time-consuming to set up than linked servers. The following list outlines the steps for setting up remote servers:

1. Define the local and remote servers on both servers.

2. Configure each server for remote access.

3. On the remote server, define the method for mapping logins and users to the server's own logins and users.

4. Set the remote option for password checking.

If you are connecting between multiple SQL Server 2000 or SQL Server 7.0 servers, it is best to set them up as linked servers. However, if you need to execute remote procedure calls on a pre-7.0 SQL Server, you'll need to set up remote servers. Let's take a look at how that is done just in case you might need it.

Remote Server Setup

Assume that the local server is called near_server and the remote server is called far_server . First, you need to use sp_addserver to add the remote server name to the sysservers table in the master database if it's not defined already. You can get a list of the servers defined using sp_helpserver :

[View full width]

 
 [View full width] 
 exec sp_helpserver  go name             network_name   status                           id     collation_name  connect_timeout query_timeout ---------------- -------------- -------------------------------- --     --------------  --------------- ------------- near_server      near_server    rpc,rpc out,use remote collation  0     NULL  0             0 

Generally, you won't need to execute sp_addserver for the local server name. This is usually taken care of during setup. The local server will have an ID of . If you do need to add the definition for the local server, specify the local flag as the second argument:

 exec sp_addserver near_server, local 

You will need to execute sp_addserver once for each of the remote servers that you will access from the local server. For example, on the local server ( near_server ), execute the following command to add far_server :

 exec sp_addserver far_server 

If the local server is a 7.0 or later version of SQL Server, you can add the remote servers using sp_addlinkedserver :

 exec sp_addlinkedserver far_server 

On the remote server ( far_server ), you need to define the local server ( near_server ) that will be connecting to it:

 exec sp_addserver near_server 

To add remote servers in Enterprise Manager, open the Security folder, right-click on Remote Servers, and choose the New Remote Server option to bring up the Remote Server Properties dialog box (see Figure 19.2). In this dialog box, you can enter the server name and specify whether it allows the remote server to execute RPCs. You can also establish how logins are mapped when executing RPCs. Login mapping is covered a few paragraphs later in this section.

Figure 19.2. Adding remote servers in SQL Enterprise Manager.

Next , you need to ensure that each server is configured for remote access. By default, remote access is automatically enabled during setup to support replication. If it has been disabled, you'll need to re-enable it using Enterprise Manager or T-SQL. The syntax to configure each server for remote access is as follows :

 exec sp_configure 'remote access', 1  reconfigure 

To enable remote access using Enterprise Manager, right-click the server and choose the Properties option in the pop-up menu. Go to the Connection tab and make sure the check box Allow Other SQL Servers to Connect Remotely to This SQL Server Using RPC is checked (see Figure 19.3). In this dialog box, you can also set the length of time the RPC should wait for query results. The default is , which indicates waiting an infinite amount of time. Click OK to accept any changes.

Figure 19.3. Enabling remote access in SQL Enterprise Manager.

After enabling remote access, you'll need to shut down and restart each server.

The next step takes place on the remote server. This is where things get a bit tricky.

What you need to do is define how to map the logins from the server making the remote procedure request ( near_server ) to the environment on the server receiving the request ( far_server ). Although you are technically setting things up on the remote server ( far_server ), it is treated as the local server, and the local server ( near_server ) is treated as the remote server. Confused yet? I've always found it easiest to not try to think about it too much and just follow the steps. Following is the syntax:

 exec sp_addremotelogin  remote_server_name  [,  local_login_name  [,  remote_login_name  ]] 

For example, on the remote server ( far_server ), execute the following command to map each login on near_server to the same login on far_server :

 exec sp_addremotelogin near_server 

This is the simplest mapping method. It presumes that the logins are the same on both servers, and maps login to login.

TIP

If users from the remote server need access on your server, don't forget to add them with sp_addlogin .

To map all remote logins from the remote server ( near_server ) to a single local login on the local server ( far_server ), execute the following:

 exec sp_addremotelogin near_server, far_server_user 

This is another straightforward mapping method. Any legitimate user on near_server will be mapped to the single specified login on far_server .

The last login mapping method allows you to map each individual login on the remote server ( near_server ) to a different login name on the local server ( far_server ). In the following example, the login named mdoe on near_server will be mapped to the login jdoe on far_server .

 exec sp_addremotelogin near_server, jdoe, mdoe 

The login to which you map the remote logins will determine the permissions the remote users will have on the local server. If you want to restrict the procedures that the remote users can execute, be sure to set the appropriate permissions on the procedure for the login to which they are mapping.

The last step is to configure how the local server ( far_server ) validates remote logins coming from far_server . This is accomplished using the sp_remoteoption system procedure:

 exec sp_remoteoption remote_server, local_login_name, remote_login_name,  option,  {true  false} 

Essentially, you only have one option availablewhether to trust all logins from the remote server, or to not trust them and require the passwords to match between the remote server logins and the local logins. This is accomplished by setting the trusted option to true (trusted) or false (not trusted).

NOTE

If the trusted option is not turned on, you need to establish and maintain synchronized passwords between servers. Because you can't specify a separate remote password when executing a remote procedure call, SQL Server validates the passwords by matching the user's password from the originating server with the password for the mapped login ID on the receiving server. If the passwords are not synchronized, remote access will not be granted.

When mapping remote logins to different login IDs or to a single login ID on the receiving server, the trusted option is typically used because it would be unfeasible to try and synchronize passwords in these situations. For example, all users on the requesting server executing RPCs would have to have the same password as the login ID to which they are mapped on the receiving server. This would tend to create a bit of a security hole.

For example, on the remote server ( far_server ), execute the following procedure to set up logins without requiring synchronized passwords between servers:

 sp_remoteoption near_server, near_server_login, null, trusted, true 

To get information on individual logins mapped for a server and whether they are trusted, use the sp_helpremotelogin command:

 sp_helpremotelogin [  remote_server  [,  remote_name  ] ] 

For a list of all remote logins, execute sp_helpremotelogin without a parameter. In the example that follows, three different remote logins are configured for two different remote servers. The sa logins on both remote servers KAYDEROSS and NLCWEST1 map to the local server login repl_subscriber . All other logins from NLCWEST1 map to the remote_login login ID on the local server. All remote logins are trusted so passwords do not have to be synchronized between the servers.

 sp_helpremotelogin  server                 local_user_name        remote_user_name       options ---------------------- ---------------------- ---------------------- --------- KAYDEROSS              repl_subscriber        sa                     trusted NLCWEST1               remote_login           ** mapped locally **   trusted NLCWEST1               repl_subscriber        sa                     trusted 

To set up remote logins and the trusted option in Enterprise Manager, open up the Security folder for the server that will be receiving the RPC requests and click Remote Servers to display a list of defined servers. Bring up the Properties dialog box, as shown in Figure 19.2. Within the remote server properties, you can establish the various login mappings and whether passwords need to be checked.

It's easy to see how setting up remote servers can be a confusing and tedious process. You have to perform setup tasks on both the local and remote servers. In addition, the mapping of logins severely limits what types of servers can be accessed. Login mappings are performed at the remote server instead of the local server, which works fine if the remote server is SQL Server, but how do you perform this task in another database environment that doesn't have user mappings? How do you tell an Oracle database to which Oracle user to map a SQL Server user? These are just a few of the problems with remote servers. After you see how linked servers are set up and see the expanded capabilities they provide, you won't want to use remote servers unless you absolutely have to, which should only be when you need to execute RPCs on pre-7.0 SQL Servers.