Share via

  • Article

Creating Linked Servers (SQL Server Database Engine)

Creating a linked server lets you work with data from multiple sources. The linked server does not have to be another instance of SQL Server, but that is a common scenario. This topic shows how to create a linked server and access data from another SQL Server.

In This Topic

  • Before you begin:

    Background

    Security

  • Examples:

    SQL Server Management Studio

    Transact-SQL

  • Follow Up: Steps to take after you create a linked server

Background

A linked server allows for access to distributed, heterogeneous queries against OLE DB data sources. After a linked server is created, distributed queries can be run against this server, and queries can join tables from more than one data source. If the linked server is defined as an instance of SQL Server, remote stored procedures can be executed.

The capabilities and required arguments of the linked server can vary significantly. The examples in this topic provide a typical example but all options are not described. For more information, see sp_addlinkedserver (Transact-SQL).

Security

Permissions

Requires ALTER ANY LINKED SERVER permission on the server.

[Top]

How to Create a Linked Server

You can use any of the following:

  • SQL Server Management Studio

  • Transact-SQL

Using SQL Server Management Studio

To create a linked server to another instance of SQL Server Using SQL Server Management Studio

  1. In SQL Server Management Studio, open Object Explorer, expand Server Objects, right-click Linked Servers, and then click New Linked Server.

  2. On the General page, in the Linked server box, type the name of the instance of SQL Server that you area linking to.

    Note

    If the instance of SQL Server is the default instance, enter the name of the computer that hosts the instance of SQL Server. If the SQL Server is a named instance, enter the name of the computer and the name of the instance, such as Accounting\SQLExpress.

  3. In the Server type area, select SQL Server to indicate that that the linked server is another instance of SQL Server.

  4. On the Security page, specify the security context that will be used when the original SQL Server connects to the linked server. In a domain environment where users are connecting by using their domain logins, selecting Be made using the login’s current security context is often the best choice. When users connect to the original SQL Server by using a SQL Server login, the best choice is often to select By using this security context, and then providing the necessary credentials to authenticate at the linked server.

  5. Click OK.

[Top]

Using Transact-SQL

To create a linked server by using Transact-SQL, use the sp_addlinkedserver (Transact-SQL)CREATE LOGIN (Transact-SQL) and sp_addlinkedsrvlogin (Transact-SQL) statements.

To create a linked server to another instance of SQL Server using Transact-SQL

  1. In Query Editor, enter the following Transact-SQL command to link to an instance of SQL Server named SRVR002\ACCTG:

    USE [master]
GO
EXEC master.dbo.sp_addlinkedserver 
    @server = N'SRVR002\ACCTG', 
    @srvproduct=N'SQL Server' ;
GO

  2. Execute the following code to configure the linked server to use the domain credentials of the login that is using the linked server.

    EXEC master.dbo.sp_addlinkedsrvlogin 
    @rmtsrvname = N'SRVR002\ACCTG', 
    @locallogin = NULL , 
    @useself = N'True' ;
GO

[Top]

Follow Up: Steps to take after you create a linked server

To test the linked server

  • Execute the following code to test the connection to the linked server. This example the returns the names of the databases on the linked server.

    SELECT name FROM [SRVR002\ACCTG].master.sys.sysdatabases ;
GO

Writing a query that joins tables from a linked server

  • Use four-part names to refer to an object on a linked server. Execute the following code to return a list of all logins on the local server and their matching logins on the linked server.

    SELECT local.name AS LocalLogins, linked.name AS LinkedLogins
FROM master.sys.server_principals AS local
LEFT JOIN [SRVR002\ACCTG].master.sys.server_principals AS linked
    ON local.name = linked.name ;
GO

    When NULL is returned for the linked server login it indicates that the login does not exist on the linked server. These logins will not be able to use the linked server unless the linked server is configured to pass a different security context or the linked server accepts anonymous connections.

[Top]