Sunday, May 11, 2014

MS SQL Server: How To Add Linked Server

*Notes; Adding a Linked server can be done by either using the GUI interface or the sp_addlinkedserver command.

Adding a linked Server using the GUI

To add a linked server using SSMS (SQL Server Management Studio), open the server you want to create a link from in object explorer.
  1. In SSMS, Expand Server Objects -> Linked Servers -> (Right click on the Linked Server Folder and select “New Linked Server”) > Add New Linked Server

  2. The “New Linked Server” Dialog appears.  (see below).

  3. For “Server Type” make sure “Other Data Source” is selected.  (The SQL Server option will force you to specify the literal SQL Server Name)
  4. Type in a friendly name that describes your linked server (without spaces). I use AccountingServer.
  5. Provider – Select “Microsoft OLE DB Provider for SQL Server”
  6. Product Name – type: SQLSERVER (with no spaces)
  7. Datasource – type the actual server name, and instance name using this convention: SERVERNAMEINSTANCENAME
  8. ProviderString – Blank
  9. Catalog – Optional (If entered use the default database you will be using)
Define the Linked Server Security
Less Secure. Uses SQL Server Authentication to log in to the linked server. The credentials are used every time a call is made.
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 instance is on the same server and the logins have the appropriate permissions, I recommend this one.

  1. Click OK, and the new linked server is created



Post a Comment