Setting up a linked server in SQL Server allows you to execute commands against OLE DB data sources, including other SQL Server instances. This provides the ability to query remote data from your local SQL Server instance. The process typically involves using SQL Server Management Studio (SSMS) or Transact-SQL commands.
Here are the steps to set up a linked server using SQL Server Management Studio (SSMS), based on common practice:
Step-by-Step Guide to Setting Up a Linked Server
Setting up a linked server via the SSMS graphical interface follows a structured process involving several configuration steps.
1. Launch SSMS and Connect
Begin by opening SQL Server Management Studio (SSMS) on your client machine. Connect to the SQL Server instance where you want to create the linked server (this is your local server).
2. Create a Linked Server
Navigate the Object Explorer tree:
- Expand Server Objects.
- Right-click on Linked Servers.
- Select New Linked Server....
This opens the 'New Linked Server' dialog box.
3. Configure General Properties
On the General page of the 'New Linked Server' dialog, provide the basic information:
- Linked server: Enter the name you want to use to refer to the linked server (e.g.,
REMOTESERVER). This name will be used in queries. - Server type:
- Select SQL Server if you are linking to another SQL Server instance. This is the simplest case.
- Select Other data source if you are linking to a different type of data source (e.g., Oracle, Access, Excel, or another OLE DB provider).
- If you selected 'Other data source', you need to configure the provider and data source details:
- Provider: Choose the appropriate OLE DB provider (e.g.,
Microsoft OLE DB Provider for SQL Serverfor older SQL Server versions,OLE DB Provider for Oracle, etc.). - Product name: The name of the OLE DB data source (optional but recommended).
- Data source: The name of the data source as interpreted by the provider (e.g., network name/IP address of the server, file path for Access/Excel).
- Provider string: Additional connection properties for the provider (optional).
- Location, Catalog: Specify database or file locations (optional, provider-dependent).
- Provider: Choose the appropriate OLE DB provider (e.g.,
4. Configure Security
The Security page is crucial for defining how the local SQL Server instance will authenticate when connecting to the remote server using the linked server definition.
You have several options:
- Not be made: No security context is passed. Usually prevents successful connection unless the remote server allows anonymous access (rare).
- Be made using the login's current security context: Uses the credentials of the currently logged-in local user. This works well in trusted domain environments using Windows Authentication.
- Be made using a security context that was not defined: Allows specific logins to use a specific security context (see table below).
- Be made using this security context: Uses a fixed remote login and password for all local connections through this linked server.
A common approach is to map specific local logins to remote logins:
| Local Login | Impersonate? | Remote User | Remote Password | Description |
|---|---|---|---|---|
| (All authenticated) | Checked | Uses the current login's context (Kerberos delegation required). | ||
| (All authenticated) | Unchecked | RemoteUserName | Password | All local logins use this single remote login/password. |
| LocalLoginName | Checked | Specific local login impersonates itself remotely (Kerberos delegation). | ||
| LocalLoginName | Unchecked | RemoteUserName | Password | Specific local login uses this remote login/password. |
| (Not listed in above) | Not be made | Logins not explicitly mapped or using 'All authenticated' will not connect. | ||
| (Not listed in above) | Be made using this security context | (Fixed user/pwd below) | Logins not explicitly mapped or using 'All authenticated' use the fixed context defined below. |
Choose the method that best fits your security requirements.
5. Configure Server Options
On the Server Options page, you can adjust various settings that affect the behavior of the linked server connection. Key options include:
- Collation Compatible: Set to
TRUEif both servers use the same collation or if you want SQL Server to assume collation compatibility, potentially avoiding collation checks during queries. - Data Access: Set to
TRUEto allow distributed queries against this linked server. - RPC: Set to
TRUEto enable Remote Procedure Calls from the linked server to your local server. - RPC Out: Set to
TRUEto enable Remote Procedure Calls from your local server to the linked server. Essential for executing stored procedures remotely. - Use Remote Collation: Set to
TRUEto use the remote server's collation for linked server queries.
Configure these options based on how you plan to interact with the linked server.
6. Test the Connection
After configuring all the settings, it's essential to test the connection before saving.
- Click the Test Connection button on the 'New Linked Server' dialog (usually found near the bottom-left).
A successful test confirms that SSMS can connect to the remote data source using the provided configuration. If the test fails, review the server name, provider options, and security settings.
7. Use the Linked Server
Once the linked server is successfully created, you can query data from it using a four-part naming convention in your SQL queries:
LinkedServerName.DatabaseName.SchemaName.ObjectName
For example:
SELECT *
FROM REMOTESERVER.AdventureWorks2019.HumanResources.Employee;You can also execute remote stored procedures using EXEC or the OPENQUERY function for more complex scenarios or pass-through queries.
Example using OPENQUERY:
SELECT *
FROM OPENQUERY(REMOTESERVER, 'SELECT TOP 10 * FROM HumanResources.Employee');Properly setting up the linked server enables seamless data access and integration between disparate data sources within your SQL environment.
