Issue in using loopback linked server

Question

Issue in using loopback linked server

PrashantShah

Old Hand

Points: 347
More actions
October 23, 2012 at 2:20 pm

#269002

I have two db's on a SQL2008 server let's say database A & database B with their own SQL log in. I would like to access a table "T" on database B When logged into database A.
I logged in as SYS ADMIN & created a loopback link server using the following Syntax
EXEC sp_addlinkedserver
@server=N'SELF',
@srvproduct=N' ',
@provider=N'SQLNCLI',
@datasrc=@@SERVERNAME
Then I Log in to database A using its own log in credentials & run the below 4 part query to access a table T on database B
Select [SELF].B.dbo.T
I get a following error When I run the above query
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI10" for linked server "SELF" does not contain the table ""B"."dbo"."T"". The table either does not exist or the current user does not have permissions on that table.
Any idea on how to resolve this issue using the link server?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

the error is pretty straight forward; it could not find the table in the database. maybe when you abstracted it out, you hid the issue.

do you Really have a database named ? ANd a Table named [T]?

on my server, I KNOW i have a database named SandBox, and another database named 'PERFECT1200'

I also KNOW the tables i'm using exist.

this worked without errors:

like you said, I'm logged in as a sysadmin for testing this:

EXEC sp_addlinkedserver

@server=N'SELF',

@srvproduct=N' ',

@provider=N'SQLNCLI',

@datasrc=@@SERVERNAME

SELECT * FROM SELF.PERFECT1200.dbo.GMACT

SELECT * FROM SELF.SANDBOX.dbo.AllMeasures

Now, if i log in as the user [ClarkKent], that user MUST BE A USER IN BOTH DATABASES.

if he's a user in one database, but not a user in the linked server database, that's an expected security error.

i bet that's where your error is.

you either need to allow the linked server to use other credentials , or make the user a user in both databases.

now in real life, you never use SA.

what i usually do is create multiple users without login, and use that user for permissions for reading data on the lined server:

CREATE USER READ_ONLY WITHOUT LOGIN;

EXEC sp_addrolemember 'db_datareader','READ_ONLY'

EXEC sp_addrolemember 'db_datawriter','LIMITED_USER'

CREATE USER LIMITED_USER WITHOUT LOGIN;

EXEC sp_addrolemember 'db_datareader','LIMITED_USER'

EXEC sp_addrolemember 'db_datawriter','LIMITED_USER'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

PrashantShah Old Hand Points: 347 More actions · Answer 2

Thank you Lowell for your reply

I do have database B & table T, I think as you indicated the problem is the user needs to be in both the databases, I will follow your suggestion to add a user & see what happens

David Webb-CDS SSCoach Points: 17398 More actions · Answer 3

But if you add the user in both databases, and the databases are on the same server, why do you need a linked server? Why not just use 3 part naming conventions and access the table directly?

And then again, I might be wrong ...
David Webb

PrashantShah Old Hand Points: 347 More actions · Answer 4

I agree David, If I create a user in both the db's than I do not need a link server.

Creating a User is what I am trying to avoid as we have quite a few client db's & we will have to create the USER on all those db's - hence we would like to use Linked Server

We have an Admin db with its own log in where all the Admins are set up with different access rights to different Clients. We also have quite a few Client db's with their own Log ins where a certain stored procedure accesses this Admin db's to get all the rights for an ADMIN

The Script we are using can easily be accomplished using OPENROWSET & OPENDATASOURCE but we do not have access to its use, hence we are trying to create Linked Server so we can use OPENQUERY syntax or a four part name syntax to get the ADMIN info we want from the ADMIN db

After doing some digging around I found that a Loopback Linked server (A Linked server to its own instance) can be created, I am trying to figure out on how to set this up

I would appreciate some info, If anyone has an idea on how to set up the loopback Linked server

David Webb-CDS SSCoach Points: 17398 More actions · Answer 5

Regardless of whether the data is retrieved from the loopback server or directly, the id that gets the data has to have access to that database. You can map login ids for the linked server so that the login id for database A maps to the login id for database B, but there's no way I know of to circumvent data access rules to let the login for database A get to data it doesn't have access to without using the database B login somehow.

And then again, I might be wrong ...
David Webb