In this article, we discuss How to add Linked Server to SQL Server. There are two ways to add another linked server in SQL Server. Using the first method (sp_addlinkedserver), you need to specify the actual server name as the “linked server name”. What this means is that every time you want to reference the linked server in code, you will use the remote server’s name.
This may not be beneficial because if the linked server’s name changes, then you will have to also change all the code that references the linked server. I like to avoid this method even though it is easier to initially set up. The rest of the steps will guide you through setting up a linked server with a custom name:
To add a linked server using SSMS (SQL Server Management Studio), open the server you want to create a link from in object explorer.
- In SSMS, Expand Server Objects -> Linked Servers -> (Right-click on the Linked Server Folder and select “New Linked Server”)
- The “New Linked Server” Dialog appears. (see below).
- For “Server Type” make sure “Other Data Source” is selected. (The SQL Server option will force you to specify the literal SQL Server Name)
- Type in a friendly name that describes your linked server (without spaces). I use HRDataServer.
- Provider – Select “Microsoft OLE DB Provider for SQL Server”
- Product Name – type: SQLSERVER (with no spaces)
- Data source – type the actual server name, and instance name using this convention: SERVER_NAME\INSTANCE_NAME
- ProviderString – Blank
- Catalog – Optional (If entered use the default database you will be using)
- Prior to exiting, continue to the next section (defining security)
- Within the same Dialog on the left menu under “Select a Page”, select Security
- Enter the security option of your choice. See security details following down.
- Click OK, and the new linked server is created.
Linked server security can be defined in a few different ways. Different security methods are outlined below. The first three options are the most common:
| Option Name | Description |
| Be made using the login’s current security context | Most Secure. Uses integrated authentication, specifically Kerberos delegation to pass the credentials of the current login executing the request to the linked server. The remote server must also have the login defined. This requires establishing Kerberos Constrained Delegation in Active Directory unless the linked server is another instance on the same Server. If the instance is on the same server and the logins have the appropriate permissions, I recommend this one. |
| Be made using this security context | Less Secure. Uses SQL Server Authentication to log in to the linked server. The credentials are used every time a call is made. |
| Local server login to remote server login mappings | You can specify multiple SQL Server logins to use based upon the context of the user that is making the call. So if you have George executing a select statement, you can have him executed as a different user’s login when linking to the linked server. This will allow you to not need to define “George” on the linked server. |
| Not be made | If a mapping is not defined, and/or the local login does not have a mapping, do not connect to the linked server. |
| Be made without using a security context | Connect to the server without any credentials. I do not see a use for this unless you have security defined as public. |
