Server and Client Configuration and Troubleshooting

Like this article? We recommend

SQL Server 7 Essential Reference

Learn More Buy

Like this article? We recommend

Like this article? We recommend 

SQL Server 7 Essential Reference

Learn More Buy

Setting Up and Configuring Remote and Linked Servers

One SQL Server can interact with other SQL Servers as well as many other heterogeneous data sources. This can be done in two ways: remote servers and linked servers. When you set up a remote server, another SQL Server can execute stored procedures that reside in the remote server. Remote servers must be SQL Servers. This architecture is far more limited than the linked server architecture, which permits users to issue select, insert, update, and delete queries against the linked server. The linked server need not be a SQL Server; in fact, using linked servers, it is completely possible to join data from tables residing in SQL Server, Oracle, and Excel. In most cases, you will want to use linked servers rather than remote servers, because the linked servers are more powerful. I am going to cover remote servers as well as linked servers, however, because you may have legacy systems using the older architecture and because replication is implemented with remote servers.

Remote and Linked Server Configuration Options

Four configuration options deal with remote and linked servers. In most cases, the defaults are what you want, but you must make sure that the settings have not been changed from the default when you set up remote servers.

Options That Apply to Both Remote and Linked Servers

The following options apply to both remote and linked servers:

• Remote Login Timeout (S). This configuration option affects both remote and linked servers. It specifies how long to wait when connecting to a remote server. If you try to connect to a remote server, and that server is down, for example, you might wait forever. The default is 0, which means that you want to wait indefinitely. You may want to set this to an interval, such as 5 or 10 seconds. (S, D).

General Tip

The Remote Login Timeout option cannot be set in Enterprise Manager; you must use Transact-SQL or DMO to set it.

• Remote Query Timeout (S). This option specifies the number of seconds that SQL Server should wait after issuing a request to a linked server or a remote server before timing out if no results have been returned. The default is 0, which means that you wait indefinitely. (S, D)

Options That Apply Only to Remote Servers

The following options apply only to remote servers:

• Remote Access. The default is 1, which allows other servers to access this server remotely. If you are planning to use remote servers, you should leave this option alone. If you set it to 0, other servers can't access the server remotely. (S, S)

• Remote Proc Trans. If this option is set to 1, all operations using remote servers will be distributed transactions managed by MSDTC. Because a lot of overhead is required to manage distributed transactions, you should leave this option set to 0 (the default) and explicitly request a distributed transaction only when you need one. (S, D)

General Tip

You can read more about distributed transactions in the Books OnLine.

Remote Servers

Remote servers are set up in pairs. First, each server must be aware of the other's existence. Then, you must set up logins for the remote servers. These are effectively mappings between the login ID used on one of the servers to the login ID used on the other server.

Setting Up Remote Servers with Transact-SQL

This procedure must be done on both servers. First, use the following command:

sp_addlinkedserver 'server', 'SQL Server'

Suppose, for example, that you have two servers, one named ServerA and one named ServerB. On ServerA, issue the following command:

sp_addlinkedserver 'ServerB', 'SQL Server'

On ServerB, issue the following command:

sp_addlinkedserver 'ServerA', 'SQL Server'

Now, you must map the logins. The way you do this depends on whether the logins are SQL Server Authentication logins or NT Authentication logins. (See Chapter 9, "Security," for details on the different methods of authentication.)

Mapping SQL Server Authentication Logins

If the user has the same login name and password on both SQL Servers, it is not necessary to perform this step. If Tom has a login on ServerA and a login on ServerB, for example, the mapping is automatic. If the names differ, however, you must define the mappings. Assume for this example that ServerA has a login named Theresa, and you want that login to map to a login named RemoteUsers on ServerB. Use the command sp_addremotelogin, as follows:

sp_addremotelogin 'remoteserver', 'login', 'remote_name'

For example, on ServerB:

sp_addremotelogin 'ServerA', 'RemoteUsers', 'Theresa'

After this has been done, Theresa, running on ServerA, can execute any stored procedures that RemoteUsers have been given permission to on ServerB. If you want bidirectional logins (that is, from ServerB to ServerA), use a similar process on ServerA.

The final step is to specify whether a password is needed when Theresa wants to run a procedure on ServerB. In most cases, it is easiest if you do not request that the password be checked, because remote stored procedures are likely to be executed from local stored procedures and it is difficult to interact with a user for a password. You specify this with the command sp_remoteoption, as follows:

sp_remoteoption 'remoteserver', 'loginame',
'remotename', trusted, {true | false}

To continue the preceding example, the command should look like this:

sp_remoteoption 'ServerA', 'RemoteUsers', 'Theresa',
 trusted, true

Mapping NT Authentication Logins

Mapping NT Authentication logins is done differently. Use the command sp_addlinkedsrvlogin, as follows:

sp_addlinkedsrvlogin 'remoteserver', {true | false},
 'locallogin', 'remotename', 'rmtpassword'

The second argument must be false for an NT Authentication login. Assume that PINE\SHARON has an NT Authentication login on ServerA, and that you want to map this login to RemoteUsers on ServerB with no password. The command, issued on ServerB, should look like this:

sp_addlinkedsrvlogin 'ServerA', false, RemoteUsers,
 'PINE|SHARON', NULL

Setting Up Remote Servers with Enterprise Manager

It's much easier to set up remote servers with Enterprise Manager than with Transact-SQL, but it's not possible to map NT Authentication logins in Enterprise Manager. In the Security folder in the console pane, right-click on Remote Servers and choose New Remote Server. The screen shown in Figure 3.4 displays.

Figure 3.4 Remote Server Setup screen.

It is important to check the box labeled RPC near the top of the screen. You can map all users to a single login on the remote machine or you can map them individually as shown in Figure 3.3. To skip checking passwords on the remote server, don't check the box in the Check Password column.

Setting Up Remote Servers with DMO

You work with the RemoteServers collection, the RemoteServer object, the RemoteLogins collection, and the RemoteLogins object to set up remote servers with DMO.

To add a remote server, create a RemoteServer object. Specify its Name property and add it to the RemoteServers collection of a connected SQLServer object.

To set up remote logins, create a RemoteLogin object. Set the RemoteName, LocalName, and Trusted properties. (Set these to true if password is not to be checked; otherwise set them to false.) Add the object to the RemoteLogins collection of a RemoteServer object.

Linked Servers

Linked servers are a new feature in SQL Server 7.0. They allow direct queries against many different data sources, including Oracle, Excel, Access, and ODBC data sources. After you have set up a linked server, you can query it by just including the linked server name as part of the table name, as follows:

SELECT * from MySpreadsheet...Sheet1$ 
SELECT * from ORACLEDB..Scott.Emp

Setting Up Linked Servers with Transact-SQL

Use the sp_addlinkedserver command to set up a linked server with Transact-SQL. This is the same command I described under remote servers, but there is a lot more information that you must provide when linking a heterogeneous server, as follows:

sp_addlinkedserver 'server', 'product_name',
provider_name','data_source','location',
'provider_string', 'catalog'

Server is a name you assign to the server, such as MyLinkedServer. The following table lists possibilities for the other parameters:

If you want to set up an Excel spreadsheet as a linked server, the command should look like the following:

sp_addlinkedserver 'ExcelSpreadsheet', '', 
'Microsoft.Jet.OLEDB.4.0',
 '\\EXCEL:\MySpreadsheets\LastMonth.xls', '', 'Excel 5.0'

Remember that the path names are from the SQL Server's point of view; you must use share names accessible to SQL Server. Do not use your mapped drive letters. If the files do not reside on the SQL Server computer, SQL Server must be logging in with a domain account and that account must have permission to access the files.

When you set up linked servers, it's also necessary to configure logins for those servers. Use the command sp_addlinkedsrvlogin, as follows:

sp_addlinkedsrvlogin 'servername',{true | false},
 'SQL Server loginname',
 'linked server login name',
 'linked server password']

When you specify true for the second argument, the SQL Server login name is used to connect to the remote server. You can only use this if the SQL Server login is a SQL Server Authentication login. You may specify null for the SQL Server login name. In this case, all SQL Server logins will log in to the linked server with the linked server login name and password. If you provide a value, it must be a SQL Server login or a Windows NT login that has been granted access to the SQL Server. Assume, for example, that the Excel spreadsheet allows an Admin login with no password, and you want all SQL Server users to be able to log in to this linked server. The command to set this up is as follows:

sp_addlinkedsrvlogin 'ExcelSpreadsheet', FALSE, 

  NULL, 'Admin', NULL

Setting Up Linked Servers with Enterprise Manager

It's very simple to set up linked servers with Enterprise Manager. In the Security folder, right-click Linked Servers and choose New. The screen shown in Figure 3.5 displays.

Figure 3.5 Setting up a linked server.

As annoying as it is, all linked server names must be in uppercase letters. Select the OLE DB Provider from the drop-down list, and provide the other elements using the parameters table in the preceding section. Leave Collation Compatible unchecked unless you are absolutely certain that the remote data source has the same character set as SQL Server. The RPC and RPC Out boxes only make sense for SQL Servers; they specify whether remote procedure calls from and to the server are allowed.

It's also possible to set up the logins with Enterprise Manager. You do so on the Security tab illustrated in Figure 3.6.

Figure 3.6 Specifying linked server logins in Enterprise Manager.

If you choose No Security Context Will Be Used, no logins at all are used. This makes sense for things such as Excel, if the spreadsheet isn't password protected, and text files, which have no security other than file-level permissions. If you choose They Will Be Impersonated, the SQL Server login is used to authenticate the user at the linked server. This only works for SQL Server Authentication logins. Checking this makes sense if the remote server is a SQL Server with the same logins as the server on which the linked server is defined. If you choose They Will Be Mapped To, all SQL Server users will connect to the linked server with the specified username and password. You should choose They Are Not Allowed to Access if you want to specify the logins individually in the grid at the bottom of the screen.

In the grid, provide the SQL Server login. In most cases, you won't choose Impersonate, because doing so requires that the logins be the same on both servers.

Refer back to Figure 3.6. With this setup, Tigger logs in to the linked server as a user named Scott with a password of Tiger. Any other users will log in as a user named Admin with a password of secret.

Setting Up Linked Servers with DMO

Each SQL Server object has a LinkedServers collection that contains LinkedServer objects. Each LinkedServer object has a LinkedServerLogins collection that contains LinkedServerLogin objects.

To add a new LinkedServer, instantiate a LinkedServer object and provide its Name property. Use the same properties you used for setting up linked servers with Transact-SQL to set up properties for DMO. When the properties of the LinkedServer object are complete, add it to the LinkedServers collection of a connected SQLServer object.

To define linked server logins, instantiate a LinkedServerLogin object. This object has four properties.

Then, add the LinkedServerLogin object to the appropriate LinkedServerLogins collection.