SQL Server Linked Servers: Linking Instances of SQL Server Step-by-Step

In most places hosting instances of SQL Server, you will likely find older and newer versions of SQL Server running side-by side. This can of course become a challenge when you are writing code to make the servers communicate with each other.

When writing a SQL query on one server, how can one tie together information from databases on other servers? The simplest way is to link the servers by creating a cross server link. In general the cross server links work well across the network and across the various versions of SQL Server (with some exceptions).

So how does one set up a linked server?

The process is actually quite straightforward. I will illustrate a step-by-step example below for setting up a linked server in SQL Server 2008.

1. First start up SQL Server Management Studio (SSMS) and login to your server using an account with highlevel permissions.
2. Click on the Server Objects node
3. Click on the Linked Servers node within Server Objects
4. Right Click the Linked Servers node and select the option: New Linked Server

   

5. In the popup that appears enter the network name of the SQL server instance that you wish to link to in the field titled: Linked Server. Alternately you can enter a descriptive name for your new Linked Server. Here is a picture of the popup:

   

6. Next in the radio selection called ‘Server type’ you will say how you wish to connect to the remote server
   1.  If you entered the network name for your linked server in step 5 above then you can leave the server type radio selection as SQL Server. Skip to step 8 below if you choose this option.
   2. Otherwise if you provided a descriptive name you can select ‘Other Data Source’. I personally like to give a descriptive name and choose the ‘Other Data Source’ option.
7. If you  selected the ‘Other Data Source’ option then fill out the fields below it that are no longer grayed out. These are:
   1. In the Provider dropdown select ‘Microsoft OLE DB Provider for SQL Server’
   2. In the Product Name field enter the network name for your linked server
   3. In the Data Source field enter the network name for your linked server
   4. Leave the Provider String Field blank
   5. Optionally under the Catalog field you can specify the default database that you want to connect to. If you do not specify anything the default database will be the Master database and any query you run will need to specify the database on the server to query
8. The next important step is to set your linked server’s security settings. To do so click on the security tab on the left bar. Here is a picture of what the Security section looks like:

   

   1. The security section warrants a bit of investigation. This section consists of a ‘Local Server to remote server login mappings:’ section at the top and a ‘For a login not defined in the list above connections will:’ section
   2. For the  ‘Local Server to remote server login mappings:’ section
      1. Under the Local Login column you can select an account from the local SQL Server
      2. Under the Impersonate column you can select a checkbox to tell SQL server if it should attempt to try to impersonate the Local Login account you provided in the previous column.
         1. If you check the Local Login account checkbox then SQL Server will attempt to connect to the remote server using the UserId and Password of the Local Login account
         2. If you leave the Local Login account checkbox unchecked then the following two columns ‘Remote User’ and ‘Remote Password’ become editable. Using this method if you query the linked server under the local login account, then SQL Server will try to connect to the remote server using the remote User Id and Password that you have specified.
   3. For the  ‘For a login not defined in the list above connections will:’ section you are provided with four radio buttons.
      1. The first option is ‘Not be made’. This means that if you do not try to connect to query the linked server using an account defined in the section above, SQL server will not attempt to connect to the remote server.
      2. The second option is ‘Be made without using a security context’. This option implies that if you do not query the linked server using a defined account, then SQL server will attempt to connect to the remote server without passing credentials. This could pose a security concern.
      3. The third option is ‘Be made using the login’s current security context’. This means that if you are not running a query to the remote server using a defined account, then SQL server will try to connect to the remote server using your currently logged in credentials.
      4. The fourth option is ‘Be made using this security context’. I tend to find this option quite useful. What this is saying is that if you are not running a query to the remote server using a defined account from the list in the first section, then SQL server will try to connect to the remote server using a generic default account that you can specify. This is a catch-all solution.
9. The final important step is to set your Server Options. To do so click on the Server Options on the left bar. Here is a picture of what this tab looks like:

   

   1. There are several options here, some of which I find quite useful, others of which are much more situational. These are:
      1. Collation Compatible: Situationally useful, especially if your databases have been set with differing collation settings. If possible your DBA should have ensured that the databases use the same collation, but sometimes this is not the case.
         1.  If this option is set to true: SQL Server assumes that all characters in the linked server are compatible with the local server.
         2.  If this option is not set: SQL Server evaluates comparisons on character columns locally.
      2. Data Access: Important for obvious reasons
      3. RPC (Remote Procedure Call): Situationally useful. Enables an RPC from the remote server.
      4. RPC Out: Important. Enables an RPC to the remote server. Do you wish to be able to execute stored procedures via your linked server?
      5. Use Remote Collation: Important.
         1. If false: queries will use the default collation of the local server and the collation name option is ignored
         2. If true:   SQL Server will use the collation set on the remote server, and the collation specified in the collation name field is used for non-SQL Server data sources.
      6. Collation Name:  Situationally useful. Specifies 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.
      7. …. these options are not too useful, I tend not to set them and only skip to the last option ….
      8. Enable Promotion of Distributed Transaction. Situationally useful. When this option is set to true: calling a remote stored procedure starts a distributed transaction and runs the transaction with MS DTC.