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 this procedure.

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.

Prerequisites

• Sign in with an account that is a member of the SQL Server sysadmin fixed server role

• Create a local SQL Server login. In the following steps, this account is mapped to a login on the SQL Server you will link to.

Create a linked server

1. Open SQL Server Management Studio, enter the name of your local SQL Server, and then select Connect.

2. Expand Server Objects, right-click Linked Servers, and then select New Linked Server.

   To see Server Objects, connect to a local on-premises SQL Server. Then, Server Objects should be displayed.

3. In the Linked server text box, enter the full network name of the SQL Server you want to link to.

   Note

   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.

4. Under Server type, select SQL Server.

5. In the left pane, select Security.

   In this step, you map the local account you created to the remote server login. Your options:

   Option	Description
   Be made using the login’s current security context	In domain environments, users typically connect with their domain logins. This option may be best since the security context of the signed-in domain account is mapped to the local account you created.
   Be made using this security context	When users connect to the local SQL Server using a SQL Server login, then this option may be best. Then enter the login and password for the account on the linked server.

6. Select Add, and enter the following:

   1. Under Local Login, select the local account you created.

   2. Check Impersonate if the local login also exists on the remote server.

   3. Alternatively, if the local login will be mapped to a remote SQL Server login you, enter the Remote User name and Remote Password for the remote server login.

      Note

      To use impersonation, your SQL Server configuration and login accounts must meet the requirements for delegation. See Configuring Linked Servers for Delegation for more details.

7. In the left pane, choose Server Options. Set the RPC and RPC Out parameters to True, and then select OK.

See Also

Advanced Information About Backup and Restore