In environments in which multiple SQL Servers (or other databases such as mySQL) are present, there are many times in which server A could require data from a database on server B. In order to accommodate this scenario SQL Server presents us with the Linked Server.
This is basically an integrated means of connecting to other SQL Servers whilst also giving a level of control between the systems such as security settings.
Creating a Linked Server is quick and simple. The example below will be based on connecting two named instances on my own machine, but is equally relevant to any SQL Servers across a local network.
Firstly expand “Server Objects”, right click Linked Servers and click “New Linked Server…”
This will give you the Linked Server form:
Starting with the “General” tab (the default), we start to fill in the relevant details, in my test case I am connecting to a local instance called [(local)\SQL2008R2]. This is a SQL Server and therefore we tick that option and all other options become greyed out:
Next, change to the Security view…
The following configuration will depend on how you wish to set up your access levels. I will explain the options on offer…
You can choose to have specific local users use remote user accounts by adding them as follows:
Therefore anytime the user “boredDBAUser” accesses the linked server it will do so under the “boredDBALink” account which is set up on the remote server with its own access rights.
Use this with the following setting to ensure that ONLY those people specifically mapped will have access to the remote server:
The second option is for occasions when zero security is required on the remote server as it accepts anonymous users. I have rarely seen this used:
However, if you have the same users set up on both servers then you can check the third option in order to allow your current login credentials to be passed through to the remote server:
Lastly you can specify a generic login on the remote server that ALL connections then use. This is the least secure and should rarely be used unless, for example, the linked server is a read only machine to which everyone needs the same access:
The last tab is “Server Options”. Most of these can be left alone, but the key ones are “Data Access”, “RPC” and “RPC Out”.
Without Data Access set to true, you cannot query the linked server. Therefore this is true by default. It’s also a good way of temporarily and quickly breaking the link to the server if required.
RPC allows remote procedures to be called FROM the linked server, whereas RPC Out allows remote procedure calls TO the linked server.
If you are simply querying basic data then leave these False, but if you intend to run stored procedures over the link then it’s easiest to turn them both to True otherwise you’ll receive the common error:
“Server is not configured for RPC”
In order to access data from a linked server you have to use a 4 part name instead of the usual 3. Other than that, everything is the same:
Although note that intellisense doesn’t recognise a linked server and therefore you’ll always have your code underlined.