To work properly, distributed queries and transactions depend on linked servers. You configure the linked servers you are using by registering their connection and data source information in SQL Server. Then you will be able to reference the linked server by using a single logical name. If you no longer need to link to a server, you can remove the linked server connection.
If you want a server to be able to use distributed queries, distributed transactions, or remote command execution, you must configure linked server connections to other servers. For example, if clients that access a server named Zeta make distributed queries to Pluto and Omega, you must configure Pluto and Omega as linked servers on Zeta. If clients that connect to Pluto make distributed queries to Zeta and Omega, you must configure Zeta and Omega as linked servers on Pluto. To add a linked server, complete the following steps:
In SQL Server Management Studio, connect to the server instance you want to configure.
In Object Explorer view, expand the Server Objects node.
Right-click the Linked Servers entry, and then choose New Linked Server from the shortcut menu to open the dialog box shown in Figure 11-1.
In the Linked Server text box, type the name of the linked server to create.
If you are linking to a SQL Server, select the SQL Server option.
If you are linking to a different data source, select the Other Data Source option, and then configure the data source using the text boxes provided. If there is no text box available in the dialog box for a specific option, then you cannot configure that option for the selected provider. Provide information in the text boxes as follows:
Provider. Select the name of the OLE DB provider to use when communicating with the specified linked server from the drop-down list box.
Product Name. Set the server product name for the OLE DB data source.
Data Source. Provide the OLE DB data source, which is used to initialize the OLE DB provider.
Provider String. Type a provider-specific connection string that identifies a unique data source.
Location. Set the location of the database for the OLE DB provider.
Catalog. Indicate the catalog to use when connecting to the OLE DB provider.
The most commonly used option combinations are provider name and data source. For example, if you were configuring a linked server for a Microsoft Access database or a Microsoft Excel spreadsheet, you would select Microsoft Jet 4.0 OLE DB Provider and then set the data source name. With Oracle, you would select Microsoft OLE DB Provider For Oracle and then set the data source name.
Select the Server Options page from the list in the left pane of the dialog box to configure server-specific settings as follows:
Collation Compatible. Set this option to enable SQL Server to send comparisons on character columns to the provider. Otherwise, SQL Server evaluates comparisons on character columns locally. Set this option only when the linked server has the same collation as the local server.
Note
Collation compatible controls sort order settings. If you do not select this option, SQL Server uses the local sort order. This affects the order of result sets, and you should note it when you develop SQL Server applications or configure clients that support distributed transactions.
Data Access. Set this option to enable the linked server for distributed query access.
RPC. Set this option to enable remote procedure calls from the linked server.
RPC Out. Set this option to enable remote procedure calls to the linked server.
Use Remote Collation. Set this option to have SQL Server use the collation from the linked server’s character columns. If you do not set this option, SQL Server interprets data from the linked server using the default collation of the local server instance. Note that only SQL Server databases take advantage of this option.
Collation Name. Set this option to assign a specific collation for queries and transactions. You must set the Collation Compatible option to False before you can set this option.
Connection Timeout. Use this text box to set the time-out value for connections made to the remote server.
Query Timeout. Use this text box to set the time-out value for queries made to the remote server.
Click OK to create the linked server. Next, you must configure security settings for the linked server, as discussed in the section titled "Configuring Security for Linked Servers" later in this chapter.
The corresponding Transact-SQL command for adding linked servers is sp_addlinkedserver. Use this stored procedure as shown in Example 11-3.
Example 11-3. sp_addlinkedserver Syntax and Usage
Syntax
sp_addlinkedserver [@server =] 'server' [, [@srvproduct =] 'product_name'] [, [@provider =] 'provider_name'] [, [@datasrc =] 'data_source'] [, [@location =] 'location'] [, [@provstr =] 'provider_string'] [, [@catalog =] 'catalog']
Usage
EXEC sp_addlinkedserver
@server='ORADBSVR8',
@srvproduct='Oracle',
@provider='OraOLEDB.Oracle',
@datasrc='ORACLE10';
GOTable 11-1 provides a summary of parameter values you can use when configuring various OLE DB providers. The table also shows the sp_addlinkedserver parameter values to use for each OLE DB provider. Because some providers have different configurations, there may be more than one row for a particular data source type.
Table 11-1. Parameter Values for Configuring OLE DB Providers
DB data | OLE DB | ||||
|---|---|---|---|---|---|
DB data source | provider | product_name | provider_name | data_source | Other |
SQL Server | Microsoft SQL Native Client OLE DB Provider | SQL Server (default) | — | — | — |
SQL Server | Microsoft SQL Native Client OLE DB Provider | — | SQLNCLI | Network name of SQL Server (for default instance) | Database name optional for catalog field |
SQL Server | Microsoft SQL Native Client OLE DB Provider | — | SQLNCLI | Servernameinstancename (for specific instance) | Database name optional for catalog field |
Oracle | Microsoft OLE DB Provider for Oracle | Any | MSDAORA | SQL*Net alias for Oracle database | — |
Oracle 8.0 and later | Oracle Provider for OLE DB | Any | OraOLEDB.Oracle | Alias for the Oracle database | — |
Access/Jet | Microsoft OLE DB Provider for Jet | Any | Microsoft.Jet.OLEDB.4.0 | Full path name of Jet database file | — |
ODBC data source | Microsoft OLE DB Provider for ODBC | Any | MSDASQL | System DSN of ODBC data source | — |
ODBC data source | Microsoft OLE DB Provider for ODBC | Any | MSDASQL | — | ODBC connection string for provider_string |
File system | Microsoft OLE DB Provider for Indexing Service | Any | MSIDXS | Indexing Service catalog name | — |
Microsoft Excel Spreadsheet | Microsoft OLE DB Provider for Jet | Any | Microsoft.Jet.OLEDB.4.0 | Full path name of Excel file | Excel 5.0 for provider_string |
IBM DB2 Database | Microsoft OLE DB Provider for DB2 | Any | DB2OLEDB | — | Catalog name of DB2 database in catalog field |
You use linked server security to control access and to determine how local logins are used. By default, new linked servers are set to have no security context when a user login is not defined. This blocks access to all logins not explicitly mapped to the linked server.
To change the security settings for a linked server, complete the following steps:
Start SQL Server Management Studio, and then access the local server that contains the linked server definitions you want to change.
In Object Explorer view, expand the Server Objects node, and then expand the Linked Servers node. You should now see an entry for each linked server that you created on the currently selected server.
Right-click the icon for the linked server you want to configure, and then choose Properties to open the Linked Server Properties dialog box.
In the Linked Server Properties dialog box, click the Security page, as shown in Figure 11-2.
Map local logins to remote logins by clicking Add.
Configure the following options on a per login basis:
Local Login. Sets the ID of a local login that can connect to the linked server.
Impersonate. Select this check box to use the local login ID to connect to the linked server. The local login ID must match a login ID on the linked server exactly.
Use the option buttons and text boxes in the lower portion of the Security page to set a default security context for all users who do not have a specific login setting for the linked server. These options are used as follows:
Not Be Made. Users without logins are not allowed access to the linked server.
Be Made Without Using A Security Context. Blocks access to all logins not explicitly mapped to the linked server.
Be Made Using The Login’ s Current Security Context. Logins not explicitly mapped to the linked server use their current login and password to connect to the linked server. Access is denied if the login and password do not exist on the linked server.
Be Made Using This Security Context. Logins not explicitly mapped to the linked server will use the login and password provided in the Remote Login and With Password text boxes.
When you have finished configuring logins, click OK.
The related Transact-SQL command for configuring logins is sp_addlinkedsrvlogin. Use this stored procedure as shown in Example 11-4.
Example 11-4. sp_addlinkedsrvlogin Syntax and Usage
Syntax
sp_addlinkedsrvlogin [@rmtsrvname =] 'rmtsrvname' [,[@useself =] 'useself'] [,[@locallogin =] 'locallogin'] [,[@rmtuser =] 'rmtuser'] [,[@rmtpassword =] 'rmtpassword']
Usage
EXEC sp_addlinkedsrvlogin
@rmtsrvname='ORADBSVR8',
@useself='false',
@locallogin=null,
@rmtuser='william',
@rmtpassword='tango98';
GOYou set server options for remote and linked servers using sp_serveroption. Use this stored procedure as shown in Example 11-5. Key options are summarized in Table 11-2.
Table 11-2. Key Options for sp_serveroption
Option Name | Option Usage/Description |
|---|---|
collation compatible | If TRUE, compatible collation is assumed with regard to character set and collation sequence (or sort order) and SQL Server sends comparisons on character columns to the provider. Otherwise, SQL Server always evaluates comparisons on character columns locally. |
collation name | Sets the name of the collation used by the remote data source if use remote collation is TRUE and the data source is not a SQL Server data source. The name must be a specific, single collation supported by SQL Server. |
connect timeout | Sets the time-out value for connecting to the linked server. Set to 0 to use the sp_configure default. |
data access | Set to TRUE to enable a linked server for distributed query access. Set to FALSE to disable. |
lazy schema validation | If TRUE, SQL Server skips schema checking of remote tables at the beginning of a query. |
query timeout | Sets the time-out value for queries against a linked server. Set to 0 to use the sp_configure default. |
rpc | Set to TRUE to enable RPC from the linked server. |
rpc out | Set to TRUE to enable RPC to the linked server. |
use remote collation | If TRUE, the collation of remote columns is used for SQL Server data sources, and the collation specified in collation name is used for non-SQL Server data sources. Otherwise, distributed queries will always use the default collation of the local server, while collation name and the collation of remote columns are ignored. |
If you do not need a linked server anymore, you can delete it by completing the following steps:
Start SQL Server Management Studio, and then access the local server that contains the linked server definitions you want to delete.
In Object Explorer view, expand the Server Objects node, and then expand the Linked Servers node. You should now see an entry for each linked server that you created on the currently selected server.
Right-click the icon for the linked server you want to remove, and then choose Delete to open the Delete Object dialog box.
In the Delete Object dialog box, click OK.
The Transact-SQL command to drop linked servers is sp_dropserver. The Transact-SQL command to drop linked server logins is sp_droplinkedsrvlogin. Use these stored procedures as shown in Samples Example 11-6 and Example 11-7.