Configure Linked Microsoft SQL Servers

The following how-to is for the SQL 2008 product line and assumes the use of Microsoft's free SQL Management Studio.

Configuring your Server

1. Connect to your server in SQL Management Studio. For help using SQL Management Studio, go here.

2. In the Object Explorer on the left, expand the server and the Server Objects folder.

3. Right-click Linked Servers and select New Linked Server from the context menu.

4. In the right-hand panel of the New Linked Server window select SQL Server as the linked server type. Use the domain name for the contents of the Linked server control.

5. Select the security page in the "Select a page" list on the top-left side of the New Linked Server window to bring up the remote-to-local login mapping page.

If you intend to use local logins (easier for non-windows credentialed apps) then you must use the mappings iirc. Otherwise, you can use reuse a current connections Windows credentials. Here is the text from Microsoft regarding the behavior for unmapped local logins:

Not be made
Specify that a connection will not be made for logins not defined in the list.

Be made without using a security context
Specify that a connection will be made without using a security context for logins not defined in the list.

Be made using the login's current security context
Specify that a connection will be made using the current security context of the login for logins not defined in the list. If connected to the local server using Windows Authentication, your windows credentials will be used to connect to the remote server. If connected to the local server using SQL Server Authentication, login name and password will be used to connect to the remote server. In this case a login with the exact same name and password must exist on the remote server.

Be made using this security context
Specify that a connection will be made using the login and password specified in the Remote login and With password boxes for logins not defined in the list. The remote login must be a SQL Server Authentication login on the remote server.


6. The options on the Server Options page are described here. Enable those that make assumptions about collation being identical, data access, and rpc. The latter for feature richness (e.g. calling remote stored procs) and the former for potential performance benefits.

7. Click OK.

Repeat this process on the "remote" machine back to the "local" if you want a truly bidrectional linked set.

Making a Script

1. At the top of the New Linked Server window (left of middle), click the Script button. This will generate some transact-sql in management studio window that you can use as a template.