Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Apr 4, 2002

Linked Servers - Page 2

By Neil Boyle

Logical Server Names

You might want to set up multiple links to the same target server, an obvious reason for this being to lock down security. You might want one set of users to access one database and another to access a different database, but not let the two groups have access to both databases.

Multiple links to the same target server
Click image for full size

The code below will set up two logical linked servers to the same machine--one will be designed to access the PUBS database (called fuji_PBS_DB) while the other (fuji_NORTHWIND_DB) gives access to the Northwind database.

To test the code, you will need to set up two logins on your target server, called fuji_NORTHWIND and fuji_PUBS, and grant access to each in the relevant databases.

sp_addlinkedserver    	
fuji_PBS_DB, droplogins
   @server='fuji_PUBS_DB', 	
   @srvproduct='',
   @provider='SQLOLEDB', 
   @datasrc='FUJI'
go

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

sp_addlinkedserver    	
fuji_PBS_DB, droplogins
   @server='fuji_NORTHWIND_DB', 	
   @srvproduct='',
   @provider='SQLOLEDB', 
   @datasrc='FUJI'
go

sp_addlinkedsrvlogin 
   @rmtsrvname = fuji_NORTHWIND_DB
   , @useself = false
   , @locallogin = NULL
   , @rmtuser = fuji_NORTHWIND
   , @rmtpassword = fuji_NORTHWIND
go

Again, we can run our simple test to demonstrate the difference between the two servers:

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

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

[Each of the above selects originally appeared on a single line. We've wrapped them to multiple lines here for Web formatting purposes. -Ed.]

A Point About Security

What we have done here is to set up two linked servers and map all the logins from the local server onto one specific login on the linked server. This means that anyone who knows about the existence of the linked server setup and has access to the local server will also have access to the relevant data on the linked server as well.

Given that I said we wanted to separate the two groups of users and not give them access to the other group's data, this is not exactly an ideal solution.

In this instance it would be better to map individual logins on the local server to their required login on the linked server.

Another Point About Security

As this technique can 'open up' your servers if you do not use it carefully, it's probably best not to shout about it. Do not give the functionality directly to end users--encapsulate it in stored procedures, functions or client-side code, and keep strict controls on how users and developers use it. Users only need their data--they don't need to know how it got there.

Using Your Linked Server

Having set all this up, what can we to with it? Let's look at some examples:

We have already seen the basic syntax for accessing the remote data:

Select * from openquery (server, 'your query here')

This causes SQL Server to open a connection on the target server, log in, execute the query you specified, return the data to the server you are working on, and finally shut down the link.

This query does exactly the same thing, but the syntax is much neater and more flexible to use:

select * from fuji_PUBS_DB.pubs.dbo.authors

With this simplified syntax we can easily join tables on different servers. On my remote server I changed the name of a couple of people in the authors table of the PUBS database, and produced this simple query to return the data that was different across the two servers:

select r.au_fname as remote_fname, 
  r.au_lname as remote_lname, 
  l.*
from  authors l 		
join  fuji_PUBS_DB.pubs.dbo.authors r
on    l.au_id = r.au_id
where l.au_fname <> r.au_fname OR l.au_lname <> r.au_lname


MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM