Creating And Configuring A Linked Server In SQL Server

Linked Servers are a necessity in most SQL Server environments.  A linked server allows one instance of SQL Server to communicate with another instance or with other database products such as Oracle or MySQL.  Linked servers can also be configured as OLE DB providers to communicate with additional products such as Excel and Access.

Creating and configuring a linked server is quite simple, for the most part.  In most cases you will likely be creating them for simple SQL Server instance-to-instance communication.

The steps below outline what is necessary to create a basic linked server so that one instance of SQL Server can communicate with another and allow for Remote Procedure Calls (RPC).  RPC allows for a stored procedure to be run remotely from one server to the next (linked server).

To get started, connect to your database server in Microsoft SQL Management Studio.  Expand the Server Objects drop-down and right-click Linked Servers and select New Linked Server...

NewLinkedServer

Once the New Linked Server window appears we’ll need to make some changes to the default settings.  To begin, enter the name of the server you want to link.  In this example I am creating the linked server on LAB-SQL2012 to connect to LAB-SQL-04.

By default the Server type will be set to Other data source.  Since we are creating a link to an exiting SQL Server instance, select the radio button next to SQL Server as shown below.

NewLinkedServer_General

Next select the Security page. We’ll need to make a quick change here as well.  At the bottom of the screen, under For a login not defined in the list above, connections will:, select the radio button next to Be made using this security context.  Here you will want to provide credentials for connecting to the server.  The best practice is to create a service account to manager your linked server connections.  You’ll want to make sure the service account has been granted datareader and datawriter role memberships for each database on the server that you want to be accessible.  You will also need to create the service account on all servers that will be linked.  For more information on creating login accounts in SQL Server, you can review my post on Creating A New SQL Server Authentication Login and Assigning Role Memberships.

New_Linked_Server_cred_window

The final step is to configure the Server Options page.  Though you can potentially leave the default setting intact on this page, chances are you are going to require your linked servers to have the ability to execute cross-server stored procedures, in which case you need to set RPC and RPC Out to True.  You can also configure the Connection Timeout and Query Timeout thresholds here as well, though in most cases it is fine to leave these set to the default 0 value.

NewLinkedServer_ServerOptions

Once you have made all the necessary changes click OK.  Your new linked server has now been created.

Expand the Linked Servers drop-down and you will see your new linked server in the list along with any other existing links.

LinkedServerCreated

As with most anything in SQL Server, you can also script out creating a new linked server.  The following script will create the same linked server as was created above.  I have noted with comments the section of code at the top where you will need to provide the login credentials as well as the server name.

This is a modified version of the script that you can pull from Management Studio.  I have created  variables for the server, login and password to allow you to quickly alter the script when creating a new linked server.  I keep a template of this script saved so that I can quickly provide the server name and login credentials to create new linked servers on the fly.