Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Issue in using loopback linked server Expand / Collapse
Author
Message
Posted Tuesday, October 23, 2012 2:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 12, 2013 9:14 AM
Points: 3, Visits: 12
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?
Post #1376238
Posted Tuesday, October 23, 2012 3:14 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:02 PM
Points: 12,890, Visits: 31,853
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 [B]? 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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1376261
Posted Tuesday, October 23, 2012 4:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 12, 2013 9:14 AM
Points: 3, Visits: 12
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
Post #1376273
Posted Tuesday, October 23, 2012 5:06 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 4:43 PM
Points: 897, Visits: 7,109
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
Post #1376284
Posted Wednesday, October 24, 2012 8:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 12, 2013 9:14 AM
Points: 3, Visits: 12
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
Post #1376501
Posted Wednesday, October 24, 2012 9:13 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 4:43 PM
Points: 897, Visits: 7,109
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
Post #1376539
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse