Linked Servers and how to add a linked server

Linked Servers: is a Link to an external (remote) data source. The remote data source can be Oracle, Access, Excel, SQL server or any other data source that uses an OLE DB provider.

The Linked Servers option allows you to connect to another instance of SQL Server running on a
different machine, perhaps remotely in a different city/country. This can be useful if you need
to perform distributed queries (query a remote database).

Purpose of Linked Server: It enabled us to query remote database servers of different providers. Thus we can copy or select table data between multiple servers. We can also use any DML query on the remote server using Linked Servers.

Creating a Linked Server:

  • Navigate to Server Objects > Linked Servers
  • Right click on Linked Servers and select New Linked Server

Adding LinkedServer

  • Provide the details for the linked server (as mentioned in the below screens) :LinkedServerDetails

     

 

  • This login should be present on remote server with required access on DB/DB objects.RemoteServerDetails
  • Details about the options enabled in below screen shot:
    Data Access : Enables and disables a linked server for distributed query access.
    RPC : Enables RPC from the specified server.
    RPC Out : Enables RPC to the specified server.
RemoteServerConfig

Now Right click the newly added server and click on Test Connection.

Distributed Queries : Query run against linked server,executing query you must include a fully qualified, four part table name for each data source to query.  “linked_server_name.catalog.schema.object_name”. Here catalogue is the remote server database.

Configuring the Security Model : When you use linked servers to access external data sources, you should pay special attention to the security context for the external connection.

Linked Server security model

  • Self-mapping: When a linked server is created, this mode is added for all local logins, so SQL Server tries to connect to the external data source using the current user’s login credentials. The same login and password must exist on the remote server. This is the default behavior.
  • Delegation:  This mode impersonates the Windows local credentials; the connection forwards the credentials of an authenticated Windows user to the linked server. The Windows user account and password must exist on the linked server.
  • Remote Credentials: