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
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).
You can use any of the following:
Using SQL Server Management Studio
To create a linked server to another instance of SQL Server Using SQL Server Management Studio
In SQL Server Management Studio, open Object Explorer, expand Server Objects, right-click Linked Servers, and then click New Linked Server.
On the General page, in the Linked server box, type the name of the instance of SQL Server that you area linking to.
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.
In the Server type area, select SQL Server to indicate that that the linked server is another instance of SQL Server.
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.
To create a linked server to another instance of SQL Server using Transact-SQL
In Query Editor, enter the following Transact-SQL command to link to an instance of SQL Server named SRVR002\ACCTG:
Execute the following code to configure the linked server to use the domain credentials of the login that is using the 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.
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.