Linked Server – Part 1

What is Linked Server?
Linked server is a concept in SQL Server to access external data sources. This external data sources can be Access, Oracle, Excel, SQL Server or almost any other data system that can be accessed by OLE or ODBC.

Where it is used??
A linked server configuration enables SQL Server to execute commands against OLE DB data sources on remote servers, In simple we can say users can retrieve datas from another instance of SQL Server other data sources (Excel, Access, Oracle etc), this may be another named instance or another SQL Server. Linked servers offer the following advantages:

  • Remote server access.
  • The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
  • The ability to address diverse data sources similarly.

Requirements to create a linked server

  • MSDTC service should be running
  • SPN should be set correctly to avoid double hop problem

linked_server_part1_1

How to Configure Linked Server to access data from another Instance of SQL Server

Consider you are having two SQL servers named server1 and server2 in your working place. Consider you need to fetch datas from server2 via server1, in this case you need to configure server2 as linked server in server1. Then using server2 linked server you can fetch datas from server2 SQL Server via server1. Lets discuss how to configure this linked server

  • Open SSMS connect to server1 (To create the linked server, the login should have ALTER ANY LINKED SERVER & ALTER ANY LOGIN permissions in server1)
  • Expand Server Objects folder –> Linked Server

linked_server_part1_2

  • Right click on the linked server folder and click on New Linked Server, the you will be prompted with a new windows
  • Since we are configuring linked server to another SQL Server, you need to select SQL Server Option button in the main page and provide the linked server name as shown below.

linked_server_part1_3

  • Once you have mentioned the server to connect, you need to specify the security context via server1 has to communicate to server2, click on security tab in the left pane
    You have four options to configure the security lets discuss about this

linked_server_part1_4

  • Not be Made

Its similar to disabling the linked server. i.e when you use this option and try to access the linked server you will be thrown with the error as below and it wont allow you to use the linked server to fetch the datas.
Msg 7416, Level 16, State 1, Line 1
Access to the remote server is denied because no login-mapping exists.

  • Be made without using a security context

    *)To use this option both the SQL Services(in both server1 & server2) must be running under same account and same password or else you wont be able to use this option.

    *) When a user logged in to server1 and if he she access data from server2 then SQLService credentials from server1 will be used to connect server2 and access the data

    *)Using this option wont be a best security practice since SQLService account will always be provided with SA fixed role and hence any user can use this linked server to access modify any data in server2 on any database

  • Be made using the logins current security context

*)Selecting this option will use the currently logged in users security credentials to server2. Hence if the current user have proper privilege in server2 he can access the tables modify the tables.

*)This option can be considered as best security practice, since all the privileges will be taken from the users credentials and hence he she wont be allowed to have elevated privileges in server2.

*)If the login doesnt exist in the destination server,( i.e server2) then you will receive the below error.
OLE DB provider “SQLNCLI” for linked server “server2” returned message “Login timeout expired”.

  • Be made using this security context

*) This option lets you to use remote credentials in local server. i.e you will provide sql login which exists in server2, hence connection to server2 will be made using this credentials from server1.

*) This option cant be used when the destination server is in windows authentication mode.

*)Linked server configured using this option will allows users to access the linked server and all the privileges exists for SQL login in server2 can be used by any of the user from server1

Once security credentials provided for the linked server, you can change server options for the configured linked server or let it in default state. Lets see description about server options page

linked_server_part1_5

Collation Compatible
Affects Distributed Query execution against linked servers. If this option is set to true, Microsoft SQL Server assumes that all characters in the linked server are compatible with the local server, with regard to character set and collation sequence (or sort order). This enables SQL Server to send comparisons on character columns to the provider. If this option is not set, SQL Server always evaluates comparisons on character columns locally.

This option should be set only if it is certain that the data source corresponding to the linked server has the same character set and sort order as the local server.

Data Access
Enables and disables a linked server for distributed query access.

Rpc
Enables RPC from the given server.

Rpc Out
Enables RPC to the given server.

Use Remote Collation
Determines whether the collation of a remote column or of a local server will be used.
If true, the collation of remote columns is used for SQL Server data sources, and the collation specified in collation name is used for non-SQL Server data sources.

If false, distributed queries will always use the default collation of the local server, while collation name and the collation of remote columns are ignored. The default is false.

Collation Name
Specifies the name of the collation used by the remote data source if use remote collation is true and the data source is not a SQL Server data source. The name must be one of the collations supported by SQL Server.

Use this option when accessing an OLE DB data source other than SQL Server, but whose collation matches one of the SQL Server collations.

The linked server must support a single collation to be used for all columns in that server. Do not set this option if the linked server supports multiple collations within a single data source, or if the linked server’s collation cannot be determined to match one of the SQL Server collations.

Connection Timeout
Time-out value for connecting to a linked server.

Query Timeout
Time-out value for queries against a linked server.

Test the created linked server

To select or modify the data you need to use four part identifier if your external data source is SQLServer. The format is as below

SELECT * FROM linkedservername.databasename.objectownername.objectname

An Example shown below

SELECT NAME FROM server2.master.dbo.sysdatabases

Conclusion

Thus linked server is a concept which will be useful to access external data sources effectively!!

Leave a Reply

6 comments

  1. We have having issue with Linke Server if the two servers are on differnt network and connected on VPN. The servers are windows 2008 Standard Edition and SQL Server 2008 R2.

    It throws msg as “OLE DB provider “SQLNCLI10” for linked server “SBILINKtest” returned message “No transaction is active.”.
    Msg 7391, Level 16, State 2, Line 3
    The operation could not be performed because OLE DB provider “SQLNCLI10” for linked server “SBILINKtest” was unable to begin a distributed transaction.

    Could you please help us in this.

Leave a comment

Your email address will not be published. Required fields are marked *

*