How to Create a Linked Server

When BizTalk Server is installed in a distributed topology, the databases that belong to a BizTalk Group exist on multiple SQL Server. You must configure a linked server connection to each of the remote servers before you can back up the entire BizTalk environment from the BizTalk Management server. A linked server is an OLE DB data source that is used in SQL Server distributed queries.

As a part of the backup and restore process, the Backup BizTalk Server job automatically creates linked servers. If necessary, however, you can manually create linked servers using the procedure described below.

Linked servers can also be created using the sp_addlinkedserver stored procedure. There are security considerations associated with this operation. When a linked server is created using sp_addlinkedserver, all local logins will be mapped to the new linked server by default. To control access to the linked server, the sp_droplinkedsvrlogin procedure should be used to drop the global login mapping, followed by sp_addlinkedsvrlogin to map the desired login account(s) to the new linked server. When using sp_addlinkedsvrlogin, it is recommended that you set the @useself parameter = TRUE. This avoids the need to embed a user name and password into your SQL script.

For an overview of linking servers in SQL Server 2005, see

You must be logged on with an account that is a member of the SQL Server sysadmin fixed server role to perform this procedure.

  1. Click Start, click All Programs, click Microsoft SQL Server 2005 and then click SQL Server Management Studio.

  2. In the Connect to Server dialog box, specify the name of the appropriate SQL Server, and then click Connect.

  3. In SQL Server Management Studio, double-click Server Objects, right-click Linked Servers, and then click New Linked Server.

  4. In the New Linked Server dialog box, on the General page, in Linked server, enter the full network name of the SQL Serveryou want to link to.

    This procedure often refers to the server you are linking to as the remote server. This is for convenience only, to indicate the relationship of the linked (“remote”) server to the local server. Do not confuse this usage with the obsolete remote server functionality in SQL Server.

  5. Under Server type, click SQL Server.

  6. In the left pane of the New Linked Server dialog, under Select a page, choose Security.

  7. You will need to map a local server login to a remote server login. On the right side of the Security page, click the Add button.

  8. Under Local Login, select a local login account to connect to the remote server. Check Impersonate if the local login also exists on the remote server. Alternatively, if the local login will be mapped to a remote SQL Server login you must supply the Remote User name and Remote Password for the remote server login.

    To use impersonation, your SQL Server configuration and login accounts must meet the requirements for delegation. For more information about impersonation and delegation, see

  9. In the left pane of the New Linked Server dialog, under Select a page, choose Server Options. Set the Rpc and Rpc Out parameters to True, and then click OK.

Community Additions