SQL Server: linked server

Tweet

When working in a MS SQL Server environment, it is sometimes really useful to connect to another DBs. Those databases can be on the same machine or on the net, it doesn't matter. We can connect to those data sources using linked servers in MS SQL server.
In this article we will see how to do it.

How to query a linked server
Before getting into the core of this post, we should consider how we can query a linked database.
The basic schema we can use in a FROM clause is:

<LinkedServerName>.<DatabaseName>.<Schema>.<Object>

Said that, we can move on and see how to create a linked server.

Create a linked server

In SQL Server Management Studio (SSMS), we can navigate to "Server Objects" using the left panel. Expanding the tree node, we will see "Linked Servers": that's what we need.
Right click "Linked Servers" and choose "New Linked Server".
That will open a new window, where we need to fill in some basic information. First of all we need to give the new linked server a name, filling the "Linked Server" field. This will be the name of our linked server as seen just above (LinkedServerName).
After that, we will select the linked server type. If our linked server is a SQL Server database, we will select the first checkbox, otherwise we will have to choose from a list of available linked server types.
This is important, because we basically have different options to choose from. The data source could be an OLE DB, so we just need to choose the appropriate data source provided we configured it appropriately.

Security
On the left panel, we are in the "General" page. We can now move to the "Security" page and configure how clients will be able to connect to our linked server.
We basically can choose between "Impersonate" and "Associate": the first will use windows credentials, the second let us create local credentials.
On the lower part of the panel, we can choose how other users can connect to the linked server. We have different options to choose from:
1) "Not be made": nobody apart from the previous configured users can connect to the db;
2) "Be made without using a security options": it will not use any security options;
3) "Be made using login’s current security context": it will use the current user Windows credentials;
4) "Be made using this security context":anybody will be able to connect with valid credentials.

Server options

The last page is "Server options". Here we can give the final touches to our new linked server. Available options are:

• Collation Compatible - It will determine if the linked server will have the same collation as the local server.
• Data Access – Decide if the linked server data will be accessible or not. This is used to temporary disable a database. 
• Rpc – We use this option in order to allow remote procedure calls from the linked server.
• Rpc Out – This is for remote procedure calls to the linked server.
• Use Remote Collation – When "True" columns collation of the remote server will be used.
• Collation Name – This is the collation name.
• Connection Timeout – Here we can set a connection timeout.
• Query Timeout – This will set a query timeout

That's all. We just click "Ok" and the new linked server is created.

Query the linked server

Let's say we created a new linked server named "NewLinkedServer". The server has database called "DB1" with a table called "Users". How do we query the table?
Easily enough:

Select * from NewLinkedServer.DB1.dbo.Users

And that's all!
I hope you liked the post...