Linked Servers

Linked servers provide connectivity to external data sources, allowing you to access data and run procedures in SQL Server other than the one you are currently running on.

Moreover, linked servers provide connectivity to just about any data source you can get an ODBC driver for, providing you with a DML interface to Oracle databases, Excel spreadsheets, flat files and many others. In many cases, this makes linked servers a viable alternative to using DTS or BCP to get data from external sources.

In Part One of this article we will look at connecting SQL Servers together as linked servers, and in Part Two we will consider connectivity to other data sources.

To get the best out of the example code, you will need to be familiar with the SQL Server client connectivity tool and with setting up and permissioning Login and User IDs.

All the examples here are created using SQL Server 2000, though they should work fine on SQL Server 7 as well.

Linked SQL Servers

Linking two servers creates a “one way” connection between the two servers:

Click image for full size

So if I log on to my MiyajimaXP server and set up Fuji as a linked server, I will then be able to query data and run stored procedures on the Fuji servers without directly logging on to the Fuji server. It does not set up a two way link, though–I cannot yet go to the Fuji server and access the MiyajimaXP server from there. To accomplish this, I would have to set up another linked server going back the other way.

Setting up a basic Linked Server

Step one in setting up a linked server is to ensure that you have Network Connectivity between the two servers, and to set up an alias using the SQL Server Client Connectivity tool. Once that is done, we need to tell the “local” server about the linked (or “remote”) server. Here’s the SQL to set up a linked server:

sp_addlinkedserver 
    'Fuji,
    , N'SQL Server'

In this example I have used the Alias name set up using the Client Connectivity tool–that’s just to keep things simple for now–later on we will look at using logical names for servers and using multiple links.

Once that’s done, we have to handle permissioning between the two servers–we need to specify what user and password we will use when logging in to the linked server.

By default, any logins that exist on both servers will be allowed to execute queries across the link, provided that the passwords are identical on each server–otherwise the user will get a “Login failed” message. Ensuring user passwords and permissions are in sync for both servers may be an administrative headache for the DBA, but there you go.

Setting up linked server logins

There are other approaches you can take to linked server security. One is to ensure that any login across the linked server uses only a standard login ID at the remote end. This command will ensure that anyone that accesses the Fuji server logs into that server with the same Login ID.

sp_addlinkedsrvlogin 
    @rmtsrvname = FUJI
    , @useself = false
    , @locallogin = NULL
    , @rmtuser = fuji_PUBS
    , @rmtpassword = fuji_PUBS

This command maps all the Local logins to the login “fuji_PUBS” on the remote server–so all the accesses over the linked server will inherit the permissions of fuji_PUBS on the remote server. Using this technique, user passwords and permissions do not need to be kept in sync, but if you change the password for fuji_PUBS on the linked server, you will have to reset the Linked server login also.

Testing the link

A simple way to test the link is to run this basic query–the first line shows your connectivity on the local server and the second line shows the connectivity details on the remote server:

select @@serverName, user_name(), db_name(), @@spid

select * from 
   openquery (FUJI, 'select @@serverName, user_name(), db_name(), @@spid')

In this simple example you can also see the basic syntax for running queries against a linked server, but more on that later.

More on logins

The fuji_PUBS login will now become the default login for anyone accessing the linked server, and they will of course inherit the permissions of that login on the remote server. We can override this for specific users by mapping their logon ID to another login on the linked server. This example sets up a link between a login called ‘Tokyo’ that exists on both servers:

sp_addlinkedsrvlogin 
    @rmtsrvname = FUJI
    , @useself = true
    , @locallogin = tokyo
    , @rmtuser = tokyo
    , @rmtpassword = Tokyo

Now, if anyone signs in as “Tokyo” and accesses the linked server, they will log in to the linked server with their own ID, while all other users will still get the default ID on the linked server.

Dropping Logins

You can drop a specific linked server login with the command:

sp_droplinkedsrvlogin 
    @rmtsrvname = FUJI
    , @locallogin = Tokyo

Dropping Servers

If you need to drop an entire server, you can do so with the sp_dropserver command. All the logins associated with that server must first be dropped before the server itself can be dropped, but the easy way to do that is to specify the ‘droplogins’ parameter of the sp_dropserver SP, which gets it to do the hard work for you.

sp_dropserver fuji, droplogins